Product: Gmail

Audience: Users who want to send personalized emails using mail merge in Gmail.

Overview

This article guides you through the process of setting up a spreadsheet and email for performing a mail merge in Gmail. You can use mail merge in Gmail to send personalized email campaigns, newsletters, and announcements to a wide audience.

Important: Mail merge replaces multi-send mode in Gmail.

How Mail Merge Works

Mail merge lets you personalize messages with merge tags, such as @firstname and @lastname. When you send a message, each recipient gets a unique copy of the email in which the merge tags are replaced with their details. Recipients can’t check who else you sent the message to. To easily manage conversations, you'll get the recipient’s replies in separate threads. If you have a large number of recipients, you can link a spreadsheet that contains their contact information. Any column in the spreadsheet can be used as a merge tag in your message. It includes custom details for each recipient to personalize your message.

Create a new Mail Merge

Prepare Your Spreadsheet in Google Sheets

• Open Google Sheets and create a new spreadsheet.
• The first row of your spreadsheet should contain headers that will be used as placeholders in your email template. These headers will become your merge tags.
  • For example:
    • FirstName
    • LastName
    • ContactEmail
    • Message
• Enter your contact data in the rows below the headers. Ensure that the "ContactEmail" column contains the email addresses of your recipients.
• Important: Contact information must be in the first tab of your spreadsheet and can only contain text.
• Example Spreadsheet:

Compose Your Email in Gmail and Add Recipients

• On your computer, open Gmail.
• At the top left, click Compose. You can also open an existing draft.
• On the right of the "To:" line, click Use mail merge.
• Turn on Mail Merge.
• You'll likely click “Add from a spreadsheet."

Add Recipients from a Spreadsheet (If Applicable)

• If your recipient information is in a spreadsheet:
  • In Gmail, with Mail Merge turned on, click Add from a spreadsheet.
  • Select a spreadsheet.
  • Click Insert.
  • In the window, select the columns from your spreadsheet that have recipient info:
    • Email
    • First name
    • Last name (optional)
  • Click Finish. Your spreadsheet is added to the "To:" line in the message.
• Tip: When you use a spreadsheet for recipient information, check the text characters used in your column headers and email addresses.
  • If a column name contains special characters other than letters or numbers, you can identify the corresponding merge tag in Gmail by its position. For example, the first column would be called "@A."
  • Email addresses that contain special characters are considered invalid.

Add Recipients Directly to Your Message (Alternative)

• If your recipients are not in a spreadsheet, you can add them directly to the "To:" line in Gmail, as mentioned in step 2.
• Tips for adding recipients directly:
  • To ensure your message uses the correct recipient name, check their name in Google Contacts.
  • To add multiple recipients, create a label in Google Contacts and group recipients. When you add the label in the "To:" line in Gmail, the grouped recipients populate automatically. Learn how to organize contacts with labels.
  • If the recipient isn’t in Google Contacts, mail merge populates the first and last name based on what you enter in the "To:" line. For example, if you enter "Lisa Rodriguez [email protected]" as a recipient, Gmail uses "Lisa" as @firstname and "Rodriguez" as @lastname.

Use Merge Tags in Your Email

• In your message, enter @.
• Select a merge tag:
  • @firstname
  • @lastname
  • @fullname
  • @email
  • Merge tags are determined by the column headers in your spreadsheet.
• To insert the merge tag, press Enter.
• You can't use merge tags in:
  • Subject lines
  • Hyperlinked text
• Note: You cannot “reuse” Gmail Templates or Google Doc templates without readding your merge fields. Otherwise they will exist as text that says “@firstname” etc.

Handle Missing Recipient Information (Default Values)

• If you have a recipient with missing information for a merge tag, you'll get an error message. For example, you get an error if you try to email "Sam [email protected]" and use either the @firstname or @lastname merge tag. This is because Gmail can’t be sure whether "Sam" is this person’s first name or last name.
• In this situation, you can:
  • Enter a default value in the error message. For example, for recipients who don’t have a first name, "Hi @firstname" can be "Hi friend."
• Go back to the draft and:
  • Add the missing value in the "To:" line, in Google Contacts, or in the spreadsheet you linked.
  • Remove any recipient with missing values from the "To:" line or the spreadsheet you linked.

Send Your Mail Merge

• Once you've composed your email and added recipients (from a spreadsheet or directly), send the email.
• You can send a test message that will only go to you, simulating what the merge would be like.

Find Your Sent Messages

