Yuka Kato
Personalizing emails increases user engagement, and personalizing an email to a single recipient is easy. However, personalizing the message for each recipient can be tricky when sending an email to multiple recipients. This is where mail merge comes in!
Mail merge allows you to personalize bulk messages. You send one email to a group, but each recipient will receive a personalized message.
This article will discuss how to mail merge with Google Sheets using different methods.
How to Mail Merge in Google Sheets: The 3 Best Methods
Remember that mail merging combines an email template and a data source. Google Sheets is an easy way to store all of your recipient data and it’s also an easy tool to use for a mail merge. While you can also perform a mail merge directly in Gmail, this article will show you how to perform one directly in Google Sheets.
There are three main ways to mail merge with Google Sheets, each with its pros and cons. This section will discuss these three methods. They are:
1. Use an Extension (Mailsuite) to Mail Merge with Google Sheets
The best way to send mail merge emails with Google Sheets is using an extension.
A Google Sheets extension like Mailsuite streamlines the mail merge process, allowing you to send personalized emails to multiple recipients with only a few clicks.
Mailsuite also equips you with many advanced features (like email tracking), turning your spreadsheet into a powerful email marketing tool.
The steps for using Mailsuite to mail merge with Google Sheets are as follows:
Step 1: Get Mailsuite add-on
Go to the Google Workspace Marketplace. Then, hit the blue Install button and follow the prompts to add Mailsuite to your Gmail.
Step 2: Set up the mail merge
After successfully installing mail merge, open the Google Sheet with all of your recipient data. Once all of your data has been added, navigate to Extensions>Mail Merge for Mailtrack>Start a Mailmerge
Step 3: Write the template for your mail merge email
A window will appear where you can write the subject line and body of your email. Mailsuite automatically imports the existing mail merge fields from the spreadsheet into the list of variables on the right side of the email composing screen. Make sure to personalize your emails with merge tags! Confirm your email address is in the “Sending from” field and that the number of recipients is correct.
Step 4: Send a test email.
Once everything in step 3 is complete, click Send preview email. This will send a test email to your own email address. This is an important step because you’ll be able to see the email as your recipients will see it. Make sure to check for typos and grammar errors, formatting issues, and ensure the merge fields imported the data properly.
Step 5: Send your campaign.
If you’re happy with how the test email looks in your email account, go back to your Google Sheet and click Send campaign. This will send your email to all of your recipients.
2. Use Google’s Mail Merge App Script
The Google Mail Merge App Script allows users to create a mail merge with Gmail & Google Sheets to streamline sending personalized messages to a large number of recipients.
It involves using the built-in Google App Script in Google Sheets to retrieve data from a spreadsheet and use it to populate a template email.
Typically, the script loops through each row in a Google Sheet to extract relevant data for each recipient and uses this to customize the email content for that recipient.
The steps for using Google’s Mail Merge App Script to mail merge using Google Sheets are as follows:
Step 1: Set up your Apps Script to automate Gmail mail merge
There are two ways to set up your script – manually and using a Google shortcut.
The manual method requires manually writing a Javascript script to automate the mail merge function. It starts with creating a Google Sheets spreadsheet containing recipients’ information for your email.
Each row in the spreadsheet should represent a recipient, and each column should hold the recipients’ unique data you want to include in your personalized email (such as first name, last name, email address, and date of signup).
Next, open the built-in Google App Script editor by clicking Extensions followed by Apps Script.
Then, write your script into the editor.
Understandably, writing a Javascript script to automate mail merge is not for everyone, as the majority of email marketers are not developers.
So, Google provides a shortcut, allowing users to download a sample spreadsheet with a pre-written mail merge app script.
If you’re not a developer, go with this shortcut and use Google’s Mail Merge App Script to send personalized emails at once to multiple recipients.
To do this, download a copy of the mail merge Gmail-Google Sheets sample spreadsheet. Below is the sample spreadsheet:
Modify the spreadsheet with your recipients’ data. Add recipients in rows. Update the email address column with your recipients’ addresses. Edit columns to customize the data you want to include in your email and remove any unnecessary columns.
Step 2: Create an email template
After setting up your script for your spreadsheet, you should draft the email you want to send.
Open your Gmail account and click Compose. Type your template text into the compose window and include placeholders in the text where you want to add recipients’ data.
Placeholder text should correspond with column headers in the spreadsheet and be enclosed by brackets, e.g., {{First Name}}.
Step 3: Run the Script
After setting up your script and spreadsheet and creating your mail merge template, the next thing is to run the script.
To do this, open your spreadsheet, click Mail Merge, and then Send Emails.
Authorize the script when prompted.
If the “Google hasn’t verified this App” window appears, click Advanced > Go to Gmail/Sheets mail merge (unsafe). Then, click Allow to allow Gmail/Sheets mail merge access to your Google Account.
Go back to your spreadsheet and click Mail Merge > Send Email. In the pop-up window, paste the subject line of your email template and click OK.
Gmail will automatically send the email to your recipients, extracting data from the spreadsheet to personalize the email for each recipient.
Advanced Features of Google’s Mail Merge Spreadsheet
Google’s Mail Merge spreadsheet has several advanced features you can unlock to supercharge your mass mailing campaigns too. These include:
- CC and BCC fields: This feature allows you to add someone (like a coworker or business partner) in the CC or BCC field of your mail merge emails so they can confirm you sent them. You don’t need to add your mail merge recipients in the BCC field though since each email will be sent individually to them..
- Send From email address: This feature allows you to enter the email address that recipients will see as the sender’s email address when they get your email.
- Name of Sender: This feature allows you to enter the name that recipients will see as the sender’s name when they get your email.
- Reply To email address: This feature adds a specific email address to your emails that recipients can reply to.
- No Reply: This feature sends your emails from a generic no-reply email address that recipients cannot reply to.
To unlock these advanced features of Google’s Mail Merge spreadsheet, do the following:
Step 1: Open the built-in Google App Script editor
Open your spreadsheet, click Extensions, and then click Apps Script.
Step 2: Find the advanced parameters.
In the App Script editor, scroll down to the “Uncomment advanced parameters as needed” line.
Step 3: Uncomment advanced parameters
The advanced features are deactivated by “//,” as that designates them as comments. To activate any of the advanced features, remove the forward slashes and enter valid data.
For example, to activate the Reply To feature, remove the forward slashes in the Reply To line and enter a valid email address to which you want recipients to send their replies.
3. Connect Your Outlook Account to Google Sheets
Even if you use Outlook, you can still perform a Google Sheets Mail Merge with Gmail. Both Gmail and Outlook can perform mail merges (even mail merges with attachments), but Gmail tends to be the easier platform to use.
The process involves adding your Outlook account to your Gmail as an alias. This enables the Gmail Mail Merge add-on to send emails through your Outlook account.
The steps for doing this are:
Step 1: Generate an app password for Outlook
Log in to your Outlook account. Choose the Advanced security option. Scroll down to the App passwords section and click Create a new app password.
Copy the password revealed in the next pop-up, and click Done.
Step 2: Add your Outlook account to Gmail
Log in to your Gmail account. Click on the gear settings icon and then See all settings. In the settings window, switch to the Account and Imports tab.
Scroll down to the “Send mail as” section and click Add another email address.
Enter your Outlook email address into the new window that pops up and click Next Steps.
On the next pop-up, paste the password generated in step 1. Then, click Add Account.
Gmail will email a verification code to your Outlook email address. Sign in to your Outlook account, copy the code, and paste it into the verification box.
Your Outlook account will automatically be added as an alias to your Gmail account.
Step 3: Change Email Sender in Mail Merge
Open your Google Sheets data file and click the Mail Merge icon in the sidebar. In the Configure section, click Sender’s Email and choose the Outlook email address you added as an alias to your Gmail.
You can then send Google Sheets merge emails on behalf of your Outlook account.
The good thing is that you only need to connect your Outlook account one time. While it’s possible to send a mail merge in Outlook, that’s a more complicated process and involves Outlook, Microsoft Word, and Microsoft Excel. If you’re an Outlook user, this is the most efficient method.
Which Method is Right for You?
To determine which of the three main methods of mail merging with Google Sheets is right for you, you only need to see how they compare against each other.
| Tool/Feature | Ease of Use | Customization | Tracking Features | Pricing |
|---|---|---|---|---|
| Google’s Mail Merge App Script | Moderate | Poor | None | Free |
| Outlook with Google Sheets | Difficult | Poor | None | Free |
| Mail Merge Extension (Mailsuite) | Easy | Great | Advanced | Cheap |
Using Mailsuite to mail merge with Google Sheets comes out on top, as it makes the mail merge process easy, offers good customization options, has advanced tracking features, and is relatively inexpensive.
While Google’s Mail Merge App Script and connecting Outlook to Google Sheets are free, these methods are not straightforward and don’t always work correctly, offer poor customization, and do not provide any email tracking.
With the Mailsuite extension, you do not need to set up a script (as with Google’s Mail Merge App Script) or add an alias to Gmail (as with Outlook and Google Sheets). Mail merging is simple and requires only a few extra steps.
Importantly, Mailsuite comes with advanced email marketing features, including email tracking. With email tracking, you can know when recipients read your emails and click your links. This helps you measure the effectiveness of your campaign and craft more relevant follow-up communication.
For example, when you know that a recipient read your email and clicked your link, your next email to them would differ from a follow-up to another who hasn’t read your email.
Takeaway: Mail Merge with Google Sheets like a Pro
Mail merging helps you send personalized emails to multiple recipients at once. You send out one email, but each of your numerous recipients gets a personalized message. This can increase user engagement (like open rate and clickthrough rate, leading to more successful campaigns.
This article discusses the three main methods of mail merging with Google Sheets: using Google’s Mail Merge App Script, connecting Outlook to Google Sheets, and using an extension to merge mail with Google Sheets.
The best of the three methods is to use an extension to mail merge with Google Sheets, as it streamlines the process and supercharges advanced features like email tracking.
There is no better Google extension to mail merge with Google Sheets than Mailsuite. The effective extension makes mail merging using Google Sheets easy. Plus, it offers superior email tracking ability and many other email marketing features.
To help you start mail merging like a Pro, read our other articles about the topic:
Step by Step: Excel to Word Mail Merge Mastery
Mail Merge PDF: Send Emails with a PDF Attachment
Effortless Outreach: How to Do Mail Merge Using Google Docs
How to Use the New Mail Merge in Pages (Ultimate Guide)
Mail Marge in Google Sheets FAQ
What is a Mail Merge in Google Sheets?
Simply put, mail merge is an emailing feature that lets you send personalized mass emails using data stored in a Google Sheet. Each email can contain unique information from the spreadsheet. Without mail merge, sending personalized mass emails would be impossible.
You’d have to manually craft each email with each recipient’s personalized data. Thus, emailing 100 recipients will require crafting 100 emails, which will make mass emailing take forever.
Thankfully, mail merge automates the process of sending personalized mass emails by allowing you to send one email to many recipients, each of whom receives a personalized message.
Mail merge does this by connecting an email template with a data source (such as a spreadsheet) that has all of your recipient data.
The email template contains the message you want to send along with placeholders for recipients’ unique data, like first name and last name.
Mail merging pulls each recipient’s data from the spreadsheet and inserts it into the appropriate placeholder in the template, thereby creating personalized messages for each recipient.
There are many reasons you may need to use mail merge in business communication. These include:
- Sending personalized emails to customers for special promotions
- Sending mail merge documents like invoices with personalized details to clients
- Sending personalized sales emails to prospects based on their interests (like the specific product they viewed)
- Sending personalized renewal reminders to customers (with details about their individual subscription status and how to renew)
How do I set up a Mail Merge in Google Sheets?
You need to use a Google Sheets add-on like “Mail Merge for Google Sheets™ by Mailtrack“. Install the add-on, prepare your Google Sheet with the necessary data (such as email addresses and personalized content), and simply follow the add-on instructions to create and send mail merge emails.
How do I customize the email content in Mail Merge?
Customize your email content by using placeholders in your email template that correspond to column headers in your Google Sheet. For example, use {{FirstName}} in your email template, and the add-on will replace it with the actual names from the corresponding column in the sheet.
Is it possible to track emails sent through Mail Merge in Google Sheets?
Yes, some Mail Merge add-ons like Mailsuite offer email tracking features. With Mailsuite, you can track opens, clicks, and other engagement metrics, providing you with insights on how recipients interact with your emails.
