Mail Merge Pro
Introduction
Mail merge is a great time-saver and makes sending bulk emails much easier. But the default mail merge in Word has some limitations. I first started exploring this because I wanted to customize subject lines, but Word’s built-in feature didn’t allow it.
After trying out different solutions, I found one that worked and made a few tweaks to fit my needs. Now, it does exactly what I want! Below, I’ve put together the steps to install and use this macro so you can do the same.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
Sub MailMergePro()
' declare variables
Dim outlookApp As Outlook.Application
Dim outlookMail As Outlook.MailItem
Dim outlookAccount As Outlook.Account
Dim fso As FileSystemObject
Dim attachFile As File
Dim mm As MailMerge
Dim df As MailMergeDataField
Dim singleDoc As Document
Dim mailBody As String
Dim recordNum As Long
Dim sendFlag As Boolean
Dim hasDetailFlag As Boolean
Dim tempFileName As String
' identify the mail merge of the active document
Set mm = ActiveDocument.MailMerge
' check for the mail merge state being that of a mail merge ready to go
If mm.State <> wdMainAndDataSource Then
If MsgBox("Mailmerge not set up for active document - cannot perform mailmerge. Macro will exit.", vbOKOnly + vbCritical, "Error") = vbOK Then Exit Sub
End If
' Give the user an opportunity to abort, and also the option to save the emails in drafts, or send immediatly
Select Case MsgBox("MailMerge to email will proceed for " & mm.DataSource.RecordCount & " records." + Chr(10) + Chr(10) + _
"Click 'Yes' to send the emails immediatly, 'No' to save the emails in draft, and 'Cancel' to abort.", _
vbYesNoCancel + vbDefaultButton2 + vbQuestion, "Send Emails")
Case vbCancel
Exit Sub
Case vbYes
sendFlag = True
Case Else
sendFlag = False
End Select
' set variables
' outlookApp is used to control outlook to send an email
' fso is used to read the HTML file with the email content
Set outlookApp = New Outlook.Application
Set fso = New FileSystemObject
' we need to use a temporary file to store the html generated by mail merge
' fso.GetTempName creates a name with the extension tmp. We remove this
' because image files are stored in a folder with the name without the extension and with "_files" at the end
tempFileName = Replace(fso.GetTempName, ".tmp", "")
' loop through all the records
For recordNum = 1 To mm.DataSource.RecordCount
' select the record
mm.DataSource.ActiveRecord = recordNum
' run through the fields to check if a valid email address is provided in any of the "to", "cc" or "bcc" fields (valid address = contains an "@")
hasDetailFlag = False
For Each df In mm.DataSource.DataFields
Select Case Trim(LCase(df.Name))
Case "to", "cc", "bcc"
If InStr(1, df.Value, "@", vbTextCompare) > 0 Then
hasDetailFlag = True
Exit For
End If
End Select
Next
' only create an email if there is a valid address
If hasDetailFlag Then
' use mailmerge to create a new document for one record (defined by recordNum)
mm.Destination = wdSendToNewDocument
mm.DataSource.FirstRecord = recordNum
mm.DataSource.LastRecord = recordNum
mm.Execute Pause:=False
' save the generated doc as a html file in the temp directory
Set singleDoc = ActiveDocument
singleDoc.SaveAs2 FileName:=Environ("Temp") & "\" & tempFileName & ".tmp", FileFormat:=wdFormatFilteredHTML
singleDoc.Close
' read the html from the temp directory using fso
mailBody = fso.OpenTextFile(Environ("Temp") & "\" & tempFileName & ".tmp", 1).ReadAll
' create a new email message in outlook
Set outlookMail = outlookApp.CreateItem(olMailItem)
' ensure formatting is HTML
outlookMail.BodyFormat = olFormatHTML
' add the mail body from the html created via mailmerge and updated for the newly attached images
outlookMail.HTMLBody = mailBody
'outlookMail.Display
' run through all the fields in the mail merge data, when an email field is identified add the data to the appropriate field
For Each df In mm.DataSource.DataFields
' first check for the field being populated for the active record (row), only check if there is data provided
If Trim(df.Value) <> "" Then
' try matching the field name to accepted field names
' note that the field name is converted to lower case and trimmed to maximise chances of matching
Select Case Trim(LCase(df.Name))
Case "to"
' add in the to address or addresses as they are presented in the data, multiple address should be separated by a semicolon
outlookMail.To = df.Value
Case "cc"
' add in the cc address or addresses as they are presented in the data, multiple address should be separated by a semicolon
outlookMail.CC = df.Value
Case "bcc"
' add in the bcc address or addresses as they are presented in the data, multiple address should be separated by a semicolon
outlookMail.BCC = df.Value
Case "subject"
' add in the subject as it is presented in the data
outlookMail.Subject = df.Value
Case "importance"
' change the importance, accepted input values are "high", "normal", and "low" (not case sensitive)
' if field is not provided, or an incorrect input value is provided, then the default is used
' default is typically "Normal", but may have been changed in Outlook Options.
Select Case Trim(LCase(df.Value))
Case "high"
outlookMail.Importance = olImportanceHigh
Case "normal"
outlookMail.Importance = olImportanceNormal
Case "low"
outlookMail.Importance = olImportanceLow
End Select
Case "sensitivity"
' change the sensitivity, accepted input values are "confidential", "personal", "private", or "normal" (not case sensitive)
' if field is not provided, or an incorrect input value is provided, then the default is used
' default is typically "Normal", but may have been changed in Outlook Options.
Select Case Trim(LCase(df.Value))
Case "confidential"
outlookMail.Sensitivity = olConfidential
Case "personal"
outlookMail.Sensitivity = olPersonal
Case "private"
outlookMail.Sensitivity = olPrivate
Case "normal"
outlookMail.Sensitivity = olNormal
End Select
Case "readreceipt"
' request or do not request a read receipt
' if the field contains a boolean TRUE, or any form of "true"/"yes"/"y" (case insensitive) then request a read receipt
' if the field contains a boolean FALSE, or any form of "false"/"no"/"n" (case insensitive) then do not request a read receipt
' if field is not provided, or an incorrect input value is provided, then the default is used
' default is typically to not request a read receipt, but may have been changed in Outlook Options.
Select Case Trim(LCase(df.Value))
Case "true", "yes", "y"
outlookMail.ReadReceiptRequested = True
Case "false", "no", "n"
outlookMail.ReadReceiptRequested = False
End Select
Case "deliveryreceipt"
' request or do not request a delivery report
' if the field contains a boolean TRUE, or any form of "true"/"yes"/"y" (case insensitive) then request a delivery report
' if the field contains a boolean FALSE, or any form of "false"/"no"/"n" (case insensitive) then do not request a delivery report
' if field is not provided, or an incorrect input value is provided, then the default is used
' default is typically to not request a delivery report, but may have been changed in Outlook Options.
Select Case Trim(LCase(df.Value))
Case "true", "yes", "y"
outlookMail.OriginatorDeliveryReportRequested = True
Case "false", "no", "n"
outlookMail.OriginatorDeliveryReportRequested = False
End Select
Case "deliverytime"
' add in a delivery time (delay delivery)
' checks for the field containin a value or something which looks like a date and/or time
' if a datetime is provided, and that datetime is in the future then the delay is added to that datetime
' if a date is provided, and that date is in the future then the delay is added to midnight at the start of the provided date
' if a time is provided then the next instance of that time will be used to define the delay (so email could be sent "tomorrow" if time already passed)
' if no data, invalid data, or a date/datetime in the past is added then no delivery delay is added
If (IsNumeric(df.Value) Or IsDate(df.Value)) Then
If CDate(df.Value) < Now() - Date Then ' time only, time is in the past so set time for "tomorrow"
outlookMail.DeferredDeliveryTime = Date + 1 + CDate(df.Value)
ElseIf CDate(df.Value) < 1 Then ' time only, time is in the future so set time for "today"
outlookMail.DeferredDeliveryTime = Date + CDate(df.Value)
ElseIf CDate(df.Value) > Now() Then ' date or datetime in the future
outlookMail.DeferredDeliveryTime = CDate(df.Value)
End If
End If
Case "account"
' select the account from which the email is to be sent
' the account is identified by its full email address
' to identify the account, the code cycles through all the accounts available and selects a match
' if no data, or a non-matching email address is provided, then the default account is used
' note! not the same as send as - see below
For Each outlookAccount In outlookApp.Session.Accounts
If outlookAccount.SmtpAddress = df.Value Then
outlookMail.SendUsingAccount = outlookAccount
Exit For
End If
Next
Case Else
If Left(Trim(LCase(df.Name)), 10) = "attachment" And _
(Mid(Trim(df.Name), 11) = "" Or IsNumeric(Mid(Trim(df.Name), 11))) Then
' if the field name is "attachment" (not case sensitive) or is "attachment" followed only by numbers
' e.g. "Attachment1" then the filepath will be added as an attachment.
outlookMail.Attachments.Add df.Value
End If
End Select ' end test for the field names
End If ' end check for the data value being blank
Next df ' move on to the next record
' check the send flag and send or save
If sendFlag Then
outlookMail.Send
Else
outlookMail.Close (olSave)
End If
Set outlookMail = Nothing
End If ' end the test for whether a valid address is presented in the data
Next recordNum ' proceed to the next record and repeat
End Sub
Steps to Add a Macro in Word
Enable Developer Tab
1.1 Right-click anywhere on the ribbon (bar with options like file, home, insert, etc.) and select “Customize the Ribbon”.
1.2 In the dialog box, check the box next to “Developer” in the right-hand column.
1.3 Click OK to add the Developer tab to the ribbon.Open Visual Basic Editor
2.1 Click the Visual Basic button in the Developer tab.
2.2 If the Project Explorer window is not visible, go to View > Project Explorer.Insert a New Module
3.1 In the Project Explorer, right-click “Normal”, then select Insert > Module.
3.2 A new module (e.g., Module1) will appear under Normal.Add Macro Code
4.1 In the white editing space of the new module, paste the macro code.Add Required Libraries
5.1 Go to Tools > References in the VBA editor.
5.2 Check the boxes for:
5.2.1 Microsoft Outlook xx.x Object Library (replace xx.x with the version number).
5.2.2 Microsoft Scripting Runtime.
5.3 Click OK to apply the changes.
Steps to Prepare the Data Source
Set Up the Excel Sheet
1.1 Create an Excel sheet with headers in the first row and data underneath, just like a normal mail merge.
1.2 Add headers for email-specific fields such as To, CC, BCC, Subject, Importance, Sensitivity, ReadReceipt, DeliveryReceipt, DeliveryTime, Account, Attachment.Populate Essential Fields
2.1 Include at least one of To, CC, or BCC.
2.2 Add a Subject (recommended).
2.3 Other fields are optional and will use default settings if left blank.Format Email Fields
3.1 To, CC, and BCC should contain email addresses separated by a semicolon (;).
3.2 Subject can be any text, customized per recipient.
3.3 Importance should be normal, high, or low.
3.4 Sensitivity can be normal, personal, confidential, or private.
3.5 ReadReceipt & DeliveryReceipt: Use true, yes, or y to request, and false, no, or n to disable.Set Delivery Time (Optional)
4.1 Enter a datetime (e.g.,18/12/2020 13:30).
4.2 Enter a date (email will be sent at midnight on that date).
4.3 Enter a time (email will be sent at the next occurrence of that time).
4.4 Use formulas like=F2+G2(where F2 has a date and G2 has a time) to generate a datetime.Specify Sending Account (Optional)
5.1 Use the Account column to define the sender’s email address.Attach Files
6.1 Use the Attachment column to add file paths of attachments.
6.2 Multiple attachments can be added using multiple Attachment columns (e.g., Attachment1, Attachment2).
6.3 Right-click a file in Windows Explorer while holding Shift, then select “Copy as path” to get the file path.
6.4 Use Excel functions like VLOOKUP or MATCH to verify file paths and automate attachment assignments.
Steps to Prepare the Template and Run the Mail Merge
Set Up the Mail Merge Template
1.1 Create your mail merge document as you would for a standard email merge.Run the Macro
2.1 Go to the Developer tab and click Macros.
2.2 Select “MailMergePro” and click Run.Choose Email Sending Options
3.1 Click Cancel to stop the macro if a mistake is found.
3.2 Click No to generate emails and save them in the Drafts folder without sending.
3.3 Click Yes to send all emails immediately.Wait for Completion
4.1 The macro will process and send (or save) the emails automatically.