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 mails 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 combinations 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.

Create a new Spreadsheet on Google Docs and fill in the recipient details to send bulk emails using gmail
Create a new Spreadsheet on Google Docs and fill in the recipient details

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.

Click Extensions and select the Apps Script option
Click Extensions 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).

3 Remove Existing Code From Code Gs Page
Remove existing code from Code.gs script page

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.

4 Paste The Mail Merge Script Code And Save The Code Gs File
Save the Mail Merge 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 and 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. Mail sure to write the column header name inside double curly brackets to make it work.

6 Create A New Draft In Your Gmail Using The Spreadsheet Headers As Variables
Create a new Gmail draft using the spreadsheet headers as variables

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.

Click Mail Merge and select Send Emails to send bulk emails using gmail
Click Mail Merge and select Send Emails

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.

9 Click Continue To Authorise The Script To Send Mails
Click Continue 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.

12 Click Go To Mail Merge To Continue
Click “Go to Mail Merge” to continue

Finally, click on Allow button to authorize the script to send bulk emails.

Finally Click Allow to Provide the Mail Merge With Necessary Permissions to send bulk emails using gmail
Click Allow to authorize the Mail Merge script

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.

Click Mail Merge and select Send Emails to send bulk emails using gmail
Click Mail Merge 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.

Type or paste the subject line of the email draft and click OK
Type or paste the subject line of the email draft and click OK

After the script has run successfully, it will update the “Email Sent Status” column with the date/time of sending the email.

Mail Sent Successfully - Sent Date Updated
Mail Sent Successfully – Sent Date Updated

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.

Email Received Successfully
Email Received 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 “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.

18 Define Variable To Include Cc And Bcc Fields In The Mail Merge Script
Update variables to include Cc and Bcc fields

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.

Editorial Staff

Hi there, we are the editorial staff at WINDOSPC (former HELLPC). We are a team of funny and technical people. Feel free to get in touch with us via Contact-Us page.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back to top button