Mail Merge using a Template

How to do a Mail Merge using Google Apps at Wellesley


This Mail Merge Script is from the official Google Solutions website, and was reviewed and tested by LTS in January 2021.  While it does require email permissions, it only uses those permissions to send the emails you are requesting to send.

As with any Mail Merge, we strongly encourageyou to send a test email to a colleague before sending the email out to the full mail merge contact list.

  1. Make sure you are logged into your Wellesley College Google Apps account.
  2. Click on this link to make a copy of the Google Sheets template.  This copy is hosted at Wellesley College, and is one where we have reviewed the code in it to make sure it is ok.  The link on the external website may be updated at any time.
  3. Update the Recipients column with email addresses you would like to use in the mail merge
  4. Create a draft message in your Gmail account using markers such as {{First name}}, which correspond to column names in the Google Sheet, to indicate text you’d like to be replaced with data from the copied spreadsheet.
  5. Copy the subject line text of the email, so you can paste it into the script later in the process.
  6. In the Google Sheet, click on custom menu item Mail Merge > Send Emails.
  7. The first time you do this, a dialog box will appear and tell you that the script requires authorization. Read the authorization notice, allow it to access your Gmail, and continue.
  8. When prompted, paste the subject line used in your draft Gmail message and click OK
  9. The Email Sent column will update with the message status.
If you want to reuse the same Google Sheet for another mailing, you will need to clear the Timestamp cells, as the script will skip any rows that have the timestamp field filled in.