VBA: How to Auto-Generate Personalized Emails in Excel

I work one of those weird jobs where the company still uses Outlook. I’m a big fan of Gmail and Google Docs, but I really like having the option of VBAing my emails.

Say, for instance, I want to send the same email to multiple people. I don’t want to put them all in a single email thread though—inevitably someone will hit Reply All, then someone else will hit Reply All, then someone else will get curmudgeonly over having a huge email chain clogging their feed.

I acknowledge that this is not an actual problem. It’s only the kind of thing that you’ll ever have to worry about if you work a disturbingly nit-picky job.

Since that’s the job that make my paychecks happen, and therefore controls my supply to coffee, I’m calling it a problem. Yes, by the way, ensuring that I always have funds to buy coffee is my entire motivation for working.

Here’s the thing. I don’t want to BCC all of them on the same email to prevent the next Reply All Fiasco of 2017 because that just looks impersonal. And we can’t have that.

I know what you’re thinking—just copy/paste the shit out of it. Control C, Control V. And repeat. But anytime I find myself doing that, I feel the neurons in my brain stop firing, and it frightens me. I’m getting to the age where I can’t guarantee those bastards will start firing again when I need them, so I need to limit my amount of mindless work in a day. Generally, I like a 60/40 split in favor of something that’s at least as engaging as a Sudoku puzzle.

I’m off-topic.

THE POINT IS, you can automate this through Excel.

The Code

Here’s the spreadsheet I’m using as an example.

The plan is to run this VBA from Excel, so you’ll have to start off by making an Application Object. To make that easier, we can dim (create) a variable OutApp and set it to Outlook.Application.

Dim OutApp As Object
Set OutApp = CreateObject("Outlook.Application")

Then we want to loop through all the email addresses, using a For Loop. Within that, we want an If statement to ask Excel if it senses an email address. All email addresses contain a some random characters, an @ sign, and a period. In order to isolate that pattern, we can use the Wildcard. That’s just a cool term for an asterisk that represents zero or more characters in an expression. Like so:

For Each cell In Columns("B").Cells
If cell.Value Like "*@*.*" Then

Nice, right? I feel like that wildcard is going to come in handy. So, for each email address, we’re going to create a new item object which we can call OutMail.

Set OutMail = OutApp.CreateItem(0)

Why is this set to 0? That’s the Outlook item type for a Mail Item. There’s a complete list of items here.

After that point, we can use a With… End With statement to simplify the lines that set all the different mail properties. Wondering what all the different properties are? There’s a list.

With OutMail
.To = cell.Value
.Subject = "Extremely Urgent"
.Body = "Good afternoon " & Cells(cell.Row, "A").Value & "," & vbNewLine & vbNewLine & Cells(2, 4)
.Importance = 2
.Display
End With

Change .Display to .Send if you don’t want to personalize the messages anymore than just having the person’s name in the opening line.  Within the body, you can use variables as normal, including cell references which in the above case are used to designate which cell holds the actual text that will go in the email.

Altogether now:

Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range

Application.ScreenUpdating = True
Set OutApp = CreateObject("Outlook.Application")

For Each cell In Columns("B").Cells
If cell.Value Like "*@*.*" Then
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = cell.Value
.Subject = "Extremely Urgent"
.Body = "Good afternoon " & Cells(cell.Row, "A").Value & "," & vbNewLine & vbNewLine & Cells(2, 4)
.Importance = 2
.Display
End With
End If
Next cell

Parting notes:
The above script is based heavily on one I found at Ron de Bruin Excel Automation. The main change was designating a specific cell for the body of the email to make it easier for me to customize later and re-use. I also took out the On Error lines because they were giving me some issues, and I remain uncertain as to why. And upped the importance to 2 because (believe it or not), some people actually notice if that’s flagged.