How To ...

Enhanced Mail Merge to Email – add Attachments, Custom Subject, Save to Drafts and more

Use Word mail merge to send emails, with all the missing features added back in. Features like adding attachments, having a custom subject, using the CC and BCC fields, using "Send As" (e.g. send as the CEO - you know you want to), and best of all you can save to drafts to check the emails before they go out.

Introduction

Word’s mail merge feature gives you the ability to send emails, but the system lacks many essential features. Below we will provide you with a Word Macro and take you through how you can use it to create and send emails using Word mail merge with all the missing features available to you.

 

The Video

The Super Quick Version

  • Copy the text of the Macro from below and add to Word.
  • Add any of the following fields to your Mail Merge data: To, CC BCC, Subject, Importance, Sensitivity, ReadReceipt, DeliveryReceipt, DeliveryTime, Account, SendAs, Attachment.
    • To, CC and BCC should contain email addresses separated by a semi-colon
    • Subject can be whatever you want, and individualised to the recipient
    • Importance can be normal, high, or low
    • Sensitivity can be normal, personal, confidential, and private
    • ReadReceipt and DeliveryReceipt can be true, yes or y to request, or false, no or n to not request
    • DeliveryTime can be a datetime, a date (email will be sent at midnight on that date) or time (email will be sent at next instance of the specified time)
    • Account is the email address of the account from which the email is to be sent (not send as, see below)
    • SendAs is the email address of the person or team from which the email should appear to be sent from, or sent on behalf of
    • Attachment is the full file path of an attachment, multiple columns can be used for multiple attachments
    • Note: Omitting a field or leaving it blank will leave that field in the email as the default value
  • Create your Mail Merge. 
  • Run the Macro
  • Sit back and let the computer work.

The Macro

******     UPDATE! – Windows code updated from what is shown in the video     ******

				
					Sub EnhancedMailMergeToEmail()
' Macro created by Imnoss Ltd
' Please share freely while retaining attribution
' Last Updated 2021-11-06

