Menu
Back to Blog

How to Mail Merge from Google Sheets

Published by mail-merge-for-gmail on
How to Mail Merge from Google Sheets: the ultimate step-by-step guide

Do you need to send hundreds of personalized emails but find the process time-consuming and tedious? Manually copying and pasting each message is simply not an option.

 

Good news: mail merge with Google Sheets allows you to send personalized emails in bulk with just a few clicks.

 

In this guide, we will walk you through the process of mastering mail merge with Google Sheets. And you'll see—it’s not as complicated as it seems.

 

What is mail merge and why use it? 

Mail merge enables you to send mass personalized emails by combining an email template with a dataset.

 

Essentially, the email template contains dynamic placeholders like {{FirstName}} or {{Company}}, which are automatically replaced with actual data from your Google Sheets spreadsheet.

 

Mail merge is a powerful automation tool that helps:

  1. Save time by avoiding manual email sending

  2. Improve email personalization and increase recipient engagement.

 

⚡ Bonus! The high level of personalization makes mail merge an excellent fit for cold emailing as well.

 

Method #1: use the Mail Merge for Gmail extension to perform mail merge with Google Sheets

It’s a breeze.

 

Step 1: Install Mail Merge for Gmail from Google Workspace Marketplace.

 

Step 2: Fill in your Google Sheets content. Each row represents an email. The minimum requirement to send a campaign is to have a column with all recipient names. Add as many columns as needed.

 

Step 3: Modify the dynamic tags in the template editor. No need to write them manually—they are automatically generated from the data you entered in Google Sheets. No room for error!

 

Step 4: Draft your mail merge email template.

 

Step 5: Send your campaign.

 

Step 6: Track and Optimize. To enable tracking, simply check “Track emails” in the add-on.

 



💡We have created a short and precise video to explain the basics of mail merge with Gmail and Google Sheets

 

 

Method #2: use Google’s Mail Merge script

What does Google offer?

Google provides a native mail merge solution via Google Apps Script, a scripting language integrated into Google Sheets. This method eliminates the need for third-party extensions.

 

But… there’s a catch! This method is quite complex if you’re not familiar with coding. Plus, it’s time-consuming.

 

✅ Advantages:

  • Free

  • No need to install an extension

  • More flexibility by writing custom code

  • Native integration with Google’s ecosystem (Gmail, Google Sheets)

 

❌ Disadvantages:

  • Requires coding knowledge (Google Apps Script).

  • No advanced graphical interface: no message preview, and using rich HTML templates requires manual HTML coding.

  • No advanced email tracking: you can’t see who opened the email, clicked a link, or replied. No automatic handling of unsubscribes or invalid emails.

  • No automatic CRM integration with platforms like HubSpot, Salesforce, or Zoho CRM.

 

How to use it ?

Here are the steps to use Google’s mail merge script:

 

1️⃣ Prepare Your Google Sheets Spreadsheet

Create a new Google Sheets file.

Add columns with recipient information (e.g., First Name, Last Name, Email, Message).

Fill in the sheet with contact data.

 

2️⃣ Write an Email Template in Gmail

Open Gmail and click “Compose.”

Draft your message, including dynamic placeholders (e.g., {{FirstName}}).

Save it as a draft but do not send it.

 

3️⃣ Add and Run the Apps Script

In Google Sheets, go to Extensions > Apps Script.

Copy and paste the script provided by Google into the Apps Script editor (Official script link) and customize it.

Click “Run” to execute the mail merge.

 

4️⃣ Verify and Send Emails

The script will send emails based on the data in your Google Sheets.

Google Sheets will display a confirmation of sent emails.

 

Not convinced? Neither are we. That’s why we created the Mail Merge for Gmail extension.

 

Method #3: connect your Outlook account to Google Sheets

How does mail merge work with Outlook and Google Sheets?

This approach allows you to use Google Sheets to manage recipient data and send personalized bulk emails via Outlook.

 

If you thought using Gmail was complicated, brace yourself for Outlook!

 

There is no native integration between Google Sheets and Outlook. To make it work, you have to:

  • Use a third-party connector like Zapier or Power Automate

  • Configure Outlook as an SMTP server within an Apps Script

  • Add Outlook as an SMTP alias in Gmail (but Gmail will send the emails, not Outlook)

 

📌 What you need:

✔ An Outlook/Microsoft 365 account

✔ A Google account (required to use Google Sheets)

✔ A third-party connector or an SMTP script to link Google Sheets to Outlook

 

How to use it?

Option 1: use a third-party connector (Zapier, Power Automate, Make)

This is the recommended method for non-developers. However, premium versions of these tools may be required as free plans have limitations.

 

  • Create an account on Zapier, Power Automate, or Make.

  • Connect Google Sheets and Outlook via the connector.

  • Configure an automatic email-sending scenario based on your Google Sheets data.

  • Draft a personalized email template with dynamic tags ({{FirstName}}).

  • Test the sending process and activate the automation.

 

Option 2: Configure Outlook as an SMTP Server in Apps Script

This is an advanced method for developers looking for a completely free solution.

 

  • Generate an Outlook app password: Go to your Microsoft account, click on "Advanced security options," scroll to "App passwords," and generate a unique password. Save it for SMTP connection.

  • Create and populate your Google Sheets file.

  • Open Apps Script and add an SMTP-compatible script for Outlook.

 

