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!
Bookmarks