How to send mass emails in Excel?

Question

This is how the message should look in the email:

This is my Excel sheet:

I have an Excel file with columns from A to M, A being Payer, and M being the email address. I need a macro to sort by email address and create an email with the data associated with that email address.

I need a message on the email that reads:

Hello team,

Please review the items below and provide your comments for the invoices due in the account regarding payment details, let us know if there is additional information needed from our end so that we can send it as soon as possible.

That message would have to be able to be changed and my signature at the end.

The data on the columns is:

A Payer
B Name
C Invoice Number
D Bill. Date
E Net due date
F Days past due
G Aging
H Value
I Cust. Mat.
J PO Number
K Bill.Doc.
L Delivery
M Email

I would like to be able to check the emails before sending them.

Answer 1

This is a handy piece of code that includes your default signature in an email.

It is also possible to add attachements (just to keep in mind). You will need to play with it using the .diplay property until it looks pretty and the info you need included is all being pulled in correctly.

You can then create another sub to to iterate though each line of data you have and send those emails.

Option Explicit
Sub EmailIncludingSignature()

    Dim eSubject As String
    Dim eRecipient As String
    Dim eBody As String
    
    Dim SAVELOC As String
    
    Dim objOutlook As Object
    Dim objEmail As Object
    Dim objAttachment As Object
    
    Dim S As String
    
        'Setup Email
    Set objOutlook = CreateObject("Outlook.Application")
    Set objEmail = objOutlook.CreateItem(olMailItem)
    'Set objAttachment = objEmail.Attachments
    'objAttachment.Add SAVELOC & ".pdf"
    
        'Email Signature
    S = Environ("appdata") & "\Microsoft\Signatures\"
    If Dir(S, vbDirectory) <> vbNullString Then S = S & Dir$(S & "*.htm") Else S = ""
    S = CreateObject("Scripting.FileSystemObject").GetFile(S).OpenAsTextStream(1, -2).ReadAll
    
        'Email parameters
    eSubject = "Test Email Signature"
    eRecipient = "cameron.critchlow@westinbearmountain.com"
    eBody = "" & _
        "<br>Hi All,<br><br>" & _
        "Last line,<br><br>" & S
    
        'Build Email
    With objEmail
        .To = eRecipient
        .CC = ""
        .BCC = ""
        .Subject = eSubject
        .BodyFormat = olFormatHTML ' send plain text message
        .HTMLBody = eBody
        .Display
        '.Send
    End With
    
End Sub

Do you have experience with VBA? does the above make sense?