How to Create Mail Merge with Gmail and Google Sheets

Mail Merge for Gmail lets you send personalized email messages to multiple contacts in one go. The individual messages have almost the same content but some parts of the message can be customized. For instance, you can greet each recipient by their first name, you can include their postal address in the message body, personalize the subject line, attach different files while the other parts of the email body remain the same.

Mail Merge in Gmail is for everyone. If you are organizing a party at your home, you can send personalized email invitations to all your friends with the help of mail merge. A business owner can use mail merge to inform customers about upcoming deals and offers. Sales and marketing teams use Mail Merge for drip campaigns. Teachers can send individual assignments and reports to students through mail merge.

Mail Merge in Gmail

Here’s how you can do mail merge with Gmail:

  1. Install the Mail Merge for Gmail add-on. Please watch the Mail merge tutorial for instructions.
  2. This will create a new Google Spreadsheet. Go to the Add-ons menu in the sheet, select Mail Merge with Attachments and then select the Create Merge Template menu.
  3. Open Google Contacts and create a new Group with all the contacts who you would like to send a personalized email. Alternatively, you can enter the names and email addresses of recipients directly in the Mail Merge Google Sheet.
  4. Under the Mail Merge menu, choose Import Google Contacts and select the Google Contacts group that you created in Step 3. It will now automatically import all the associated Gmail contacts into the spreadsheet.
  5. Go to Gmail and create a new draft email messages that will become the template for Mail merge. Alternatively, you can compose an email template in rich text using the HTML Mail tool and then copy-paste the generated HTML code into the message body field of the Google Sheet.
  6. If you would like to add file attachment to your email messages – say a PDF file or a Word document – just upload the file to Google Drive and then select Insert File Attachments to insert that link of the file into the Mail merge sheet. Mail Merge will automatically insert the actual file into the email.
  7. Go to the Mail Merge menu again and choose “Configure Mail Merge” – the status column will change to “MAIL SENT” for all email messages that were successfully delivered.

It is always a good idea to test your email campaign before sending a blast to multiple people. On the Configure Mail Merge screen, click the “Send a Test Email” button and it will send a sample email to the developer with the {{Variable Fields}} substituted with dummy values. If all the fields have been replaced in the test email, your mail merge is ready to go.

Gmail Mail Merge FAQ

Here are some answers to frequently asked questions around Mail Merge for Gmail and Google Apps:

1. How many email messages can I send per day? The free edition of Mail merge lets you send 25 emails per day but the sending limit for Premium merge is different. Gmail users can send emails to 400 recipients per day while the daily limit for GSuite (Google Workspace) accounts is 1500 recipients per day.

2. I am a Google Apps user, yet my daily limit for premium edition is only 400 per day? Google will increase your limit to 1500 if your GSuite business account is more than a few months old or if your Google Apps domain has more than 5 users after the 1st billing cycle.

3. How do I upgrade to Premium? What is the cost of a license? Please click here to buy the premium license for Mail Merge. A single-user license is \$3.25 per month per user, billed annually.

4. I’ve sent only 10 emails yet my daily email quota was reduced by 20. Why? It is likely that you have added an email address in the CC or BCC field. Remember that the daily email limit is in terms of number of recipients so if you send an email that is also cc’ed to someone, it will decrease your quota by 2.

5. What is your privacy policy? Why does Mail Merge require access to my Gmail, Google Contacts, etc. Mail Merge needs access to Gmail because it sends emails from your Gmail account. It needs access to Google Drive to fetch file attachments while it requires access to Google Contacts to import them into the Google Sheet. Read permissions requirement in detail.

6. How do I track email opens with Mail Merge? Email Tracking is a premium feature. During configuration of Mail Merge, select “Yes” for “Track Email Opens” option and the outgoing emails will include a 1x1 tracking GIF. To view the email read reports, go to the Mail Merge menu and choose Show Email Open Reports. You can see the date and time when an email was opened, the IP address and the User Agent of the client. In most cases, this will be equal to a Google IP address because Google now caches images on its own servers.

7. How do I include different CC or BCC addresses for each recipient? Add two extra columns in the Mail merge template and set the columns names as CC and BCC respectively. Now put the email addresses in these columns per row and the merged message will cc’ed and bcc’ed to respective addresses. You can put multiple emails separated by commas.

8. I sent a merge but the variable {{First Name}} didn’t substitute correctly in the outgoing email. Why? It is likely that you’ve applied multiple formatting styles to the variable inside the Gmail draft. An easy solution would be that you open your Gmail draft, delete the variable and type it again (see help). This would clear any inconsistent formatting. Now switch to Mail Merge template and configure using the recently modified Gmail draft template.