' REFERENCES REQUIRED!
' This Macro requires you to add the following libraries:
' "Microsoft Outlook xx.x Object Library" (replace xx.x with version number) and "Microsoft Scripting Runtime"
' To add them, use the "Tools" menu and select "References". Tick the check boxes next to the two libraries and press OK.

    ' declare variables
    Dim outlookApp As Outlook.Application
    Dim outlookMail As Outlook.MailItem
    Dim outlookAccount As Outlook.Account
    Dim fso As FileSystemObject
    Dim f As Object
    Dim attachFile As File
    Dim mm As MailMerge
    Dim df As MailMergeDataField
    Dim singleDoc As Document
    Dim mailBody As String
    Dim lastRecordNum As Long
    Dim recordCount As Long
    Dim sendFlag As Boolean
    Dim validRowFlag As Boolean
    Dim tempFileName As String
    Dim tempFolderName As String
    Dim fieldName As String
    Dim inputDate As Date
    ' 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

    ' set lastRecordNum to the number of the last active record (reached using wdLastRecord
    mm.DataSource.ActiveRecord = wdLastRecord
    lastRecordNum = mm.DataSource.ActiveRecord
    ' if the lastRecordNum is less than 50 we assume some may have been deselected so we count only the active records
    ' counting more than 50 records takes too long
    If lastRecordNum < 50 Then
        mm.DataSource.ActiveRecord = wdFirstRecord
        recordCount = 0
        Do While True
            ' 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 "@")
            ' also detect if the row is marked to be ignored
            validRowFlag = False
            For Each df In mm.DataSource.DataFields
                ' clean up the provided field name by running through the name letter by letter and adding only letters to the variable fieldName
                fieldName = ""
                For i = 1 To Len(df.Name)
                    Select Case Asc(LCase(Mid(df.Name, i, 1)))
                        Case 97 To 122
                            fieldName = fieldName & LCase(Mid(df.Name, i, 1))
                    End Select
                Next i
                Select Case fieldName
                    Case "ignore"
                        Select Case LCase(df.Value)
                            Case "true", "yes", "y", "ignore"
                                validRowFlag = False
                                Exit For
                        End Select
                    Case "to", "cc", "bcc"
                        If InStr(1, df.Value, "@", vbTextCompare) > 0 Then
                            validRowFlag = True
                        End If
                End Select
            Next
            If validRowFlag Then
                recordCount = recordCount + 1
            End If
            If mm.DataSource.ActiveRecord = lastRecordNum Then
                Exit Do
            Else
                mm.DataSource.ActiveRecord = wdNextRecord
            End If
        Loop
    Else
        recordCount = lastRecordNum
    End If
    If recordCount = 0 Then
        If MsgBox("Cannot find any active / valid / not to be ignored records. 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 immediately
    Select Case MsgBox("MailMerge to email will proceed for " & IIf(recordCount < 50, recordCount & " active", 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", "")
    mm.DataSource.ActiveRecord = wdFirstRecord
    recordCount = 0
    ' loop through all the records
    Do While lastRecordNum > 0
        ' 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 "@")
        ' also detect if the row is marked to be ignored
        validRowFlag = False
        For Each df In mm.DataSource.DataFields
            ' clean up the provided field name by running through the name letter by letter and adding only letters to the variable fieldName
            fieldName = ""
            For i = 1 To Len(df.Name)
                Select Case Asc(LCase(Mid(df.Name, i, 1)))
                    Case 97 To 122
                        fieldName = fieldName & LCase(Mid(df.Name, i, 1))
                End Select
            Next i
            Select Case fieldName
                Case "ignore"
                    Select Case LCase(df.Value)
                        Case "true", "yes", "y", "ignore"
                            validRowFlag = False
                            Exit For
                    End Select
                Case "to", "cc", "bcc"
                    If InStr(1, df.Value, "@", vbTextCompare) > 0 Then
                        validRowFlag = True
                    End If
            End Select
        Next
        ' only create an email if there is a valid addressa and the row is not marked as to be ignored
        If validRowFlag Then
            ' use mailmerge to create a new document for one record (defined by mm.DataSource.ActiveRecord)
            mm.Destination = wdSendToNewDocument
            mm.DataSource.FirstRecord = mm.DataSource.ActiveRecord
            mm.DataSource.LastRecord = mm.DataSource.ActiveRecord
            mm.Execute Pause:=False
            ' save the generated doc as a html file in the temp directory
            Set singleDoc = ActiveDocument
            singleDoc.SaveAs2 FileName:=Environ("Temp") & Application.PathSeparator & tempFileName & ".tmp", FileFormat:=wdFormatFilteredHTML
            singleDoc.Close SaveChanges:=wdDoNotSaveChanges
            Set singleDoc = Nothing
            ' read the html from the temp directory using fso
            mailBody = fso.OpenTextFile(Environ("Temp") & Application.PathSeparator & tempFileName & ".tmp", 1).ReadAll
            ' create a new email message in outlook
            Set outlookMail = outlookApp.CreateItem(olMailItem)

            ' display the email so that any images display correctly
            outlookMail.Display
            ' clear the content of the email and remove all attachments (i.e. clear the signature and any images in the signature)
            outlookMail.HTMLBody = ""
            Do While outlookMail.Attachments.Count > 0
                outlookMail.Attachments.Remove 1
            Loop
            ' ensure formatting is HTML
            outlookMail.BodyFormat = olFormatHTML
            ' if the html contains images, then they will be stored in a directory called
            ' tempFileName followed by the _files in the local language (e.g. _bestanden in Dutch)
            ' so we need to find the directory, and the loop through each of the files
            ' checking to see if the files are included in the email as an image (i.e. as 'src="..."')
            ' if the image is included then the image is attached to the email as a hidden attachment
            ' and the image path is updated to point to the attached image
            ' try and find the temporary folder name which would contain any images
            tempFolderName = ""
            For Each f In fso.GetFolder(Environ("Temp")).SubFolders
                If Left(f.Name, Len(tempFileName) + 1) = tempFileName & "_" Then
                    tempFolderName = f.Name
                    Exit For
                End If
            Next
            ' if the folder has been found, iterate through the files
            If tempFolderName <> "" Then
                For Each attachFile In fso.GetFolder(Environ("Temp") & Application.PathSeparator & tempFolderName).Files
                    If InStr(1, mailBody, "src=""" & tempFolderName & "/" & attachFile.Name & """", vbBinaryCompare) > 0 Then
                        outlookMail.Attachments.Add attachFile.Path, 1, 0
                        mailBody = Replace(mailBody, "src=""" & tempFolderName & "/" & attachFile.Name & """", "src=""cid:" & attachFile.Name & """")
                    End If
                Next
            End If
            ' add the mail body from the html created via mailmerge and updated for the newly attached images
            outlookMail.HTMLBody = mailBody

            ' 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
                    ' the field name is cleaned up by running through the name letter by letter and adding only letters to the variable fieldName
                    fieldName = ""
                    For i = 1 To Len(df.Name)
                        Select Case Asc(LCase(Mid(df.Name, i, 1)))
                            Case 97 To 122
                                fieldName = fieldName & LCase(Mid(df.Name, i, 1))
                        End Select
                    Next i
                    Select Case fieldName
                        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 = 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 = 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 = 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
                                
                                ' A date passed from an Excel table through mail merge will be formatted in US format ("m/d/yyyy"), but the function CDate
                                ' uses the local format, e.g. ("d/m/yyyy"). CDate is nice enough to recognise (and not error) when fed a date with the day > 12,
                                ' so both "15/1/2021" and "1/15/2021" will produce the correct date output (15 January 2021).
                                ' The next couple of lines test for whether the date is the wrong way round and flips the month and day if needed
                                ' A date is believed to be wrong if both month and day are 12 or lower, if CDate parses the date 1/2/2020 as 1 February 2020
                                ' and finally if the raw input from Excel is a date string (and not a number, which would be valid)
                                inputDate = CDate(df.Value)
                                If Day(inputDate) <= 12 And Month(inputDate) <= 12 And Month(CDate("1/2/2020")) = 2 And _
                                        InStr(1, df.Value, Format(inputDate, "d/m/yyyy")) = 1 Then
                                    inputDate = DateSerial(Year(inputDate), Day(inputDate), Month(inputDate)) + TimeSerial(Hour(inputDate), Minute(inputDate), Second(inputDate))
                                End If
                                
                                If inputDate < Now() - Date Then      ' time only, time is in the past so set time for "tomorrow"
                                    outlookMail.DeferredDeliveryTime = Date + 1 + inputDate
                                ElseIf inputDate < 1 Then             ' time only, time is in the future so set time for "today"
                                    outlookMail.DeferredDeliveryTime = Date + inputDate
                                ElseIf inputDate > Now() Then         ' date or datetime in the future
                                    outlookMail.DeferredDeliveryTime = inputDate
                                End If
                                Debug.Print df.Value, outlookMail.DeferredDeliveryTime
                            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 "sendas"
                            ' add in an address to send as or send on behalf of
                            ' only added if a valid email address
                            ' if the account does not have permissions, the email will be created but will be rejected by the Exchange server if sent
                            If InStr(1, df.Value, "@", vbTextCompare) > 0 Then outlookMail.SentOnBehalfOfName = df.Value
                        Case "replyto"
                            ' add in an address to reply to
                            ' only added if a valid email address
                            If InStr(1, df.Value, "@", vbTextCompare) > 0 Then outlookMail.ReplyRecipients.Add (df.Value)
                        Case "attachment"
                            ' add the attachment
                            outlookMail.Attachments.Add df.Value
                    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
        Else
            recordCount = recordCount + 1   ' keep a tally of skipped records using recordCount
        End If      ' end the test for whether a valid address is presented in the data

        ' test if we have just created a document for the last record, if so we set lastRecordNum to zero to indicate that the loop should end, otherwise go to the next active record
        If mm.DataSource.ActiveRecord >= lastRecordNum Then
            lastRecordNum = 0
        Else
            mm.DataSource.ActiveRecord = wdNextRecord
        End If
    Loop
End Sub

				
			

** Note ** The code has been updated from what is shown in the video. 

The Long Version

Adding the Macro

To add a macro to Word you will need to be able to see the Developer tab. If you don’t have this appearing, that’s perfectly normal for Word, they hide it by default. To make it appear is you just right-click anywhere in the ribbon and select “Customise the Ribbon”. In the dialogue box which appears make sure the checkbox next to Developer in the right-hand column is checked. Once done press Ok, and the Developer tab will appear in the Ribbon. This contains the Visual Basic and Macro buttons on the left-hand side.

Click the Visual Basic button in the Developer tab to open the Visual Basic editor where we will add our macro. If you do not see a window called “Project” then click on the View menu and select “Project Explorer”. This will bring up the Project window. In this window right-click on “Normal” and in the contextual menu select “Insert” and then “Module”. A new file called “Module1” will appear in the Modules folder under “Normal” (the file may have a different number if Module1 already exists). 

The middle of the window will be a white canvas – this is the editing space for the new module. Copy the Macro from this article and paste it into the middle of the VBA editor.

Finally, you will need to add two libraries: Microsoft Outlook xx.x Object Library (replace xx.x with version number) and Microsoft Scripting Runtime.  To add them, use the “Tools” menu and select “References”. Tick the checkboxes next to the two libraries and press OK.

Preparing the Data

Start by preparing your data exactly as you would for a normal mail merge – i.e. a sheet in Excel with headers in the first row and data underneath.

Add in headers for the email fields you want to add or change: To, CC BCC, Subject, Importance, Sensitivity, ReadReceipt, DeliveryReceipt, DeliveryTime, Account, SendAs, Attachment. 

You will need at least one of To, CC and BCC, and a Subject is recommended, all the other fields are optional and if omitted or left blank they will remain at your defaults. To populate these fields you can type the data in or use formulas. 

  • To, CC and BCC should contain email addresses separated by a semi-colon
  • Subject can be whatever you want and individualised to the recipient
  • Importance can be normal, high, or low
  • Sensitivity can be normal, personal, confidential, and private
  • ReadReceipt and DeliveryReceipt can be true, yes or y to request, or false, no or n to not request
  • DeliveryTime can be:
    • a datetime (a combination of date and time)
    • a date (email will be sent at midnight on that date) or
    • a time (email will be sent at next instance of the specified time) –
    • a datetime can by typed into Excel as a date followed by a time (e.g. “18/12/2020 13:30”) or created by adding a date to a time in a formula (e.g. “=F2+G2”, where F2 contains a date and G2 a time)
  • Account is the email address of the account from which the email is to be sent (not send as, see below)
  • SendAs is the email address of the person or team from which the email should appear to be sent from, or sent on behalf of
  • Attachment is the full file path of an attachment, multiple columns can be used for multiple attachments

Multiple “Attachment” fields (i.e. multiple columns) can be used. If you wish to give the columns different names put a number after the word Attachment and it will still work (note, only a number, so Attachment1 works, Attachment_1 does not work).

The attachments are identified by their file path. Holding Shift while right-clicking on a file in Windows Explorer will give you the option to “Copy as path” which will copy that file path to the clipboard to be pasted into Excel. You can use this technique after selecting many files. Copying and pasting in multiple file paths enables you to use VLOOKUP or MATCH to check the attachments are available and to add them to the table (see video for “VLOOKUP with Wildcards” technique). 

Preparing the Mail Merge Template

Prepare your mail merge template as you would any other mail merge to email. The email specific fields can be ignored. 

Prepare your mail merge template in Word as for a normal mail merge - there is no need to add the extra fields anywhere

Running the Mail Merge to PDF

In the Developer tab click the “Macros” button. Select the macro “EnhancedMailMergeToEmail” and click Run. 

  • If you’ve made a mistake, click “Cancel” and the macro will stop,
  • If you want to create the emails without sending them and have them stored in drafts then click “No”
  • If you are happy to send all the emails, click “Yes”.

Sit back and let the magic happen.

Share This Post

Share on facebook
Share on linkedin
Share on twitter
Share on email