Mail Merge in Gmail

Information

Mail merge will allow you to automatically import data from a Google Sheet into an email or series of emails. This is possible when following the steps below. Keep in mind that this solution contains case sensitive characters, and will need to be in the proper format to work as intended. Also please note that Google has a 1,500 email limit per 24 hours. The sheet will track who has already received an email, which allows you to run the mail merge tool again after the 24 hour period, sending emails to those who have not yet received the communication. (This must be done manually each day in increments of 1,500 until complete)

Environment

• Google Sheets
• Gmail
• Excel

Process

Setting up your Google Sheet

1. Click the link below to access the template mail merge sheet. When asked, select “make a copy”.- https://docs.google.com/spreadsheets/d/1jslfwWsQc7eDaUhNUWKwnawmZnOX2ewNFNhcdlAvYlw/copy
2. Add your data to the sheet, being sure to coordinate each data entry with the correct column. You can move and rename any of the columns except for “Recipient” and “Email Sent”. You can also add new columns if needed.
3. We recommend creating a new copy of this sheet each time you need to complete a mail merge.

Building an Email Template

1. Create a draft message in Gmail using the syntax {{column name}} for each data entry, this will pull the corresponding data from the column names in your mail merge sheet. 
2. Enter {{Recipient}} in the “To:” field when drafting your email to send emails to each recipient during one mail merge run.
3. From the copied spreadsheet, select Mail Merge > Send Emails along the top toolbar.
4. A dialog box will appear and tell you that the script requires authorization. Read the authorization notice and continue.
5. When prompted enter the exact subject line (case sensitive) that you entered in your drafted Gmail message and click OK.
6. Once the emails have been sent, the column titled “Email Sent” will update to include the date and time that each email was delivered. - If an invalid email address is found, you will receive an alert in your Gmail inbox.

Importing Data from Excel to Mail Merge Sheet

1. Align your Excel document to match the Google sheet’s layout. The source (Excel) and destination (Google Sheet) must have identical formatting including, column count, column names, and column order.
2. Save the file as a CSV file (comma-separated value).
3. In your Google mail merge sheet, click “File” > “Import” > “Upload” and select your CSV file saved in step 2.
4. “Import Location” should be set to “Append to current sheet”. Other options should be left as default.
5. Click “Import Data”.