• To find your sent messages, open the "Sent" folder in Gmail.
• In the message, you'll find a "Sent with mail merge" banner.

Common Mail Merge Errors and Fixes

Error: Blank or Missing Emails

Error Message: Emails are not being sent to some recipients, or some emails are missing data.

Fix: Ensure there are no blank cells in the column used for email (e.g., "ContactEmail").

To highlight blank cells in Google Sheets:

• Select the column with the email addresses.
• Go to "Format" > "Conditional formatting."
• In the "Format rules" panel, under "Format cells if...", select "Is empty."
• Choose a formatting style (e.g., fill with yellow) to highlight the empty cells.
• Click "Done."

To filter and find blank cells:

• Select the column with the email addresses.
• Go to "Data" > "Create a filter."
• Click the filter icon that appears in the column header.
• In the filter menu, deselect "Select all" and then select the "Blanks" option.
• Click "OK." This will show only the rows with blank email addresses. Fill these in, and then remove the filter.

Error: Duplicate Emails

Error Message: Emails are sent multiple times to the same recipient.

Fix: Identify and manage duplicate emails in your spreadsheet.

Method 1: Using COUNTIF (in Google Sheets)

• In a helper column (e.g., Column J), use the COUNTIF function to identify duplicate emails.
• For example, if your email addresses are in Column H, starting from row 2, enter this formula in cell J2: =COUNTIF($H$2:$H, H2)
• This formula counts how many times the email address in cell H2 appears in the entire column H.
• Drag the fill handle (the small square at the bottom-right of cell J2) down to apply the formula to all rows.
• Any cell in Column J with a value greater than 1 indicates a duplicate email.
• You can then filter the sheet by Column J to isolate the duplicates.

You can use this Sample Mail Merge Sheet to see this in action.

Important Mail Merge Considerations

Send Limits

• Work, school, and Workspace Individual accounts: 2,000 outgoing messages per day.
• Mail merge limits:
  • Add up to 1,500 recipients in the "To" line per message.
  • Send to a maximum of 1,500 recipients per day. (You can send one message to 1,000 recipients and another to 500.)
• The 1,500-recipient daily limit for mail merge allows you to send up to 500 normal messages per day without exceeding the 2,000 limit (for eligible accounts).
• There’s no limit to the number of unique recipients you can contact per month with mail merge.

Cc and Bcc Recipient Limits

• You can only have one recipient in the "Cc" or "Bcc" field in each message with mail merge.
• Any recipient added to "Cc" or "Bcc" is copied on each outgoing email.
• Example: Sending a message to 500 recipients with "[email address removed]" in "Bcc" results in "[email address removed]" receiving 500 copies.
• Recipients in "Cc" or "Bcc" count toward your daily send limit. (In the example above, it would use 1,000 of your daily send limit.)
• Important: If you added recipients using a spreadsheet, you can’t include "Cc" or "Bcc" recipients with your message.

Unsubscribed Recipients

• Mail merge automatically adds a unique unsubscribe link to each email.
• Recipients can use this link to unsubscribe or resubscribe.
• You'll receive an email notification when a recipient unsubscribes or resubscribes.
• You can't get a list of all unsubscribed recipients.
• After sending a message, the confirmation box shows the total recipients who unsubscribed and won’t receive the email.
• Important: Unsubscribed recipients can still receive messages sent without mail merge or from other accounts in the same domain.
• If you use a work or school account:
  • Unsubscribe data is retained when a user account is deleted.
  • Recipients can't unsubscribe from senders within their organization.

How to Avoid Your Messages Being Marked as Spam

• Follow best practices and respect your recipient's inbox.
• Adhere to local regulations and Gmail policies.
• Send messages that connect you and your recipients in a meaningful way.
• Refer to bulk email best practices for more information.

Mail Merge Limitations

• Mail merge cannot be used with:
  • Reply
  • Forward
  • Schedule send
  • Confidential mode
• Attachments are included in each recipient’s copy, which can use a large amount of storage.
• Tip: To save storage space, upload files to Google Drive and link to them in your message.

Conclusion

This article will enable you to prepare a spreadsheet and email for a Gmail mail merge, allowing you to effectively send personalized email campaigns, newsletters, and announcements to a large audience.

More Information

Visit Google's Help Document here.

For instructional/pedagogical questions, please contact your Building Administrator or Student Growth & Experience (SG&E) Level Coordinator.

For technical questions, please contact your building SPOC.