Example of a correct SMTP script for Outlook:

function sendEmails() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var data = sheet.getDataRange().getValues();
    
    var smtpServer = "smtp-mail.gmail.com";
    var smtpPort = 587;
    var username = "[email protected]";
    var password = "your-application-password"; // Use an application-specific password

    for (var i = 1; i < data.length; i++) { // Start from 1 to skip the header row
        var email = data[i][1]; // Email column
        var firstName = data[i][0]; // First name column
        var message = data[i][2]; // Personalized message column

        var subject = "Hello " + firstName + " - Your Exclusive Offer";
        var body = "Hello " + firstName + ",\n\n" + message + "\n\nBest regards,\nYour team";

        GmailApp.sendEmail(email, subject, body, {
            name: "Your Company",
            replyTo: username,
            from: username,
            smtp: {
                host: smtpServer,
                port: smtpPort,
                auth: {
                    user: username,
                    pass: password
                }
            }
        });
    }
}

Option 3: add Outlook as an SMTP alias in Gmail (optional)

This method allows you to send emails via Gmail while displaying Outlook as the sender.

 

  • Open Gmail, go to Settings > Accounts and Import.

  • Add your Outlook address under "Send mail as".

  • Configure the SMTP settings: 

  • SMTP Server: smtp-mail.outlook.com 

  • Port: 587 

  • Password: Use the previously generated app password.

  • Confirm the addition via the code sent to your Outlook email.

 

💡 Note: Gmail will send emails through its servers but display your Outlook address. This is not a direct send from Outlook and does not constitute true Outlook integration, as Outlook does not handle email sending.



Expert Insight: optimizing your Google Sheets for Mail Merge

Even the best tools won’t make up for poor data management. Not a fan of Google Sheets? Our goal is to show you just how practical it can be for mail merge. Here’s our proven method for structuring your data.

 

Define a clear column structure

Each column should have a specific role and a consistent naming convention to avoid confusion during mail merge.

 

👉 Quick Actions:

🔹 Add colors to headers to differentiate them from data.

🔹 Use header freezing (View > Freeze > 1 row) to keep them visible.

Apply email format validation

If an email address is entered incorrectly, Google Sheets will display an error message.

 

👉 Quick Actions:

  • Select the Email column.

  • Go to Data > Data Validation.

  • Choose Custom Formula and enter this rule: =REGEXMATCH(A1, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$")



Prevent duplicate emails

To avoid sending multiple messages to the same person, add conditional formatting:

Select the Email column.

Go to Format > Conditional Formatting.

Add a custom formula: =COUNTIF(C:C, C1)>1

Choose a highlight color (e.g., red for duplicates).



Why choose Mail Merge for Gmail?

⭐ Capterra overall rating: 4.8/5

 

Mail Merge for Gmail is a simple yet powerful extension that integrates directly with Gmail and Google Sheets. No complex tools are required!

 

For a more in-depth demonstration, check out:



Made Life So Much Simpler

 

Overall: 

Revolutionized the way we do contact with customers and leads. Saves me so much time and I can now tell when people see my emails.

 

Pros: 

  • Can manage large mailing lists and not trip up on accidentally mailing large groups with cc instead of bcc -Easy to see read receipts to see if someone read your emails -Able to send cc'ed emails to the same customer group but still customized -Easy to manage mailing lists.

  • Easy to add customized fields either through Gmail drafts or in the built in email template creator -Just the best -Very cheap monthly subscription to send a lot of emails -Can identify when you have your fields wrong -can do attachments, and a ton of them.

 

Cons: 

Can be a bit intimidating for first time users, but it's pretty intuitive once you get going.

 

Switched From: 

Mailchimp

 

Reasons for Switching to Mail Merge for Gmail: 

Cheaper and easier than using Mailchimp with all their templates.

 

Here are its (many) advantages:

✔️ Intuitive and quick to use, ideal even for beginners.

✔️ Suitable for small teams and freelancers.

✔️ Flexible campaign management, allowing you to cancel an email send or include an unsubscribe link.

✔️ No data is shared with third parties.

✔️ Mail Merge cannot access your Gmail inbox or Google Sheets files.

✔️ Advanced security certifications, including CASA Tier 3 and ISO 27001.

✔️ Extremely reasonable pricing, even free options available.

 

 

Here are the essential features of this tool.

 

With Mail Merge for Gmail, you can design impactful and engaging emails enriched with images, emojis, links, and even attachments.

 

Another major advantage is its high level of personalization. By using dynamic fields, you can tailor each email to the recipient (name, company, etc.), while also customizing the sender and reply-to address for more targeted and professional communication.

 

 

Schedule your campaigns precisely by setting email delivery at the optimal time to maximize their impact. And that’s not all! You can also track email delivery in real time.

 

Mail Merge also provides handy tools to simplify campaign management. Pause an ongoing send if needed, insert an unsubscribe link to enhance user experience, or send a personalized email to multiple recipients with ease. Everything is designed to optimize your marketing strategy effortlessly.

Word of the end :

With a well-structured spreadsheet and the right tools, you can prevent errors, save time, and improve the personalization of your messages.

Why make things complicated? Try Mail Merge for Gmail now and simplify your email campaigns in just a few clicks.

 

Try Mail Merge for Gmail for Free Here!



Back to Blog