Hi,

I am using Excel 2010 and Outlook 2010 and have some VBA that allows me to split out my workbook of about 40 tabs and email each tab as an attachment to the recipients in cell A1 of each tab. I would like to add body to my email though and am completely stumped, please help!

This is what I have so far and it works but I can't get text into my email body:

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("A1").Value Like "?*@?*.?*" Then

sh.Copy
Set wb = ActiveWorkbook

TempFileName = "Detailed Monthly Payroll Report " & "for " & sh.Name

addressees = Split(Range("A1"), ",")

With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, _
FileFormat:=FileFormatNum
On Error Resume Next
For I = 1 To 3
.SendMail addressees, "Monthly Payroll Report for Cost Centre " & sh.Name
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


Thanks for your help!