Free Advanced Mail Merge Tool: CC, BCC, Attachments, Shared Mailboxes, Directly From Outlook

Written By Sarah Minkler

Are you looking to streamline your email campaigns and send personalized emails effortlessly? Look no further! With our advanced Excel VBA mail merge tool, you can automate your email process, including attachments, CC, BCC, and even sending from shared mailboxes. Best of all, you don't need Microsoft Word – everything is handled within Excel and Outlook.

In this guide, we’ll walk you through the main steps to set up and use this powerful tool.

Step 1: Enable the Developer Tab in Excel

To write and run VBA code, you first need to enable the Developer tab in Excel:

  1. Open Excel and go to the File menu.

  2. Select Options.

  3. In the Excel Options window, select Customize Ribbon.

  4. Check the box next to Developer in the right pane.

  5. Click OK.

Step 2: Set Up Your Personal Macro Workbook

The Personal Macro Workbook allows you to store macros and VBA code that you can use across all your Excel workbooks:

  1. Click on the Developer tab in the Excel ribbon.

  2. Click on Record Macro in the Code group.

  3. In the Record Macro dialog box:

    • Set Macro name to Test.

    • Set Store macro into Personal Macro Workbook.

    • Click OK.

  4. Perform any simple action in Excel (e.g., type something in a cell - it really does not matter what you do)

  5. Click on Stop Recording in the Developer tab. This will create and save the Personal Macro Workbook.

  6. FYI - This step just helped make the personal.xlsb file visible which we need. It is typically hidden.

Step 3: Add the VBA Code

Copy and paste the provided VBA code into your Personal Macro Workbook:

  1. Press Alt + F11 to open the VBA editor.

  2. In the Project Explorer, find your Personal Macro Workbook.

  3. Right-click on Modules and select Insert > Module.

  4. Paste the VBA code into the new module.

Step 4: Download and Set Up the Template

Download the sample Excel spreadsheet template to structure your mail merge data:

  1. Download Sample Spreadsheet

  2. Fill in your data, ensuring you include columns for Email, CC, BCC, Attachment, and any other placeholders you’ll use in your email.

  3. It’s important to keep the tabs of the Excel named the same.

Step 5: Set Up the Variables Tab

The Variables tab in your Excel sheet is where you specify key details for your mail merge:

  1. C2: Enter the subject of your draft email.

  2. C3: Enter the shared mailbox email address you’ll be sending from (if applicable). Otherwise, just put your email address here.

  3. C4: Put the email you’d like the test email to be sent to. This is typically your own email.

This ensures that your mail merge process has all the necessary information to function correctly.

Step 6: Write Your Draft Email in Outlook

Create and save a draft email in Outlook:

  1. Open the Outlook App and create a new email. (You cannot use “New” Outlook).

  2. Write your email body, using placeholders (e.g., {{FirstName}}, {{Balance}}) that correspond to the columns in your Excel sheet.

  3. Set the subject to match the subject you specified in your Variables tab. You can include placeholders in your subject as well.

  4. Attach any common attachment to the draft email.

  5. Save the email as a draft.

Step 7: Send Your Mail Merge

Run the mail merge process to send personalized emails:

  1. In Excel, press Alt + F8 to open the Macro dialog box.

  2. Select the SendTestEmail macro and click Run to send a test to the email in Variables tab cell C4.

  3. Select the SendMailMerge macro and click Run to send your entire mail merge.

  4. Watch as your personalized emails are sent out seamlessly!

Watch the Tutorial Video

For a detailed walkthrough, check out our step-by-step video tutorial:

Watch the Video on YouTube

By following these steps, you can leverage the power of Excel VBA to send customized emails with ease. This tool is perfect for businesses, marketers, and anyone looking to automate their email processes efficiently.

#MailMerge #ExcelVBA #EmailAutomation #Outlook

Sarah Minkler