Want to send out multiple emails with personalized text, but with minimum effort? Do a Mail Merge.
Mail merge has been around for quite a while, dating back to as early as 1980. It’s a feature that is offered by most word processing and spreadsheet software.
Unfortunately, unlike the Microsoft Office packages, Google Sheets does not (yet) come with a mail merge option by default. Of course, there’s always a workaround.
In this tutorial, we will show you three ways to use mail merge in Google Sheets:
- Using an app script
- Using an add-on
- Using a browser extension
Table of Contents
What is Mail Merge?
Mail merge is a powerful tool used to create customized letters (or any kind of text document) in bulk. These documents are created so that they can be sent to multiple recipients at the same time.
It forms the basis of many business workflows, especially in the areas of email marketing, advertising, and business correspondence.
They’re quite handy when you want to send a common email template, but with a personal touch.
Mail merge can be applied to not just emails or letters, but can also be used for other purposes, like bulk label making, or invoice writing.
How Does Mail Merge Work?
Mail merge involves taking information stored in a spreadsheet and inserting this information into a template. The spreadsheet contains rows of records pertaining to each recipient. The template consists of placeholders that correspond to individual fields in the spreadsheet.
When the application performs a mail merge, it inserts the data corresponding to each field in the relevant placeholder and repeats the process for each row of the spreadsheet. It thus generates multiple instances of the original template, each one customized for individual recipients.
For example, you might have an email template like this:
In the above email, the placeholder tags have been specified between the double curly braces {{..}}.
When this email template is (mail) merged with data in the following spreadsheet, you get 5 copies of the same email, where values of each row from the spreadsheet are inserted into the placeholders of the template.
Here’s a sample of one of the mail merged emails:
The template will be personalized for each recipient in the same way, with their individual data (obtained from the spreadsheet).
How to Do a Mail Merge in Google Sheets
There are three ways to mail merge a Google spreadsheet with an email:
- Using an app script
- Using an add-on
- Using a browser extension
We will explain each method to you so that you can decide which one works best for you and your requirements.
Using an App Script to do a Google Sheets Mail Merge in Google Sheets
In Google sheets, Mail merge can be performed using an app script. For this method to work, you will need:
- A Gmail account (from which you want to send all your emails)
- A Google Sheets spreadsheet containing the data that you want to personalize for each email recipient.
The good news is that you don’t need to write the script yourself. It is readily available online for you to simply copy and use.
Let’s say you want to send emails congratulating winners of a competition. For demonstration purposes, we are going to use the following sample data corresponding to each recipient:
We want to mail merge this data with the following email template:
Notice that we specified the column header corresponding to the field we want to insert inside the placeholders (specified within double curly braces).
Here are the steps that you can follow if you want to customize the email template and simultaneously send the customized emails to all the addresses specified in column C of the above dataset:
- Get the spreadsheet ready with all your required data. Click here and make a copy of the spreadsheet.
- Customize the spreadsheet’s columns according to your requirements, depending on the data you want to include in your email template. Make sure to update the recipient email addresses in the Recipient column correctly. You can also choose to keep or remove the last column (Email Sent). This is going to get updated by the script after it sends out your emails, so keep this column blank.
- Go to your Gmail account and compose your email template.
- Copy the subject line of the email template.
- Go back to the spreadsheet and navigate to Mail Merge -> Send Emails.
- You will be asked to authorize the script. Click review permissions and authorize it.
- Once authorization is complete, navigate to Mail Merge -> Send Emails again.
- You will now be prompted to enter the subject line of your email. Paste the text that you had copied in step 4.
- This will run your script, which is going to mail merge your data to your email template. After that it will send out the customized emails to all the recipients specified in your spreadsheet. You should see a message informing you that the script is running, as shown below:
- For every email sent, you will see a notification appear in the last column of the dataset telling you the exact date and time when the emails were sent.
See also: How to Automatically Send Emails from Google Sheets Using Apps Script
How to See and Customize the Script
If you’re interested in seeing how the script worked, or if you would like to customize the script’s automation according to your needs, then you can easily do so by navigating to Extensions-> Apps Script. You will find the script included with in-line comments to help you understand the code.
Credits for the code go to Martin Hawksey, Learning Design and Technology Lead at Edinburgh Futures Institute, blogger, and Google Developer Expert. We would like to extend thanks tohimfor developing and sharing the code.
A few points to remember when using this script to perform mail merge with Google Sheets:
- App Script conditions apply to this script. Google has daily quotas and limitations on the number of emails you can send out in a period of time. You can take a look at this page to find out about limitations to sending out bulk emails.
- Make sure there are no differences in spelling between the placeholders in your email template and the corresponding column headers in your spreadsheet.
- The mail merge algorithm used in the script is case-sensitive, so you will have to make sure the capitalization in the placeholders are exactly the same as they are in the column headers of your spreadsheet.
Using an Add-on to Mail Merge from Google Sheets
If you are not comfortable with the app script or find it complicated, there are other ways to perform a mail merge.
Another way to perform a mail merge is by using a mail merge add-on. There are quite a lot of mail merge add-ons available for Google Sheets. Some of them include:
- Rapid Mail Merge
- Mail Merge by Quicklution
- Mail Meteor
- Mail Merge for Gmail
- Dr. Merge: Free Unlimited Mail Merge
And the list goes on. In fact, there’s even an add-on called ‘Yet another Mail Merge’!.
Each one of these add-ons provides a different range of features. So you’ll have to select one based on your requirement, or the type of mail merge that you want to perform.
For example, if your main target is to send unlimited emails with personalized attachments, then you could go for the Dr. Merge add-on. If your target is to send email marketing campaigns, then Mail Meteor could be more suitable.
Among the add-ons mentioned so far, the ‘Yet another Mail Merge’ or YAMM add-on is quite popular. It has become the most installed add-on from the Google Marketplace.
In the end, it all just boils down to the context.
Once you’ve decided what your mail merge requirements are, simply navigate to Extensions -> Add-ons -> Get add-ons.
Then look up “Mail merge”.
You can browse through the add-on options available, along with their features, prices, etc., and add one that you find most suitable.
Each add-on has a different mail merge system. So to find out how to mail merge your documents or emails, look up the instructions that come with the add-on after installing.
Using a Chrome Extension to Mail Merge in Google Sheets
Google spreadsheet mail merge add-ons can be simpler to use and offer more ready-automated features than an app script. However, most add-ons lack advanced email capabilities. A third option is to use a browser extension.
The GMass extension, for example, is quite helpful in sending as well as tracking mail merged emails. The extension is compatible with only the Google Chrome browser.
Once installed, you will find the GMass button added to your composer window, making it easy for you to access the feature every time you compose an email.
To install the extension, visit the Chrome Web store (from a Chrome browser), search for GMass and then press the ‘Add to Chrome’ button.
Other benefits of this extension include:
- Options to send to all rows or a filtered set of rows
- Automated email follow-ups
- Adding links, images, and file attachments to your mail merged emails
- An email sending limit of up to 10,000 emails (which is much higher than what is allowed by Gmail or Google Workspace).
- Detailed delivery reports (including open, click, reply and bounce rates)
- Provision to include suppression lists
Note that this is not the only mail merge extension available for Google Sheets in Google Chrome. Other extensions include Mail Merge for Gmail and Myriab Hub among others. However, the GMass extension is the more versatile option among the options available.
Using Mail Merge for Google Sheets to Create Mailing Labels
As we mentioned before, the applications of mail merge are not just limited to sending letters and emails. It can also be used to create mailing labels, invoices, and more. One Google add-on that specifically caters to using mail merge for label making is the Avery Label Merge add-on. Here’s how to mail merge from Google Sheets with this add-on and create mailing labels.
When you install this add-on, it automatically adds a menu item to Google Docs, allowing you to connect your documents to spreadsheets from Google Sheets.
The add-on can be used to mail merge envelopes, QR codes, and barcodes, besides labels. The mail-merge process is simple:
- Open the Google Sheet that has all the data you need in your mail merge labels.
- Open a Google Document and navigate to Add-ons -> Avery Label Merge ->Start.
- This opens an Avery Label Merge sidebar on the right of your browser window.
- You should also see a set of mail merge instructions on the left side, along with a label template:
- Follow the instructions to start the mail merge.
- You should see a confirmation message informing you that the document has been created.
- Click on the Google Document link to take a look.
- You should see a new Google Document containing all the mail merge address labels printed in your selected pattern.
You can now go ahead and print these labels, or do whatever you need to.
Frequently Asked Questions
Can You Mail Merge in Google Sheets?
Yes, although there are no menu options native to Google Sheets for performing a mail merge, there are apps scripts, add-ons, and Chrome extensions available to mail merge using Google Sheets.
Can You Mail Merge From Google Sheets to Word?
You cannot directly mail merge from Google Sheets to Word, but you can always download your Google Sheets worksheet as a .xls file and then use it to Mail merge in Word.
How Do I Mail Merge Labels From Google Sheets?
There are three ways to do a Mail Merge in Google Sheets – using an Apps Script, using an add-on, and using a Chrome extension. We have discussed all these methods at length in this tutorial.
Can You Make Labels From Google Sheets?
Yes, you can make labels using an add-on like Avery Label Merge. We have discussed how you can do that in this tutorial.
Can You Schedule Mail Sending After a Mail Merge Extension?
Yes, most of the mail merge sheets extensions contain an option for scheduling the emails. Usually, it’s an obvious option before sending.
How Many Email Messages Can You Send Per Day?
Gmail has a limit of 500 emails a day, but Gsuite subscriptions have a limit of around 2000.
How Do I Mail Merge From Google Docs to Google Sheets?
You can’t mail merge between the two, only mail merge Gmail with Google Sheets or Google Docs individually.
Is Mail Merge in Google Sheets Free?
It is not an in-built feature, but there are plenty of free extensions that can do it. Or, you can enter Google Apps Script to do it for free.
Conclusion
In this tutorial, we covered three ways to perform a mail merge in Google Sheets spreadsheets. This included some of our recommended Google Sheets add-ons and Google Chrome extensions to perform mail merge.
We hope you found the tutorial and our recommendations useful.
Related Reading:
