How to Send Emails from Excel using VBA macros?

by

Here’s a reader question:

I found your Outlook VBA coding tips very helpful. I’m wondering if you could share some code to help automate sending emails directly from Excel using VBA? I have a spreadsheet with customer data and I’d like to send personalized emails to each of them.

Thanks for the question! Sending emails directly from Excel is a powerful way to automate communication based on your spreadsheet data. Let’s dive into how you can accomplish this using VBA.

Create Outlook emails automatically in Excel

To send emails from Excel using Visual Basic for Applications, follow these steps:

  1. First, we will use VBA to create a reference to Outlook.
  2. Then, we’ll create a new email item in Outlook.
  3. Next, we’ll set the email properties like recipient, subject, and body, potentially using data from your Excel cells.
  4. Last we’ll go ahead and send the email to its recipient.

Here’s a simple VBA code to automatically create and send emails from Excel:

Sub SendEmailFromExcel()
    Dim OutlookApp As Object
    Dim OutlookMail As Object
    Dim cell As Range

    ' Create Outlook object
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookMail = OutlookApp.CreateItem(0)

    ' Assume email addresses are in column A, starting from row 2
    For Each cell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        With OutlookMail
            .To = cell.Value
            .Subject = "Your Subject Here"
            .Body = "Hello, " & cell.Offset(0, 1).Value & vbNewLine & vbNewLine & _
                    "This is the body of your email."
            .Send  ' Use .Display instead of .Send if you want to review before sending
        End With
    Next cell

    Set OutlookMail = Nothing
    Set OutlookApp = Nothing
End Sub

This script assumes that your email addresses are in column A, starting from row 2, and that the names are in column B. Adjust as needed for your specific spreadsheet layout.

Creating an excel VBA macro button

To make it easy to run your email-sending macro, you can create a button in Excel:

  1. Go to the “Developer” tab in Excel. If you don’t see it, go to File > Options > Customize Ribbon and check the box for “Developer”.
  2. Next, go ahead and click on “Insert” in the Controls group, then choose “Button” under Form Controls.
  3. Draw the button on your worksheet.
  4. When prompted to assign a macro, select the SendEmailFromExcel macro you just created.
  5. Right-click the button and choose “Edit Text” to give it a meaningful name.

Generate personalized emails with excel data

Let’s expand our script to send more personalized emails using data from multiple columns:

Sub SendPersonalizedEmails()
    Dim OutlookApp As Object
    Dim OutlookMail As Object
    Dim cell As Range
    Dim lastRow As Long

    ' Create Outlook object
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookMail = OutlookApp.CreateItem(0)

    ' Find the last row with data in column A
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row

    ' Loop through each row
    For Each cell In Range("A2:A" & lastRow)
        With OutlookMail
            .To = cell.Value
            .Subject = "Personalized Offer for " & cell.Offset(0, 1).Value
            .Body = "Dear " & cell.Offset(0, 1).Value & "," & vbNewLine & vbNewLine & _
                    "Based on your recent purchase of " & cell.Offset(0, 2).Value & ", " & _
                    "we'd like to offer you a special discount of " & cell.Offset(0, 3).Value & _
                    " on your next order." & vbNewLine & vbNewLine & _
                    "Thank you for your continued business!" & vbNewLine & vbNewLine & _
                    "Best regards," & vbNewLine & _
                    "Your Sales Team"
            .Display  ' Use .Send instead of .Display to send automatically
        End With
    Next cell

    Set OutlookMail = Nothing
    Set OutlookApp = Nothing
End Sub

This VBA script assumes your Excel sheet is structured as follows:

  • Column A: Email addresses
  • Column B: Customer names
  • Column C: Recent purchase items
  • Column D: Discount offers

Adding attachments to your excel VBA emails

If you need to include attachments with your emails, you can modify the script like this:

' Inside your email-sending loop:
.Attachments.Add "C:\Path\To\Your\Attachment.pdf"

Replace the file path with the actual path to your attachment. You can also dynamically set the attachment based on cell values in your spreadsheet.

Remember to always test your email-sending scripts thoroughly before using them with real customer data. It’s a good practice to start with .Display instead of .Send to review the emails before sending them out.