What it does
Uses a settings sheet (Email_Merge) to help you quickly send out tailored emails based on a Word template.  The Word template can either be copied to the body of the email and/or attached to the email.  Placeholders in the Word file can be updated based on values in the Email_Merge sheet.  The tailored Word template can be saved as a Word file or a PDF before it is saved then attached to each email created.  It acts much like the Mail Merge functionality between Excel and Word, but it is easier to set up and use, and can send via email, not just print.

When to use it
When you want to send tailored messages in an efficient and controlled way, this is a great way to do it.  Example use cases are client statements, invoices, updates, Christmas letters, etc.

Why to use it
It’s an extremely quick way to send tailored messages with/without attachments.  Many professionals spend a significant amount of their working hours drafting very repetitive messages and attachments, and this helps do so very efficiently.

Default shortcut
None

Other Details

• Category: MS Office / Outlook
• Difficulty: 5/5
• Usage/frequency: 1/5
• Automation factor: 5/5 (estimated 300 seconds saved each time used)
• Type: Bulk
• Date added: 12/5/2021
• Tags: Outlook, email, bulk, Word, template, attachment

Related Macros
Create Send Email Messages
File Email Messages

Other Articles
Managing Email Taking Up All Your Time? You’re Not Alone!

Email Merge Example File
Email Merge Word Template Example File

Prerequisites
Create a template in Word that you want to use for the body of emails and/or attach to emails.  Use placeholders for any text that you want to change for each letter to create, such as names, amounts, dates, identifiers, addresses, phone numbers, etc.

Instructions
Run the Email Merge macro.  It will create the Email_Merge sheet, which is where you will specify details for the emails (i.e. who to send to), and any placeholder fields you want to set up.  Below are the fields in the Email_Merge sheet you’ll want to use:

• Column B – Send To (optional): who the email should be send to.  At least one of the Send To, Send CC, or Send BCC fields is required for each row if creating or sending emails.
• Column C – Send CC (optional): who the email should be send to as CC.  At least one of the Send To, Send CC, or Send BCC fields is required for each row if creating or sending emails.
• Column D – Send BCC (optional): who the email should be send to as BCC.  At least one of the Send To, Send CC, or Send BCC fields is required for each row if creating or sending emails.
• Column E – From (optional): select from the drop-down list which email account you want to send from, if you have multiple.  If blank, the email account listed as #1 in the drop-down list will be used if creating or sending emails.
• Column F – Subject (required if creating/sending email): enter a descriptive subject for the email. If variable information is needed in the subject, such as a location #, date, name, etc., include cell references in the formula for the subject.
• Column G – Body (Message) (optional): enter the content of the email message you want to send.  Alternatively, use column L to leverage a Word template with placeholders (see below) that are updated/tailored for each row.  The contents of the tailored Word file will be copied to the email body.  If column L is used, column G will be ignored.
• Column H – Importance (optional): select the importance type for the email from the drop-down list.  The default is 1 (normal importance).
• Column I – Tailor/Create/Send (required): select Tailor to just create tailored Word/PDF files from the Word template; select Create to create the emails and tailored Word/PDF attachments; or select Send to also send the emails/attachments.
• Column J – Send Date/Time (optional): to schedule delivery of one or more of the messages, enter the send date and time in a MM/DD/YYYY HH:MM AM/PM format.  If only creating the emails/attachments, this is ignored.  If this field is blank and Send is entered in column I, the emails/attachments are sent when processed.
• Column K – Attachments (optional): to include attachments other than the tailored Word/PDF file, enter the entire path of the file(s) to attach, separating multiple files with a semi-colon (“;”).  It is recommended to link file paths and other variable fields to inputs and save the file as a template that can be reused.
• Column L – Body Message Template  (optional): to use a Word template to draft the body of the email message, enter the path of the Word template that contains placeholders that should be used to tailor the body of the email.  If no tailored email body is needed, any text in column G is used.
• Column M – Attached Message Template  (optional): to use a Word template to create a tailored Word/PDF file to attach, enter the path of the Word template that contains placeholders that should be used to tailor the Word/PDF file.  If column M is used, column N is also required.
• Column N – Attached Message Path  (optional): when using a Word template to create a tailored Word/PDF file to attach, enter the path where the tailored Word/PDF file should be saved to.  Column M is also required when using column N.
• Columns O:Q (and to the right as needed) – Field Values (optional): for any placeholders in the Word template you want to replace, enter the placeholder text in the header (row 1) of columns O and as many columns to the right as are needed, leaving no gaps.  Then for each tailored Word/PDF file, enter the value that should replace the placeholder text.  The placeholder text in row 1 should match the placeholder text in the Word template.  If a placeholder column is used and the values below are blank, the placeholder text will be replaced with a blank value.

After drafting the Word template and populating the fields on the Email_Merge sheet, run the Email Merge macro to process the contents, create/send emails and/or create tailored Word/PDF files, replacing any placeholder text.

Consider linking any variable items on the Email_Merge sheet to inputs and saving the workbook as a template to reuse over and over again.