Email worksheets code - how do you add text to email body?

[wallyb]

Hi,

I am using the following code to email individual worksheets to separate email addresses:

Sub Mail_Every_Worksheet()
    Dim sh As Worksheet
    Dim wb As Workbook
    Dim strdate As String
    Application.ScreenUpdating = False
    For Each sh In ThisWorkbook.Worksheets
        If sh.Range("c9").Value Like "?*@?*.?*" Then
            strdate = Format(Now, "dd-mm-yy h-mm-ss")
            sh.Copy
            Set wb = ActiveWorkbook
            With wb
                .SaveAs "Sheet " & sh.Name & " of " _
                      & ThisWorkbook.Name & " " & strdate & ".xls"
                .SendMail ActiveSheet.Range("c9").Value, _
                          "This is the Subject line"
                .ChangeFileAccess xlReadOnly
                Kill .FullName
                .Close False
            End With
        End If
    Next sh
    Application.ScreenUpdating = True
End Sub

How can I add text to the body of the email.

Thanks

 

[wallyb]

yes - that's exactly what i said in my message - can this be combined?

 

[Norie]

No.

The SendMail method is for sending the active workbook.

As far as I can see in your current code you will create 2 mail messages every time you run it.

 

[wallyb]

found the code

I found the code - sends each worksheet out to an email address on the worksheet and puts a meassage in the body of the email letter:

Sub Outlook_Mail_Every_Worksheet()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strdate As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    For Each ws In ThisWorkbook.Worksheets
        If ws.Range("c9").Value Like "?*@?*.?*" Then
            strdate = Format(Now, "dd-mm-yy h-mm-ss")
            ws.Copy
            Set wb = ActiveWorkbook
            With wb
                .SaveAs "Sheet " & ws.Name & " of " _
                      & ThisWorkbook.Name & " " & strdate & ".xls"
                 Set OutMail = OutApp.CreateItem(0)
                With OutMail
                    .To = ws.Range("c9").Value
                    .CC = ""
                    .BCC = ""
                    .Subject = "This is the Subject line"
                    .body = "Hi" & vbNewLine & vbNewLine & _
                            "This is line 1" & vbNewLine & _
                            "This is line 2" & vbNewLine & _
                            "This is line 3" & vbNewLine & _
                            "This is line 4"
                    .Attachments.Add wb.FullName
                    .Send
                End With
                .ChangeFileAccess xlReadOnly
                Kill .FullName
                .Close False
            End With
            Set OutMail = Nothing
        End If
    Next ws
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

Thanks