Mail Merge with SMTP: Send Custom Emails from Google Sheets

Email personalization isn’t just a “nice to have” anymore it’s expected. Whether you’re running a small business, managing a non-profit, or sending invitations to an event, sending the same generic email to everyone often gets ignored. That’s where Mail Merge with Google Sheets comes in. And if you connect it with SMTP (Simple Mail Transfer Protocol), you can send bulk personalized emails straight from your Gmail (or any email provider) without hitting limits too quickly.

In this guide, I’ll walk you step by step through how to set up and send personalized emails using Mail Merge in Google Sheets with SMTP. By the end, you’ll be able to send hundreds (or even thousands) of emails that feel tailor-made for each recipient.

What is Mail Merge?

Mail Merge is a technique that lets you create personalized messages by pulling data (like names, email addresses, and custom fields) from a spreadsheet. Instead of typing one email at a time, Mail Merge combines your email template with data from Google Sheets, and sends unique messages to each recipient.

For example:

• Instead of writing “Hello Customer”, you can send “Hello John”.
• Instead of sending one link for everyone, you can share unique coupon codes per person.

It’s a massive time-saver and increases engagement rates dramatically.

Why Use Google Sheets for Mail Merge?

Google Sheets is the perfect tool for this job because:

• Easy data management – You can store emails, names, custom fields, and tracking info in one sheet.
• Cloud-based – No need to install software; everything works online.
• Works with Gmail or SMTP – You can use your Gmail account or connect another SMTP server (like Outlook, Yahoo, or business email).
• Scalable – From 10 to 10,000+ emails, you can manage it all in Sheets.

If you’re already a Google Sheets power user, check out my guide on Google Sheets Shortcuts to speed things up while preparing your Mail Merge list.

What is SMTP and Why Use It with Mail Merge?

SMTP (Simple Mail Transfer Protocol) is the technology that allows your computer (or Google Apps Script) to send emails.

Here’s why connecting SMTP to Mail Merge makes sense:

• Avoid Gmail sending limits – Gmail caps you at ~500 emails/day (free) and ~2,000/day (Google Workspace). SMTP lets you go beyond.
• Use custom domains – Want emails to come from [email protected]? SMTP makes it possible.
• Better deliverability – Emails sent via professional SMTP servers are less likely to land in spam.

What You Need Before Starting

Before diving into the steps, make sure you have:

• A Google Account (for Google Sheets + Gmail).
• A list of recipients in Google Sheets.
• An SMTP server (can be Gmail, Outlook, Yahoo, or a business mail server like SendGrid, Amazon SES, or Zoho Mail).
• Basic familiarity with Google Sheets formulas (don’t worry, I’ll explain simply).

Step 1: Prepare Your Google Sheet

1. Open Google Sheets and create a new sheet.
2. Add column headers like:
   First Name
   Last Name
   Email Address
   Coupon Code
   Company
3. Fill in your recipient details. Example:

The column names will become the merge fields you’ll use later in your email template.

Step 2: Install a Mail Merge Add-on

Google Sheets doesn’t have Mail Merge built-in, but you can add it using an Add-on. Some popular ones are:

• Yet Another Mail Merge (YAMM)
• Mail Merge with Attachments (by Google Workspace)
• GMass

For this tutorial, we’ll use the free Mail Merge with Attachments add-on.

To install:

• Go to Extensions > Add-ons > Get add-ons.
• Search for Mail Merge with Attachments.
• Click Install.

Step 3: Create Your Email Template

Your email template is what you’ll send out. You can create it in Gmail or in the Add-on template editor.

Example template:

The double curly brackets {{ }} tell Mail Merge to pull that field from your Google Sheet.

Step 4: Connect Google Sheets with SMTP

Now comes the fun part connecting your sheet to SMTP so you can send emails at scale.

1. Open your Google Sheet.
2. Go to Extensions > Mail Merge with Attachments > Configure SMTP.
3. Enter your SMTP details:
   • SMTP Host (e.g., smtp.gmail.com or smtp.sendgrid.net)
   • Port (usually 465 for SSL or 587 for TLS)
   • Username (your email address)
   • Password (your app password, not your actual password)

Important: For Gmail and Outlook, you’ll need to generate an App Password instead of using your main password.

Step 5: Run the Mail Merge

1. Once SMTP is configured, click Start Mail Merge in the add-on.
2. Select your email template.
3. Choose the sender address (SMTP account).
4. Set sending options (schedule, track opens, etc.).
5. Click Start.

Google Sheets will now send emails one by one, pulling personalized data for each contact.

Bonus: Adding Attachments

If you want to send PDFs, invoices, or images:

• Place the file links in a column (Attachment).
• Use the add-on to map attachments to each recipient.

This is great for sending certificates, personalized reports, or event tickets.

Step 6: Test Before Sending to Everyone

Always test before hitting “Send All.”

• Create a test row with your own email.
• Run the Mail Merge with only that row selected.
• Check if personalization and SMTP delivery work.

Best Practices for Mail Merge with SMTP

• Don’t spam – Keep your emails valuable.
• Warm up SMTP accounts – New email addresses should start with small batches.
• Track performance – Use tracking to measure opens and clicks.
• Use plain text + HTML – Fancy designs can hurt deliverability.
• Clean your list – Remove invalid emails to avoid bounces.

Troubleshooting Common Issues

Emails going to spam?

• Authenticate your domain with SPF, DKIM, and DMARC.
• Avoid spammy words like FREE, BUY NOW, 100% GUARANTEE.

SMTP login not working?

• Check if you’re using an App Password.
• Make sure your server allows SMTP connections.

Reached Gmail limits?

• Use a dedicated SMTP service like SendGrid, Amazon SES, or Zoho Mail.

When to Use Mail Merge with SMTP

• Newsletters for small businesses.
• Event invitations with personalized details.
• School reports sent to parents.
• Non-profits sending donation receipts.
• HR departments sharing salary slips.

Alternatives to Google Sheets Mail Merge

If you find this process too technical, you can also explore:

• Mailchimp – Great for marketing campaigns.
• Kit (ConvertKit) – Best for creators and bloggers.
• Sendinblue – Affordable with SMTP relay included.

But honestly, Google Sheets + SMTP is the most cost-effective DIY solution.

Conclusion

Sending personalized emails using Mail Merge in Google Sheets with SMTP is a powerful way to:

• Save hours of manual work.
• Reach more people without Gmail limits.
• Boost engagement with personalization.
• Maintain full control of your email process.

With just a Google Sheet, an email template, and SMTP access, you can send professional, targeted emails that actually get read.

So the next time you think about blasting the same message to everyone don’t. Instead, set up a Mail Merge workflow and watch your engagement (and responses) go up. Also, if you love working with Google tools, don’t miss my guide on Google Docs Shortcuts to make your daily workflow even faster.

READ NEXT:

• Advanced Gmail Search Operators: Find Emails Faster
• How to Get the Favicon Image of Any Website Using Google
• 20 Important URLs That Every Google User Should Know
• How to Make Any Song as Your Ringtone on Android