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 columns to your Mail Merge data: To, CC BCC, Subject, Importance, Sensitivity, ReadReceipt, DeliveryReceipt, DeliveryTime,FollowUp, Account, SendAs, Attachment.
- To, CC and BCC should contain email addresses separated by a semi-colon (multiple columns can be used if preferred)
- 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)
- FollowUp is used to flag the message for a follow up – if a number is provided in this column it indicates the number of days from today for the follow up, if date (in the future) is provided the follow up will be on that date
- 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
- Omitting a field or leaving it blank will leave that field in the email as the default value
- Multiple To, CC, BCC and Attachment columns can be used
- Create your Mail Merge.
- Run the Macro
- Sit back and let the computer work.
Errors in the source data will cause the email to be saved as a draft with the errors listed in the body of the email (e.g. “attachment does not exist”). Major errors will cause an error box with an error number and text. If you get a popup box, please contact Imnoss.
The Macro
****** UPDATE! – Windows code updated from what is shown in the video ******
Original code at bottom of article.
Sub EnhancedMailMergeToEmail() ' Macro created by Imnoss Ltd ' Please share freely while retaining attribution ' Last Updated 2022-09-22 ' Please make sure you copy all the text in this code snippit box, including the function "StripToLcaseLetters" below this sub. ' declare variables Dim outlookApp As Object 'Outlook.Application Dim outlookMail As Object 'Outlook.MailItem Dim outlookAccount As Object 'Outlook.Account Dim mm As MailMerge Dim df As MailMergeDataField Dim singleDoc As Document Dim lastRecordNum As Long Dim sendFlag As Boolean Dim inputDate As Date Dim toString As String Dim ccString As String Dim bccString As String Dim subjectString As String Dim errorString As String Dim contOnError As Boolean Dim fso As Object ' 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." & _ vbLf & vbLf & "Please click the Edit Recipient List button To confirm that mail merge is correctly setup for this document." _ , 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 has found " & mm.DataSource.RecordCount & " records." & vbLf & vbLf & _ "Macro will ignore records that have been unchecked in the 'Edit Recipients' dialog box." & vbLf & vbLf & _ "Click 'Yes' To send the emails immediately, '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 ActiveDocument.Content.Characters(1).Select ' set outlookApp which is used To control outlook To send an email ' use late binding so that if the reference is not added there is not an error Set outlookApp = CreateObject("Outlook.Application") ' go To the last record and record the record number ' if records are unchecked then the last record may not equal the record count mm.DataSource.ActiveRecord = wdLastRecord lastRecordNum = mm.DataSource.ActiveRecord mm.DataSource.ActiveRecord = wdFirstRecord ' loop through all the records Do While lastRecordNum > 0 ' set fields To be populated for each email errorString = "" toString = "" ccString = "" bccString = "" subjectString = "" ' 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 ' extract the "MailEnvelope" from the word doc, which is an outlook email containing the contents of the doc Set outlookMail = singleDoc.MailEnvelope.Item ' 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 Select Case StripToLcaseLetters(df.Name) Case "to" ' add in the To address or addresses as they are presented in the data, if multiple To fields are present they will all be added, separated by a semicolon If InStr(1, df.Value, "@", vbBinaryCompare) > 0 Then toString = toString & ";" & df.Value Else errorString = errorString & vbCrLf & "Invalid email address in To field: " & df.Value End If Case "cc" ' add in the CC address or addresses as they are presented in the data, if multiple CC fields are present they will all be added, separated by a semicolon If InStr(1, df.Value, "@", vbBinaryCompare) > 0 Then ccString = ccString & ";" & df.Value Else errorString = errorString & vbCrLf & "Invalid email address in CC field: " & df.Value End If Case "bcc" ' add in the bcc address or addresses as they are presented in the data, if multiple BCC fields are present they will all be added, separated by a semicolon If InStr(1, df.Value, "@", vbBinaryCompare) > 0 Then bccString = bccString & ";" & df.Value Else errorString = errorString & vbCrLf & "Invalid email address in BCC field: " & df.Value End If Case "subject" ' add in the subject as it is presented in the data If subjectString = "" Then subjectString = df.Value Else errorString = "Second subject field found containing: " & df.Value End If 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 StripToLcaseLetters(df.Value) Case "" ' leave as default Case "low", "l" outlookMail.Importance = 0 'olImportanceLow Case "normal", "n" outlookMail.Importance = 1 'olImportanceNormal Case "high", "h" outlookMail.Importance = 2 'olImportanceHigh Case Else errorString = errorString & vbCrLf & "Importance value not recognised: " & df.Value 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 StripToLcaseLetters(df.Value) Case "" ' leave as default Case "normal" outlookMail.Sensitivity = 0 'olNormal Case "personal" outlookMail.Sensitivity = 1 'olPersonal Case "private" outlookMail.Sensitivity = 2 'olPrivate Case "confidential" outlookMail.Sensitivity = 3 'olConfidential Case Else errorString = errorString & vbCrLf & "Sensitivity value not recognised: " & df.Value 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 StripToLcaseLetters(df.Value) Case "" ' leave as default Case "true", "yes", "y" outlookMail.ReadReceiptRequested = True Case "false", "no", "n" outlookMail.ReadReceiptRequested = False Case Else errorString = errorString & vbCrLf & "Read receipt value not recognised: " & df.Value 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 "" ' leave as default Case "true", "yes", "y" outlookMail.OriginatorDeliveryReportRequested = True Case "false", "no", "n" outlookMail.OriginatorDeliveryReportRequested = False Case Else errorString = errorString & vbCrLf & "Delivery receipt value not recognised: " & df.Value End Select Case "deliverytime", "delaydelivery" ' 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"). However, CDate will convert both "15/1/2021" and "1/15/2021" To 15 January 2021 ' irrespecitve of location settings. ' 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 _ (df.Value Like Format(inputDate, "d/m/yyyy") & "*" Or df.Value Like Format(inputDate, "dd/mm/yyyy") & "*") 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 Else errorString = errorString & vbCrLf & "Delivery time value not recognised: " & df.Value 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 Exit For Next If Not outlookAccount Is Nothing Then outlookMail.SendUsingAccount = outlookAccount Else errorString = errorString & vbCrLf & "Account not found: " & df.Value End If Case "sendas", "sendonbehalfof" ' 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 Else errorString = errorString & vbCrLf & "Send as email not recognised: " & df.Value End If 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) Else errorString = errorString & vbCrLf & "Reply To email not recognised: " & df.Value End If Case "followup" outlookMail.FlagStatus = olFlagMarked outlookMail.FlagRequest = "Follow up" 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"). However, CDate will convert both "15/1/2021" and "1/15/2021" To 15 January 2021 ' irrespecitve of location settings. ' 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 _ (df.Value Like Format(inputDate, "d/m/yyyy") & "*" Or df.Value Like Format(inputDate, "dd/mm/yyyy") & "*") 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.FlagDueBy = Date + 1 + inputDate ElseIf inputDate < 1 Then ' time only, time is in the future so set time for "today" + days/time outlookMail.FlagDueBy = Date + inputDate ElseIf inputDate < 5000 Then ' assume count of days To follow up (e.g. 7), add To current date and time outlookMail.FlagDueBy = Now() + inputDate ElseIf inputDate <= Now() Then ' date or datetime in the past, set To due now (annoying, but anyhooose) outlookMail.FlagDueBy = Now() Else ' date/datetime in future, use that value outlookMail.FlagDueBy = inputDate End If Else errorString = errorString & vbCrLf & "Delivery time value not recognised: " & df.Value End If Case "attachment", "attachments" ' create a file system object To check the file ' if the file does not exist, then the attachment is not added and the error string is updated If fso Is Nothing Then Set fso = CreateObject("Scripting.FileSystemObject") If fso.FileExists(df.Value) Then ' add the attachment outlookMail.Attachments.Add df.Value Else errorString = errorString & vbCrLf & "Attachment: " & df.Value & " could not be found" 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 ' set the collected To, CC and bcc fields (some systems do not allow the To field To be read ' so we have To collect all To fields together first and then add them in one go If Len(toString & ccString & bccString) = 0 Then errorString = errorString & vbCrLf & "No valid email addresses provided in To, CC and BCC fields" If Len(subjectString) = 0 Then errorString = errorString & vbCrLf & "No subject provided" outlookMail.To = toString outlookMail.cc = ccString outlookMail.BCC = bccString outlookMail.Subject = subjectString outlookMail.Display ' outlookMail.UserProperties.Add "Generator", olText, True ' outlookMail.UserProperties.Find("Generator").Value = "Imnoss" ' check the send flag and send or save If Len(errorString) > 0 Then singleDoc.Content.Text = "Errors found: " & errorString outlookMail.BodyFormat = 1 'olFormatPlain outlookMail.Subject = "**Errors in mail merge: " & subjectString outlookMail.Close 0 'olSave If Not contOnError Then If MsgBox("Errors were found when creating email, email has been saved as a draft with subject """ & _ "**Errors in mail merge: " & subjectString & """ and a full list of errors in the body of the email" & vbLf & vbLf & _ "Press OK To continue with remaining records and Cancel To exit the macro.", vbOKCancel, "Errors in email") = vbCancel Then Exit Sub End If contOnError = True End If ElseIf sendFlag Then outlookMail.Send Else outlookMail.Close 0 'olSave End If Set outlookMail = Nothing singleDoc.Close False Set singleDoc = Nothing ' 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 Private Function StripToLcaseLetters(inputString As String) As String ' strips out all non-letters and converts To lowercase Dim i As Long Dim s As String For i = 1 To Len(inputString) Select Case Asc(Mid(inputString, i, 1)) Case 65 To 90, 97 To 122 s = s & Mid(inputString, i, 1) End Select Next i StripToLcaseLetters = LCase(s) End Function
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.
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.
Original code shown in video
This code is provided for reference. If you get an error with the updated code above you will almost certainly get an error with the code below.
Sub OriginalEnhancedMailMergeToEmail() ' Macro created by Imnoss Ltd ' Please share freely while retaining attribution ' 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 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 ' if the html contains images, then they will be stored in a files directory ' this loop iterates through the files in the files directory ' it checks 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 For Each attachFile In fso.GetFolder(Environ("Temp") & "\" & tempFileName & "_files").Files If InStr(1, mailBody, "src=""" & tempFileName & "_files/" & attachFile.Name & """", vbBinaryCompare) > 0 Then outlookMail.Attachments.Add attachFile.Path, 1, 0 mailBody = Replace(mailBody, "src=""" & tempFileName & "_files/" & attachFile.Name & """", "src=""cid:" & attachFile.Name & """") End If Next ' 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 "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 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
We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept”, you consent to the use of ALL the cookies.