9. Why is the option to create Gmail drafts disabled in my Mail merge? The Drafts option is only available when you are performing Mail Merge in plain text or HTML. It is not available if you are using a readymade Gmail draft as a Merge template.

10. How do I insert unique images in all my outgoing email messages? You can create a new column in Mail merge sheet called Images. Now upload the image to an image hosting site, get the image URL and add it to the sheet as shown below:

<img src="image_url"/>

In the Gmail draft, add {{Images}} anywhere and it will be replaced with the actual image.

11. How do I insert unique Hyperlinks in my email messages? Create a new column in the Mail Merge template called Links (you can give any name). Now add the links in the Google Sheet as shown below:

<a href="http://ctrlq.org">Visit our website</a>

In the Gmail draft, add {{Links}} and they’ll be substituted with the actual hyperlink. You can also use mailto for link to email addresses.

12. I am scheduling an email but it is sending it right away. Why? The scheduled emails are sent +/- 30 minutes of the scheduled time. So if you have scheduled an email to go out at 2:15 PM, it can go anytime between 2-2:30 PM. The emails are scheduled in the timezone of the Google Spreadsheet.

13. Do I have to keep my computer open for the scheduled emails to go at specific time? No, the Google Add-on runs in the background on Google Servers and you can close the Google Sheet after configuration.

14. I have run out of my daily email quota? How do I send emails automatically when the quota is reset by Google? You can add a schedule date that is 3-4 from now and run mail merge. The messages will be scheduled and they will be automatically delivered when your quota is reset.

15. I am running Mail Merge but it is simply not sending the emails. Why? You’ve either run out of email quota or because the Mail Merge Status column in the spreadsheet is set to “MAIL SENT”. This check is in place to prevent you from accidentally sending multiple emails to the same address should you run merge multiple times.

16. I need to send an email blast to 10,000 email contacts. Can Mail Merge handle this? It is recommended that you uses a dedicated email sending service like MailChimp for sending bulk emails. You can use the Gmail Address Extractor to pull all emails from your Gmail mailbox.

17. I use Salesforce as my CRM. Is there a way to log all the emails into Salesforce.com Salesforce provides you with a unique email address. When you configure mail merge, put that email address in the BCC field of the Configure screen and Run Mail merge. All emails will be marked to Salesforce now for tracking.

18. Can I cancel Mail Merge from running? How do I recall my emails? Mail Merge uses Gmail and therefore, unlike Microsoft Outlook, there’s no option to recall sent emails. You can, however, cancel a Mail Merge process by hitting the STOP button. If you would like to remove the scheduled emails from the queue, go to the Mail Merge menu and choose Cancel Schedule Emails. Alternatively, you can remove the corresponding rows from the Mail merge sheet.

Mail Merge Support

19. How do I report issues with Mail Merge? Open the Mail Merge Google Sheet, go to Add-ons > Mail Merge with Attachments > Instructions and Support > Send Debug Logs. It will send a copy of the sheet and the mail merge configuration to the Google Script developer.

Google Scripts are powerful. Some of our other popular Google Scripts that will improve your productivity include Email Scheduler, Email Extractor, Save Gmail to Google Drive and Gmail autoresponder.

Amit Agarwal

Amit Agarwal

Google Developer Expert, Google Cloud Champion

Amit Agarwal is a Google Developer Expert in Google Workspace and Google Apps Script. He holds an engineering degree in Computer Science (I.I.T.) and is the first professional blogger in India.

Amit has developed several popular Google add-ons including Mail Merge for Gmail and Document Studio. Read more on Lifehacker and YourStory

0

Awards & Titles

Digital Inspiration has won several awards since it's launch in 2004.

Google Developer Expert

Google Developer Expert

Google awarded us the Google Developer Expert award recogizing our work in Google Workspace.

ProductHunt Golden Kitty

ProductHunt Golden Kitty

Our Gmail tool won the Lifehack of the Year award at ProductHunt Golden Kitty Awards in 2017.

Microsoft MVP Alumni

Microsoft MVP Alumni

Microsoft awarded us the Most Valuable Professional (MVP) title for 5 years in a row.

Google Cloud Champion

Google Cloud Champion

Google awarded us the Champion Innovator title recognizing our technical skill and expertise.

Email Newsletter

Sign up for our email newsletter to stay up to date.

We will never send any spam emails. Promise.