MrExcel Publishing
Your One Stop for Excel Tips & Solutions

SendMail


Posted by Neil Weston on June 14, 2001 8:21 AM

I'm currently using SendMail in VBA for Excel to send a file via MS Outlook but I want to add some text to the message together with the excel file as an attachment.

Can anyone help?



Posted by Tom Morales on June 14, 2001 3:27 PM

Neil - I don't think you'll able to do what you want with the sendmail method, unless you're happy with conveying your message in the subject header. To make a bona fide message, you'll have to save the Excel file (which your macro can do), and then have Outlook VBA take over.

First thing, add VBA outlook to your references. (In VBA editor under Tools/References)

Then, use code along these lines:
sub sendmyfilesomewhereplease
ActiveWorkbook.SaveAs FileName:="C:\MyFiles\my new excel file.xls"
Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.CreateItem(olMailItem)
With myItem
.Recipients.Add "John Q Receiver@aol.com"
.body = "This is the message text"
.Subject = "This is my message subject"
End With
Set myAttachments = myItem.Attachments
myAttachments.Add "C:\MyFiles\my new excel file.xls", _
olByValue, 1, "My file transmission"
myItem.Send
End Sub

Good Luck
Tom