Mail Merge in Google Mail with a Google Sheet

Audience: Clubs, Faculty, Researchers, Staff, Students and Teaching Assistants

This KB Article References: Google Mail
This Information is Intended for: Clubs, Faculty, Researchers, Staff, Students, Teaching Assistants
Last Updated: September 26, 2018
This option is no longer working.  Try the new option using Qualtrics

 

Mail merge (sending emails personalized for each recipient) is possible using Google Mail, Google Sheets and a script/program called Yet Another Mail Merge. Currently, scripts (add-ons) are not enabled for Stony Brook Google accounts, so to use these directions, use this template, which already has the script.

Mail Merge is a powerful tool, but please review these policies/limits when using it:

  • Emails should be personally relevant to recipients
  • Get approval (VP, CIO) before sending 101+ messages (Instructor/Academic Area emails to students are OK)
  • Review SBU's Email Policy
  • In a 24-hr period, you can send up to 2000 messages 

Set up, Test, and Run Mail Merge

  1. Use this template (from the link, select Use this template) and then select File > Rename and give the copy a good name
  2. Make sure column headers (row 1) are lowercase with no spaces
  3. Enter/paste in test, not real, data (data that only goes to your colleagues or another email address you have access to)
  4. In Google Mail, create a draft email. Give the email a clear Subject and anywhere you want data from your spreadsheet, use the column headers with << >> surrounding them like this: << firstname>>. Type the column header into the email (do NOT use copy paste as that can cause issues).
    When you finish the draft, click the X in the top right to close and save it and it will be saved in your Drafts

  5. Return to the Google sheet with your data
  6. Click Mail Merge > Standard Merge (if the Mail Merge menu doesn't appear, refresh the page  a few times)
      
    The first time you use this sheet to do a Merge, there are a few extra steps:
    1. Authorize by clicking Continue
      Authorization required with continue button and cancel button
    2. Scroll down the Request for Permission window and click Accept
         
  7. If you see the "Which column contains the recipients?" option, select the column that contains the recipients' email addresses and then wait for the screen to refresh; do not click Close (if you don't see this screen that's ok. It automatically found your email address column)
  8. From the list, select the draft you created in step 4 above (the drafts' subjects are listed). Once selected, the draft/template will appear below the list in green as the Selected template
    standard merge with draft selected and appearing in green below list of drafts
    *If you want to send the message from an email address other than your first.last@stonybrook.edu address that you have access to, follow these instructions to add that address as as Send From Address (you need to set additional send from email address up before running mail merge or they will not appear in the MailMerge screen)
  9. Then click Send Mails. It will process and let you know it is done (it may take awhile if you have many rows of data). Your spreadsheet will get an additional column called Merge Status (and if you had to select the column for recipients' email addresses in step 7, that will be renamed Email Address)
  10. Now, remove all the test data beginning with row 2 (leaving the column names in row 1) and enter your real data.
  11. Repeat steps 6-9 to run the merge with your real data.

Send a Reminder or Second Mail Merge Email

  1. Revise the draft email (e.g., to let the recipients know this is a reminder)
  2. In the spreadsheet, remove "Done" from each row in the Merge Status column
       
  3. Repeat the above steps 6-9

Notes on Mail Merge

  • This is a script created by the third party. It is not supported by Google nor by the University. Use at your own risk.
  • Images pasted in the email template can cause a copyBlob error.  Remove them if you get an error. 
  • When testing with real data, typing the word 'done' into the Merge Status field will not prevent mail from being sent. Just don't test with real data.
  • If only some or no data merges, change your email draft to plan text (click on the drop down in the bottom right corner of the draft and select "plain text mode") and make the header row of your spreadsheet plain text format (select the header row, click Format > Number > Plain Text). If the data still doesn't merge, remove the merge codes (e.g., <<firstname>>) and type (do not paste) them in again. Then retry the mail merge.

CCing an additional recipient

  • To CC a recipient for each email, add the email address in the CC field in the draft email.
  • To include multiple recipients on one email, add them to the Email Address column on your spreadsheet, separating email address with a comma:

Additional Information


Getting Help


The Division of Information Technology provides support on all of our services. If you require assistance please submit a support ticket through the IT Service Management system.

Submit A Ticket

Hosted By


DoIT Training and Development