emailing message body with vba

[PointerDog]

Hello,
I am using the following code from Ron DeBruin 'how to email site' and I would like to add a message to each email. Any help is appreciated. thanks much

this what I am using - but I do not know how to add message:
Sub Mail_sheets()
Dim MyArr As Variant
Dim last As Long
Dim shname As Long
Dim a As Integer
Dim Arr() As String
Dim N As Integer
Dim strdate As String
For a = 1 To 253 Step 3
If ThisWorkbook.Sheets("mail").Cells(1, a).Value = "" Then
Exit Sub
End If
Application.ScreenUpdating = False
last = ThisWorkbook.Sheets("mail").Cells(Rows.Count, _
a).End(xlUp).Row
N = 0
For shname = 1 To last
N = N + 1
ReDim Preserve Arr(1 To N)
Arr(N) = ThisWorkbook.Sheets("mail").Cells(shname, a).Value
Next shname
ThisWorkbook.Sheets(Arr).Copy

With ThisWorkbook.Sheets("mail")
MyArr = .Range(.Cells(1, a + 1), .Cells(Rows.Count, _
a + 1).End(xlUp))
End With

ActiveWorkbook.SendMail MyArr, ThisWorkbook.Sheets("mail").Cells(1, a + 2).Value

ActiveWorkbook.Close False
Application.ScreenUpdating = True
Next a
End Sub

 

[Michael M]

First up, you'd need fresh code to enable the Outlook Object
Have a look about halfway down at the section regarding the Outlook Object model.
There is also a downloadable example. This is probably one of the best sites for this kind of stuff.
Have a look here

http://www.rondebruin.nl/sendmail.htm

As an example this what I used recently to eMail ranges with a message in the body....keeping in mind that you can put the message in a cell and then reference that cell in the code.

Sub Send_Email()
Dim Email_Subject, Email_Send_From, Email_Body As String, i As Integer
Dim Mail_Object, nameList As String, o As Variant
        Email_Send_From = ""
         For i = 7 To 39 'this is a range of 32 cells with e-mail addresses in them, change to suit
            If Sheets("SHEETNAME").Range("I7").Value <> "" Then 'makes sure the range isn't BLANK
                nameList = nameList & ";" & Sheets("SHEETNAME").Range("I" & i).Value 'combines all the e-mail addresses into an array of names, just like you'd see in the "TO" box
            End If
        Next
        Set Mail_Object = CreateObject("Outlook.Application") 'makes Outlook the mail device
        With Mail_Object.CreateItem(o)
            .Subject = "YOURSUBJECT"
            .To = nameList 'the list of name created earlier
            .Body = "E MAIL TEXT GOES HERE" & Chr(13) & Chr(13) & "Regards," & Chr(13) & "YOUR NAME." & Chr(13) & "YOUR ADDRESS." 'Change comments to suit
            '.Body = Range("A1").value & Chr(13) & Chr(13) & "Regards," & Chr(13) & "YOUR NAME." & Chr(13) & "YOUR ADDRESS." 'or using a cell reference
            .Attachments.Add "YOUR PATH & FILE NAME " ' or  YOU CAN USE "ActiveWorkbook.FullName"
            '.Send  will send immediately or CHANGE THIS LINE TO .Display to review before sending
End With
        MsgBox "E-mail successfully sent", 64
        Application.DisplayAlerts = False
End Sub