How to send a mail merge with Gmail?
Corentin
Posted on March 3, 2021
There are 2 ways to mail merge in Gmail. You can either use a Google add-on that will do the job for you or build your own mail merge script in Gmail.
We’ll cover both methods in this guide. Even though we recommend using software built for that purpose which cover most issues and will probably save you time.
Method 1: building a merge in Gmail using Google Apps Scripts
As a developer, it's a good challenge to try to build your own Gmail mail merge without an add-on. And thankfully we’re going to use Google Apps Script, which makes it really easy to create Google add-ons.
Google Developer Advocates have already released a great script to help us move forward with the code.
Here is the latest version of the open-source code hosted on GitHub written by Martin Hawksey - @mhawksey. We gonna look at it step-by-step just after.
// Copyright Martin Hawksey 2020
//
// Licensed under the Apache License, Version 2.0 (the "License"); you may not
// use this file except in compliance with the License. You may obtain a copy
// of the License at
//
// https://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
// WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
// License for the specific language governing permissions and limitations under
// the License.
/**
* @OnlyCurrentDoc
*/
/**
* Change these to match the column names you are using for email
* recipient addresses and email sent column.
*/
const RECIPIENT_COL = "Recipient";
const EMAIL_SENT_COL = "Email Sent";
/**
* Creates the menu item "Mail Merge" for user to run scripts on drop-down.
*/
function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu('Mail Merge')
.addItem('Send Emails', 'sendEmails')
.addToUi();
}
/**
* Send emails from sheet data.
* @param {string} subjectLine (optional) for the email draft message
* @param {Sheet} sheet to read data from
*/
function sendEmails(subjectLine, sheet=SpreadsheetApp.getActiveSheet()) {
// option to skip browser prompt if you want to use this code in other projects
if (!subjectLine){
subjectLine = Browser.inputBox("Mail Merge",
"Type or copy/paste the subject line of the Gmail " +
"draft message you would like to mail merge with:",
Browser.Buttons.OK_CANCEL);
if (subjectLine === "cancel" || subjectLine == ""){
// if no subject line finish up
return;
}
}
// get the draft Gmail message to use as a template
const emailTemplate = getGmailTemplateFromDrafts_(subjectLine);
// get the data from the passed sheet
const dataRange = sheet.getDataRange();
// Fetch displayed values for each row in the Range HT Andrew Roberts
// https://mashe.hawksey.info/2020/04/a-bulk-email-mail-merge-with-gmail-and-google-sheets-solution-evolution-using-v8/#comment-187490
// @see https://developers.google.com/apps-script/reference/spreadsheet/range#getdisplayvalues
const data = dataRange.getDisplayValues();
// assuming row 1 contains our column headings
const heads = data.shift();
// get the index of column named 'Email Status' (Assume header names are unique)
// @see http://ramblings.mcpher.com/Home/excelquirks/gooscript/arrayfunctions
const emailSentColIdx = heads.indexOf(EMAIL_SENT_COL);
// convert 2d array into object array
// @see https://stackoverflow.com/a/22917499/1027723
// for pretty version see https://mashe.hawksey.info/?p=17869/#comment-184945
const obj = data.map(r => (heads.reduce((o, k, i) => (o[k] = r[i] || '', o), {})));
// used to record sent emails
const out = [];
// loop through all the rows of data
obj.forEach(function(row, rowIdx){
// only send emails is email_sent cell is blank and not hidden by filter
if (row[EMAIL_SENT_COL] == ''){
try {
const msgObj = fillInTemplateFromObject_(emailTemplate.message, row);
// @see https://developers.google.com/apps-script/reference/gmail/gmail-app#sendEmail(String,String,String,Object)
// if you need to send emails with unicode/emoji characters change GmailApp for MailApp
// Uncomment advanced parameters as needed (see docs for limitations)
GmailApp.sendEmail(row[RECIPIENT_COL], msgObj.subject, msgObj.text, {
htmlBody: msgObj.html,
// bcc: 'a.bbc@email.com',
// cc: 'a.cc@email.com',
// from: 'an.alias@email.com',
// name: 'name of the sender',
// replyTo: 'a.reply@email.com',
// noReply: true, // if the email should be sent from a generic no-reply email address (not available to gmail.com users)
attachments: emailTemplate.attachments,
inlineImages: emailTemplate.inlineImages
});
// modify cell to record email sent date
out.push([new Date()]);
} catch(e) {
// modify cell to record error
out.push([e.message]);
}
} else {
out.push([row[EMAIL_SENT_COL]]);
}
});
// updating the sheet with new data
sheet.getRange(2, emailSentColIdx+1, out.length).setValues(out);
/**
* Get a Gmail draft message by matching the subject line.
* @param {string} subject_line to search for draft message
* @return {object} containing the subject, plain and html message body and attachments
*/
function getGmailTemplateFromDrafts_(subject_line){
try {
// get drafts
const drafts = GmailApp.getDrafts();
// filter the drafts that match subject line
const draft = drafts.filter(subjectFilter_(subject_line))[0];
// get the message object
const msg = draft.getMessage();
// Handling inline images and attachments so they can be included in the merge
// Based on https://stackoverflow.com/a/65813881/1027723
// Get all attachments and inline image attachments
const allInlineImages = draft.getMessage().getAttachments({includeInlineImages: true,includeAttachments:false});
const attachments = draft.getMessage().getAttachments({includeInlineImages: false});
const htmlBody = msg.getBody();
// Create an inline image object with the image name as key
// (can't rely on image index as array based on insert order)
const img_obj = allInlineImages.reduce((obj, i) => (obj[i.getName()] = i, obj) ,{});
//Regexp to search for all img string positions with cid
const imgexp = RegExp('<img.*?src="cid:(.*?)".*?alt="(.*?)"[^\>]+>', 'g');
const matches = [...htmlBody.matchAll(imgexp)];
//Initiate the allInlineImages object
const inlineImagesObj = {};
// built an inlineImagesObj from inline image matches
matches.forEach(match => inlineImagesObj[match[1]] = img_obj[match[2]]);
return {message: {subject: subject_line, text: msg.getPlainBody(), html:htmlBody},
attachments: attachments, inlineImages: inlineImagesObj };
} catch(e) {
throw new Error("Oops - can't find Gmail draft");
}
/**
* Filter draft objects with the matching subject linemessage by matching the subject line.
* @param {string} subject_line to search for draft message
* @return {object} GmailDraft object
*/
function subjectFilter_(subject_line){
return function(element) {
if (element.getMessage().getSubject() === subject_line) {
return element;
}
}
}
}
/**
* Fill template string with data object
* @see https://stackoverflow.com/a/378000/1027723
* @param {string} template string containing {{}} markers which are replaced with data
* @param {object} data object used to replace {{}} markers
* @return {object} message replaced with data
*/
function fillInTemplateFromObject_(template, data) {
// we have two templates one for plain text and the html body
// stringifing the object means we can do a global replace
let template_string = JSON.stringify(template);
// token replacement
template_string = template_string.replace(/{{[^{}]+}}/g, key => {
return escapeData_(data[key.replace(/[{}]+/g, "")] || "");
});
return JSON.parse(template_string);
}
/**
* Escape cell data to make JSON safe
* @see https://stackoverflow.com/a/9204218/1027723
* @param {string} str to escape JSON special characters from
* @return {string} escaped string
*/
function escapeData_(str) {
return str
.replace(/[\\]/g, '\\\\')
.replace(/[\"]/g, '\\\"')
.replace(/[\/]/g, '\\/')
.replace(/[\b]/g, '\\b')
.replace(/[\f]/g, '\\f')
.replace(/[\n]/g, '\\n')
.replace(/[\r]/g, '\\r')
.replace(/[\t]/g, '\\t');
};
}
Follow the guidelines below to understand how to mail merge in Gmail using Apps Script:
1. Create a copy of the sample mail merge spreadsheet
Open this demonstration spreadsheet and click on “Make a copy” to get your own copy.
2. In your new spreadsheet
Click on Tools > Script editor to open Google Apps Script. From there, you will see that a script is already tied to your spreadsheet. That's because you've made a copy of the previous spreadsheet!
In the script editor, you can update the code as you wish. Changes will be reflected immediately.
Back to copied spreadsheet, update the “Recipients” column with email addresses you would like to use in the mail merge. Replace the cells’ value under the “Recipients” column with your own email address for example.
3. Now, open Gmail to create a new draft email
You can use personalized variables, like {{ firstname }} which correspond to column names of the spreadsheet you just copied. This indicates text you’d like to be replaced with data from the copied spreadsheet.
4. Back in the copied spreadsheet, click on the custom menu item called “Mail Merge” and then click on “Send Emails”
This item menu was created by the Apps Script project and will start the mail merge process.
5. A dialog box appears for authorization. Read the authorization notice and continue
Important note: The script we are using has been created and proofread by Google Apps Script teams. Always be super careful when authorizing scripts and third-party apps in general.
When prompted enter or copy-paste the subject line used in your draft Gmail message. Then click OK.
6. Sending your emails
You will see that the “Email Sent” column will update with the message status. Back in Gmail, check your Sent folder and review the emails the program just sent for you!
Keep in mind that using a script is at your own risk. Check Gmail’s sending limit before sending large volumes of emails. Especailly, be aware that your account can get blocked by Gmail if your emailing activity seems unusual in the eyes of anti-spam filters.
For these reasons, we would recommend using a mail merge solution such as Mailmeteor. Mailmeteor deals with all these aspects for you and ensures that your privacy remains protected.
Method 2: using a mail merge add-on like Mailmeteor
We’ll start with a real-life example to show you how to do a mail merge from Gmail using a Google Sheets add-on. In this example, we’re using Mailmeteor, the best rated Google mail merge add-on.
1. Get Mailmeteor
All you have to do is to install Mailmeteor from the Google Workspace Marketplace. The Worskpace Marketplace is a place where you can find all the apps compatible with your Google Suite. Mailmeteor is a tool that integrates with Gmail and Google Sheets to merge emails with Gmail.
2. Add contact in Google Sheets
Once Mailmeteor is installed, open a Google Sheets spreadsheet.
First of all, you will need to add recipients to a Google Sheets spreadsheet. This spreadsheet will be the place where you store your contact list. You will also be able to track your campaign metrics from there.
To create a mailing list you can either add your recipients manually or import contacts. To import contacts in Google Sheets, go to Menu > File > Import and select your Excel or .csv file.
Here’s a mail merge demo spreadsheet we’re going to use:
Note: when opening Mailmeteor for the first time, you will be guided through a quick onboarding tutorial. A demo spreadsheet like this one will be created for you.
Let’s breakdown how your spreadsheet should look like:
➤ Add column headers on the 1st row
Mailmeteor will pull the information from your spreadsheet to personalize your emails. Each column represents a personalized field. This field will be replaced in your email template.
In our example, we have 4 columns named: firstname, email, company, postscriptum.
Add as many columns as you want and pick any column header name you want. Make sure you have a column named “email”.
➤ Fill the columns with your recipients’ information
Fill your spreadsheet with your recipients’ info. Ensure all email cells are filled with valid email addresses. Apart from the emails, you can leave some cells blank, that is fine! In the example below, some recipients will get a Post Scriptum whereas others won't.
3. Open Mailmeteor from the Add-ons menu in Google Sheets
Once your contact list is ready, open Mailmeteor. To open Mailmeteor go to the menu and select Add-ons> Mailmeteor > Open Mailmeteor.
This is the Mailmeteor interface. It tells you how many emails you can send per day and details related to your campaign. Next, we are going to compose the template that will be used for the mail merge.
4. Compose a new email template
Click on the “Create new template” button. This will open an editor in which you can compose your email. The Mailmeteor editor is the exact same as Gmail, you will find all the actions you need to customize your email.
Now we’re going to personalize your email. Personalizing emails is important as it helps make your recipients feel unique when they receive your emails. Using personalization will also dramatically improve your opening rates - and thus the replies you will get.
5. Personalize your emails
A mail merge transforms a standard email template into a personalized email copy. It’s done by replacing variables fields within the template with the content from your spreadsheet.
To insert a variable it’s easy: add variables using double brackets like this {{ firstname }}
Here is a template you can copy-paste:
When adding a variable, always make sure that it matches a header in your spreadsheet.
Once you are satisfied with your template, click the “Save” button.
6. Preview emails before sending
Mailmeteor offers a preview feature that is super helpful to review emails before sending. The preview mode gives you a glimpse of the actual output of your email once personalized for each recipient.
You can also send a test email to yourself. Testing your emails on several devices is a best practice. This will ensure your emails will display correctly in most situations.
7. Send your mail merge
Ready for take-off? It’s time to send your mailmerge campaign.
We know that sending your mail merge can be a bit daunting at first. No worries though, if you follow these steps, everything will be alright!
✨ That's it! You are now ready to mail merge emails with Gmail using an add-on such as Mailmeteor ✨
Here's a real-life example. Watch this teacher use Mailmeteor to mail merge emails to his students:
Now it’s your turn
That’s it for this guide to mail merge in Gmail. We hope you now better understand this simple yet incredibly powerful tool called mail merge.
This guide is part of an extended guide on Mail merge in Gmail (2021). If you want to learn much more, go check it out!
Posted on March 3, 2021
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.
Related