For this example enter something in G1 & H1, use this code
Sub E_Mail_it()
Dim LstRw As Long, SendRng As Range, s As String, msg As String
LstRw = Cells(Rows.Count, "A").End(xlUp).Row
Set SendRng = Range("A6:H" & LstRw)
s = Range("H1").Value & " - you rock!"
msg = Range("G1").Value
With SendRng
SendRng.Select
ActiveWorkbook.EnvelopeVisible = True
With .Parent.MailEnvelope
' Set the optional introduction field thats adds
' some header text to the email body.
.Introduction = msg
With .Item
.To = "[email protected]"
.CC = ""
.BCC = ""
.Subject = s
.Send
End With
End With
End With
End Sub
Bookmarks