How To Use Mail Merge with Gmail in Google Sheet

Mail merge is a easy way to originating with Outlook that enables you to take data from an external source like Google Sheets and create personalized emails to multiple recipients. If you run a small business or want to publicize an event, this is a great way to mass mail people while making it look like you sent each one personally. That personal touch can have a seriously positive impact on responses.

I don’t know anyone who doesn’t have a Gmail account. Whether they use it much or not, most of us has a Google Account of some kind with all the features it brings with it. So Gmail is a good logical place to set up such a campaign. I will show you how here.

To use mail merge effectively, we need a template full of email addresses, names and any attachments. This is the data Gmail will pull to create the mail campaign for you. Before we set up the campaign itself, we need to populate a Google Sheet with this data. Then we configure Gmail to use it.

Use Mail Merge For Gmail

For this to work you will need the Mail Merge add-on for Google Sheets. Install it and we are good to go.

First we need to create the spreadsheet from which Gmail pulls the data for the merge.

1. Open a Google Sheet, select Add-ons and Mail merge with Attachments.
2. Select Create Merge Template to build the source for the mail merge. The template should include first name, last name, email address, file attachments, scheduled date and status. You can add more columns if you require.
3. Go back to the Mail merge with Attachments menu and select Import Google Contacts. You can manually add or copy and paste data into the template if you prefer.

If you want to include attachments, you can paste an attachment link from Google Drive into the File Attachments column. You will obviously need to make sure the actual attachment is in Google Drive first. If you need to attach multiple files, separate each URL with a comma.

If you want to schedule the emails, add a date and time to the Scheduled Date column. The format will be mm/dd/yyyy hh:mm.

Set Up Gmail

Now the mail merge template has been populated we can configure Gmail. We need to configure the email to call certain pieces of data from the Google Sheet template.

1. Open a new email in Gmail.
2. Add ‘Dear {{First Name}}’ to the first line of your email. You can change ‘Dear’ for whatever is most suitable for your audience. This is just to demonstrate. You can also use {{Salutation}} and add a Salutation column in your Google Sheet to truly personalize your mails.
3. Fill in the rest of the email body as required.
4. Add attachments as required.

Now you should have an email template that has basic text and any attachments. The mail merge magic happens when you select the command. It will automatically modify any settings between {{ and }} with any corresponding columns in the spreadsheet. You can add as many of these as you need just as long as you copy the column name exactly and place them between double brackets.

You can see in the image that I added an entry in the email called ‘company’. As long as I reflect that exactly in a column header in the spreadsheet, including lower case first letter, mail merge will pull the data into the mail.

Pulling It All Together

Now we have our source template and our draft email it’s time to make the magic happen.

1. Go to your Mail Merge Google Sheet.
2. Select Run Mail Merge from the Mail merge with Attachments menu.
3. Select the Gmail draft you have created and select Run.

If you did not add a Scheduled Date, the emails will be sent immediately. If you did add a Scheduled Date, the emails will be sent according to that entry. Just check your Sent mail box to see which have been sent and double check with the template.

Mail merge works flawlessly but if you would like to test it out first, create a mail merge template with only your details in it and call that in your Gmail mail. You will be able to see how it looks and make sure you have the syntax right. I would suggest doing this for the first time you send a bunch of mails but isn’t always necessary once you get the hang of things