How to Send Bulk Emails using Gmail (Mail Merge)
Sending personalized emails to multiple recipients can be a cumbersome task if you have to do it manually. There are several online tools available for sending bulk emails but most of them are paid. So, if you are looking for a free alternative, you can also use Gmail to send bulk emails.
Yes, using the combination of Google Spreadsheets and Gmail, you can send bulk personalized emails. It means you can include personalized information for each recipient in the email like Designation, Company, Country, etc. All credit goes to Martin Hawksey, who created the Mail Merge script.
SEE ALSO: How to Change or Create your Signature in Gmail?
Use Gmail to Send Bulk Emails (Mail Merge Script)
The Mail Merge script takes input from the Google Spreadsheet and sends the Gmail draft to the recipients from the Spreadsheet. So, in this tutorial, we will describe the process from the beginning. First, we will create a sample spreadsheet on Google Docs and then, configure the Mail Merge script to send bulk emails. So, let’s start:
Step 1: Create a Google Spreadsheet
First of all, we will create a Google Spreadsheet that will contain the recipient’s details. You can include as many details as you wish. However, you will have to make changes to the Mail Merge script accordingly. So, for simplicity, we have created a spreadsheet with basic details to send bulk emails.
Please note that the headers of the spreadsheet are the variables that we will use in the Gmail draft and the script, so, be careful about them. Especially the “Mail Sent Status” column must be present to make the script work.
You can also make your own custom column headers, just make sure to use the exact same name in the Gmail draft.
Step 2: Add the App Script to the Spreadsheet to send the Bulk Emails
Now, after you are ready with the recipient details in the spreadsheet, click on the Extensions menu and select the Apps Script option.
Now, you will see a new script page with the default code. Simply select and delete the existing code from the script page. We will paste here our custom script (with some changes to Martin Hawksey’s script).
SEE ALSO: How to Download Email Backup from Gmail?
Step 3: Paste and Save the Mail Merge Script
Now, copy and paste the following customized Mail Merge script into your Code.gs file. This script will allow us to send bulk emails using Gmail.
// To learn how to use this script, refer to the documentation:
// https://developers.google.com/apps-script/samples/automations/mail-merge
/*
Copyright 2022 Martin Hawksey
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.
*/
// Modified by Aslam Khan (https://windospc.com/contact-us/).
// This script now also includes recipients from CC and BCC columns of the spreadsheet.
/**
* @OnlyCurrentDoc
*/
/**
* Change these to match the column names you are using for email
* recipient addresses and email sent column.
*/
const RECIPIENT_COL = "Email"; // Change this as per the recipient column header in your google spreadsheet.
const CC = "CC"; // Change this to the name of the column header for recipients in CC.
const BCC = "BCC"; // Change this to the name of the column header for recipients in BCC.
const EMAIL_SENT_COL = "Mail Sent Status"; // Change this to the name of the column header to update the status of mail sent status.
/**
* 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 bulk emails using Gmail as per the 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, finishes up
return;
}
}
// Gets the draft Gmail message to use as a template
const emailTemplate = getGmailTemplateFromDrafts_(subjectLine);
// Gets the data from the passed sheet
const dataRange = sheet.getDataRange();
// Fetches 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();
// Assumes row 1 contains our column headings
const heads = data.shift();
// Gets the index of the column named 'Email Status' (Assumes header names are unique)
// @see http://ramblings.mcpher.com/Home/excelquirks/gooscript/arrayfunctions
const emailSentColIdx = heads.indexOf(EMAIL_SENT_COL);
// Converts 2d array into an object array
// See https://stackoverflow.com/a/22917499/1027723
// For a 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), {})));
// Creates an array to record sent emails
const out = [];
// Loops through all the rows of data
obj.forEach(function(row, rowIdx){
// Only sends emails if email_sent cell is blank and not hidden by a 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: row[BCC],
cc: row[CC],
// from: '[email protected]',
// name: 'name of the sender',
// replyTo: '[email protected]',
// 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
});
// Edits 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]]);
}
});
// Updates 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();
// Handles inline images and attachments so they can be included in the merge
// Based on https://stackoverflow.com/a/65813881/1027723
// Gets 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();
// Creates 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 searches for all img string positions with cid
const imgexp = RegExp('<img.*?src="cid:(.*?)".*?alt="(.*?)"[^\>]+>', 'g');
const matches = [...htmlBody.matchAll(imgexp)];
//Initiates 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');
};
}
After pasting the script, click on the Save button next to Run to save the script.
NOTE: Please note that this script has been customized as per our spreadsheet’s columns. Do not change the header of the “Email“, “CC“, “BCC” and “Email Send Status” columns.
Step 4: Create an Email Draft on Gmail
Now, that the script is ready, it’s time to create our sample email draft on Gmail. So, open your Gmail, click on the Compose button at the top left, and create your message draft. Make sure to write the subject line as it will be used to send bulk emails.
You can use the spreadsheet’s column headers to personalize your email draft. For example, you can use {{FirstName}}, {{LastName}}, {{Designation}}, {{Address}}, etc. in the email draft. Make sure to write the column header name inside double curly brackets to make it work.
Step 5: Authorize the Mail Merge Script
We are almost ready to send bulk emails using Gmail. But first, we need to authorize the Mail Merge script on your Google Account.
Now, you should see a new Mail Merge option in the menu bar. In case you don’t see this option, simply refresh the Google spreadsheet page.
Click on the Mail Merge menu and select the Send Emails option to run the script.
When you select this option for the first time, it will ask you to authorize the script on your Google account. So, click on the Continue button to authorize the script.
Next, select your Google account. You may get a prompt about the unverified app. Here, click the Advanced link to see more options, and then click Go to Mail Merge (unsafe) to continue.
Finally, click on the Allow button to authorize the script to send bulk emails.
PRIVACY NOTICE: Your data in the spreadsheet will be processed by Google servers. So, we warned about data privacy. You may also check here how much Google knows about you.
Step 6: Run Mail Merge to Send Bulk Emails using Gmail
After successful authorization, it’s time to run the Mail Merge script to send bulk emails. So, click the Mail Merge menu and select Send Emails.
You will see a prompt asking for the subject line of the Gmail draft. So, copy the subject line of the Gmail draft and paste it into the input field. After that, click OK to send the bulk emails.
After the script has run successfully, it will update the “Email Sent Status” column with the date/time of sending the email.
Step 7: Verify the Sent Email
Now, go to the recipient mailbox to verify the email delivery. If you see the email with the correct details, then it worked successfully.
Frequently Asked Questions about Mail Merge
You may face some issues while running the script. Here are some common FAQs that you might want to check:
I am getting the “The starting column of the range is too small” error while running the script.
Make sure that the “Email”, “CC”, “BCC” and “Email Sent Status” headers have exact same spelling in your spreadsheet. If you wish to change these headers, make sure to update the following variables in the script accordingly.
Can I add more columns to the spreadsheet with other variables?
Yes, you can add as many columns as you want. Just make sure to include those variables in your Gmail draft by enclosing them inside the double curly brackets {{ColumnHeader}}.
SEE ALSO: How to Enable Dark Mode in Gmail on Android and iPhone?
Send Bulk Emails using Gmail with Ease via Mail Merge Script
The above tutorial shows you how to send bulk emails using your Gmail account. We have provided the customized script here. However, if you wish to check more details of the original script, see this blog post.
Hope you liked this tutorial. In case you have any queries or suggestions, feel free to write them down in the comments below. You may also subscribe to our newsletter to get the latest tutorials by email.
