5

How do I do a mail merge with Gmail?

3

Labnol has a video tutorial and walkthrough.

The key is that:

you ... setup Mail Merge in Gmail through your Google Docs account

Update

There is an update to the Labnol article that shows how to add attachments - Labnol tutorial

(thanks to mvark)

5
  • This is a great solution - however would like to add attachment/s?
    – CWCarlos
    Sep 17 '10 at 13:15
  • @CWCarlos - Don't know the answer to that I'm afraid.
    – ChrisF
    Sep 17 '10 at 13:53
  • @CWCarlos you can simply put your attachment to the Public folder in Dropbox and adding the link to it as a text in the body. Zip them if there are more than one file for easier download! (www.dropbox.com)
    – Lipis
    Oct 17 '10 at 14:24
  • There is an update to the Labnol article that shows how to add attachments - labnol.org/internet/mail-merge-with-attachments/18561
    – mvark
    Jan 26 '11 at 16:32
  • @mvark - I've updated the answer with your link - thanks.
    – ChrisF
    Jan 26 '11 at 16:36
1

Here's an excellent video tutorial on using gmail, google sheets and the free Yet Another Mail Merge add-in to do a mail merge:

Create a Mail Merge with Gmail and Google Drive / Docs

You write a draft email in gmail, use this format to refer to column names in your google sheet when writing the email: $%contact email address% where 'contact email address' is the column name, then click the 'mail merge' button, choose the gmail draft you want to send, test it on yourself if you wish, then send it to everyone in your spreadsheet. Very straightforward.

0

I found EmailBee provides an easy way of doing mail merges using your Gmail account:

  1. Click https://www.emailbee.com/Account/Register

  2. Click "Sign in with Google" and go through the account creation process (basically a couple of clicks)

  3. Once you are in, click "mailbox" on the left hand menu and then the "add mail" icon on the top right of the screen and away you go

NOTE: If you want to send to your Gmail contacts you need to import them:

  1. Click contacts on left hand side menu and then add contact on top right

  2. Select the "import" tab, click "google", click next and then click "start import"

If you use Google Chrome they also have a Chrome Extension which runs inside your Gmail client on Chrome.

0

One of the easiest ways to do a mail merge in Gmail is to use a Gmail add-on that's can perform mail merges.

The following add-ons are all fairly straightforward to use, and all of them have a free plan:

0

There are basically two 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 with Google Apps Script.

We’ll cover both methods in this guide. Even though, if your are just looking for a sending a quick bunch of emails, I will recommend you to use a software built for that purpose which cover most issues and will probably save you lot of time.

Method 1: building a merge in Gmail using Google Apps Scripts

The coding way of sending a mail merge in Gmail requires advanced skills if you want to use the Gmail API. But thankfully, you don't need to. We’re going to use Google Apps Script, which makes it really easy to create Google scripts for Google Sheets (and other Google applications).

On top of that, sevaral 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. Send 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.

Install Mailmeteor for Google Sheets

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:

Demo spreadsheet to mail merge in Gmail Link to mail merge demo spreadsheet

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.

Fill spreadsheet cells to mail merge in Mailmeteor

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.

Create a new template in Mailmeteor

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:



Hello {{ firstname }},

I'm glad we could meet lately. As discussed, we are using a brand new mail merge solution called Mailmeteor.

Mailmeteor lets you send hundreds of personalized emails, right from Gmail.

I’d love to let you see some of the features to get your feedback. We want to ensure Mailmeteor would actually help you do your job better at {{ company }}.

Would you have 10-15 minutes free to chat? (I promise to keep the time!). Let me know the time that suits you best.

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.

Preview mail merge before sending

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!

Sending a mail merge with Mailmeteor

✨ That's it! You are now ready to mail merge emails with Gmail using an add-on such as Mailmeteor ✨

Real-life example:

Here's a real-life example. Watch this teacher use Mailmeteor to mail merge emails to his students:

Video thumbnail
Mail Merge Tutorial using Mail Meteor Chrome Add-On by Steve Clarck

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.