Use Google Sheets together with your Gmail account to generate and distribute customized bulk messages quickly and easily. The method explained in this tutorial is ideal for sending personalized seasonal greetings to friends or employees, for making labels, or for issuing transaction documents to lists of clients/customers. The obvious advantage of this, is that your computer does the repetitive work, saving you time and frustration, and it's completely free for personal or small business use.
How To Mail Merge in Google Sheets
Mail Merge takes a draft email message, selects information from a spreadsheet of data, (each row containing details pertaining to an individual client/customer), and combines these inputs into individual emails. The spreadsheet template we will use in this tutorial contains a script that directs Google Sheets to execute this process, and dispatch the output automatically using Gmail.
1. Open Google's sample mail merge spreadsheet - Click this link
2. Click on file -> "Make a Copy" and give your spreadsheet a name
3. Choose the Column Headers you want to include in your email
Each column header is a field you will include in your email template. For example the email template we are going to send out will say “Hi {{First Name}} {{Last Name}}," and will auto fill all of the values under that column heading.
You can modify and add column headers but must always have "Recipient" and "Email Sent" headers as these are required by the script.
In our spreadsheet we are going to include the column headers: "First Name", "Last Name", "Recipient", “Status Level”, "Discount %", “Start Date” and “Email Sent”.
4. Create an Email in Gmail and save it as a draft
This spreadsheet will be sending all of your emails from your gmail account. Log into the gmail account you plan to send the email from and create a new email.
We will use this template:
It's important to note that the email subject is how the script will know to use the draft you created.
5. Connect your mail merge spreadsheet to your Gmail:
Click on the "mail merge" button at the top of your spreadsheet and choose "Send Emails".
Click continue on the authorization notification:
Copy and paste the Subject Line from your draft email then click OK
Click on the Gmail account you want to use:
You will get an error saying "This App isn't verified", however this is a spreadsheet provided by google and is confirmed to be safe. Click "Advanced" and click "Go to Gmail/Sheets Mail Merge unsafe”.
Click "Allow" when you get the notification asking if you want to connect your Gmail to your Google Sheets spreadsheet.
6. Click “Mail merge” and “Send emails”.
All of the recipients in your spreadsheet will now be emailed a custom email based on your template.
You can confirm the emails have been sent as there will now be a date in the "Email Sent" Column beside each recipient in your mail merge spreadsheet.
It's important to note that Gmail has limitations on the number of emails you can send per day. If you need to send large amounts of emails each day a paid solution may be a better choice
Advanced Capabilities of the Mail Merge Spreadsheet:
You can unlock advanced features including BCC and CC fields, a custom Sent From email address, Name of Sender, Reply To address and No Reply fields.
1. Click on Extensions Apps Script
2. Scroll down to the line which reads: “ // Uncomment advanced parameters as needed (see docs for limitations)”
Several features included are deactivated by “//”, which designates them as comments.
3. To activate any one of these features remove the forward slashes and substitute a valid email address.
Recommended articles
More resources
