technical excel macro help please!

[TTDegs]

got a fabby macro running in my excel spreadsheet, but now I've hit a problem
And I'm hoping some wiz out there might be able to help!

I've got a sheet with all my data on, sorted by outlet.

Each seperate outlet needs to get the data for just their own store.

I can get the macro to dynamically extract each store's info into a new sheet without a problem - BUT

I now need to send this data in an email.

If (under macro control) I create an email with an attachment, I loose control from the macro (cos the PC is now running Outlook)
and even the email address (copied by macro from the appropriate cell) refuses to paste into the To box (even manually)

If I create the email as a 'for review' I can (manually) paste the email address in, but again, not by the macro as it's not 'in control' of the email


does anyone have any ideas please?

I need to make this a 'push the button and forget' operation, as it's got to work for muppet users - sorry, I mean it needs to be foolproof

any suggestions gratefully received!


But I'm constrained to using excel and Outlook, as it's a work app

[Glass Spider]

are you using the CreateItem(olMailItem) method to create your email?

mind you if i were you i'd get into access and run queries and stuff form there, I have a database that puts data out to email and is one button muppet proof job

[Pottsy]

I have had to do this before and I remember it all getting a bit unstuck. Ended up coding it in VB6 instead...

But here's a bit pinched from a quick Google...

Sub SendEmail()

ESubject = "This is a test email"
SendTo = "Some_Address@ADomain.com"
CCTo = "Different_Address@ADomain.com"
Ebody = "Testing VBA's ability to send an email."
NewFileName = "C:\My Documents\TestFile.xls"

Set App = CreateObject("Outlook.Application")
Set Itm = App.CreateItem(0)
With Itm
.Subject = ESubject
.To = SendTo 
.CC = CCTo
.Body = Ebody 
'.Attachments.Add (NewFileName) ' Must be complete path
'.Display ' This property is used when you want
' the user to see email and manually send. Then 
' comment out rest of code except “End With” statement
' and "End Sub" statement.
.send
End With
Set App = Nothing
Set Itm = Nothing
End Sub

The "complete path" might be a nightmare though, and I bet Outlook does that "something is trying to send an email, what do you want to do?" question.

[smiffy]

I've created an App using very similar logic to this, except I stopped at the .display, so the user could add text if necessary, then click on Send.

Can't remember why now, but I also had, at the beginning:

On Error Resume Next
Set OutlookApp = GetObject(, "Outlook.Application")
If Err <> 0 Then 'Outlook not running - start it
Me.MousePointer = vbHourglass
Err.Clear
Set OutlookApp = CreateObject("Outlook.Application")
DoEvents
If Err <> 0 Then
MsgBox "Please start Outlook then try again"
Err.Clear
End If
Me.MousePointer = vbDefault
End If
On Error GoTo 0

I think the CreateObject failed if the user already had Outlook open.

[Glass Spider]

On Error Resume Next, thats one of my favourites! lol

[smiffy]

Yes, very 20th Century (early), isn't it? Works, though!

[richierich]

And I thought Esperanto never took off.

[TTDegs]

many thanks for all the replies!

I've actually taken inspiration from each of them, scratched my head a lot, perused the (fairly useless!) help topics, and come up with...

macros copies relevant data into new worksheet

B1 contains the report title = "Report for 2006/10/18"
B3 contains (dynamically) the email address of the recipients
C3 contains (dynamically) the store name

emailstore = range("b3").value
emailsubj = range("b1").value + " - " + range("c3").value
activeworkbook.sendmail Recipients:=emailstore, subject:=emailsubj

and job is jobbed

MANY thanks again for all the replies - much appreciated!