Excel email macro

[Foxy17]

On using the following macro I can email each sheet of a spreadsheet to the named user of that spreadsheet. I would like to add some guidance notes into the body of the email. I get the Subject in ok , but unsure how to get into the body of the email.

[pre]

Sub Mail_Every_Worksheet()
'Working in 97-2010
Dim sh As Worksheet
Dim wb As Workbook
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim TempFilePath As String
Dim TempFileName As String
Dim I As Long

TempFilePath = Environ$("temp") & ""

If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007-2010
FileExtStr = ".xlsm": FileFormatNum = 52
End If

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

For Each sh In ThisWorkbook.Worksheets
If sh.Range("A2").Value Like "MO*" Then

sh.Copy
Set wb = ActiveWorkbook

TempFileName = "Sheet " & sh.Name & " of " _
& ThisWorkbook.Name & " " _
& Format(Now, "dd-mmm-yy h-mm-ss")

With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, _
FileFormat:=FileFormatNum
On Error Resume Next
For I = 1 To 3
.SendMail sh.Range("A2").Value, _
"Automated report for your reference"
If Err.Number = 0 Then Exit For
Next I
On Error GoTo 0
.Close SaveChanges:=False
End With

'Delete the file you have send
Kill TempFilePath & TempFileName & FileExtStr

End If
Next sh

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

[/pre]

 

[Foxy17]

That didn't go to well....code here


http://speedy.sh/uD4aZ/macrormail.txt

 

[Luke M]

From the original author's page:

http://www.rondebruin.nl/mail/folder1/mail5.htm


Ron states at top of page

Important read this :


The code on this page is working with Outlook, Outlook Express, Windows Mail and Windows Live Mail.

With SendMail it is not possible to :


1) Send text in the Body of the mail

2) Use the CC or BCC field

3) Attach other files


If you want to have the options above and more and use Outlook you

can use one of the Outlook object model examples on my mail index page.

Going to the Outlook object model section, we find this code:

http://www.rondebruin.nl/mail/folder2/mail5.htm


where we can easily set the subject and body text.

 

[Foxy17]

Thanks Luke, and for finding wher I got the macro originaly, so I've added credits to his work on it. I just though there may be something I was missing , as I'm using GroupWise....thanks again

 

[Luke M]

Yep. Not exactly sure why Ron has the two options, but it looks like the latter one will solve the problem for you.