I have the VB code below (that runs as a macro in Excel)
It separates each sheet and saves each sheet of the workbook by it's tab name as an Excel file in c:tmp - and emails each sheet to the email address on the tab of each sheet respectively. IT inserts the subject Then pops up a message box saying "Your message has been sent. Thank You."
I also want it to insert text into the body of each email message saying "Your Month End Report is attached."
I'm looking for the syntax to do that?
It seems like it would be similar to the syntax for sName (sName text is put in the subject) but I want to put the same text in the body of every email. Sub SepSheetsSaveEmail()
I have VB 6.3 and Excel 2002 SP1 and have also run this macro on a Win98se o/s.
Thanks in advance.


Sub SepSheetsSaveEmail()
'
' SepSheetsSaveEmail Macro
' Macro recorded 7/17/2002 by btemple
'
' Keyboard Shortcut: Ctrl+b
'
Dim oSheet As Worksheet
Dim sName As String

For Each oSheet In ActiveWorkbook.Sheets
sName = oSheet.Name
oSheet.Copy
ActiveWorkbook.SaveAs Filename:="C:\tmp\" & sName & ".xls"
MsgBox "Your message has been sent. Thank You"
ActiveWorkbook.SendMail sName, "Company Shipment Info"
ActiveWorkbook.Close
Next

End Sub

you are using the features already installed in excel to send mail, you would need to program the outlook 9.0 object, and create an email from scratch,

you need to reference the outlook 9.0 object in the vba editor and add the following example

Sub sendemail()

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Set objOutlook = CreateObject("Outlook.Application")

Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg

Set objOutlookRecip = .Recipients.Add("Add recepients here")

objOutlookRecip.Type = olTo

.subject = "add subjecthere"
.Body = "add body text here
.attachment ="Add attachment path here"

.Send

End With
Set objOutlook = Nothing

End Sub

hope helps,

i think all the syntax is correct, i changed the code a little but its what i use out of access, accept i fill the details in from fields rather than hard code them