How to Use Mail Merge in Gmail via Google Sheets (For Free)

Spread the love


Gmail and Google Sheets are free services, everyone can use them to send bulk emails. This guide will help you sending customized and personalized emails to a large list of recipients and you can also include attachments if you want. This will help you minimize the efforts, and this solution is ideal for small businesses, small community groups, students and personal use. We will also include a small guide to help you validate your email addresses using absolutely free Google Sheets formulas.

Here is a simple step-by-step guide:

  1. Login to your Gmail account and open this link “Gmail/Sheets Mail Merge Script by Google”. This link will take you to a Google Sheets document, where the Mail Merge Script has already been added. You might see the following warning, but don’t worry the script is from Google and can be trusted. Clink on “Make a copy” button.
  • Clink on “Make a copy” button, this will copy the Gmail Sheets mail merge spreadsheet with Mail Merge script in your Google Drive.
  • Customize the spreadsheet by adding or deleting columns based on the fields you want to include in your email.
  • Ensure that you have a “Recipient” column containing all the email addresses and an “Email Sent” column to track successful email delivery.
  • Open a new message in your Gmail account and start composing your email. Use curly brackets ({}) to indicate the fields you want to insert from the spreadsheet.
  • You can use these fields in the Subject and Email Body, wherever you want. For example, you can type “{First Name}” in the email to personalize the greeting with the recipient’s first name.
  • Copy the subject line of the email and keep it handy for later use during the mail merge.
  • Go back to Google Sheets and click on “Mail Merge” in the top menu, then select “Send Emails” to start the mail merge process.
  • Authorize the script to run by granting access when prompted.
  1. Paste the subject line you copied earlier into the prompt asking for the Gmail draft message.
  2. Click “OK” to start the mail merge. Select your desired Gmail Account, you might get an error message saying “This App isn’t Verified’, don’t worry it is from Google, so “Allow” it and click on the Mail Merge again, then click on Send Email button. The script will run, and you’ll see confirmation of successful email delivery in the “Email Sent” column.
  3. Check your Gmail inbox to see the custom email messages sent out using the mail merge.
  4. If everything works correctly, you can add more rows to your spreadsheet and repeat the mail merge process for additional recipients.
  5. If you want to explore advanced features, you can access the script editor by going to “Tools” > “Script Editor” within Google Sheets.
  6. In the script editor, you can modify settings like BCC, CC, sender name, and attachments to further customize your mail merge.
  7. Keep in mind the daily sending limits for email accounts: up to 100 emails per day for free accounts and up to 1,500 emails per day for G Suite accounts.


  • Ensure you have a column called “recipient” that contains all the different email addresses for the mail merge.
  • Keep the “email sent” column intact to track the success of the email delivery.
  • Use the correct curly brackets “{{ }}” to insert the fields from the spreadsheet into your email template.
  • Test the mail merge with a sample email before sending it to a large number of recipients.
  • Make use of the BCC and CC options if you need to send copies of the email to other recipients.


  • Don’t modify the “recipient” and “email sent” columns as they are crucial for the mail merge process.
  • Avoid exceeding the email sending limits based on your account type (100 emails per day for free accounts and up to 1,500 per day for paid G Suite accounts).
  • Don’t forget to double-check the email template and subject to avoid errors in the mail merge.
  • Avoid sending sensitive or confidential information through a mail merge without proper encryption or security measures.
  • Don’t forget to review and proofread the email content to ensure accuracy and professionalism.

Common Mistakes:

  • Forgetting to include the necessary curly brackets “{{ }}” around the field names in the email template.
  • Accidentally modifying or deleting the “recipient” and “email sent” columns, disrupting the mail merge process.
  • Sending the mail merge without testing it with a sample email, leading to errors or incorrect data being sent to recipients.
  • Ignoring the email sending limits, which could result in issues or restrictions on your account.
  • Failing to review the email content thoroughly, leading to spelling mistakes, incorrect information, or unprofessional formatting in the final email sent to recipients.
  • Remember to follow these dos and don’ts, and be cautious about common mistakes to ensure a successful and error-free mail merge using Gmail and Google Sheets.

Step-by-Step Guide: How to Use Google Sheets to Verify Email Address for Free

1. Identify the Column:

  • Locate the column in your Google Sheets where the email addresses are stored. For example, let’s assume the email addresses are in Column B.

2. Insert the Validation Formula:

  • Select the range of cells containing the email addresses (Column B).
  • Go to the “Data” menu at the top of the spreadsheet and choose “Data Validation” from the dropdown.
  • In the data validation dialog box, select “Custom formula is” and enter the following formula:

=regexmatch(B:B, "^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$")

  • This formula uses a regular expression pattern to check if the email addresses are valid.

3. Press Enter:

  • After entering the formula, press Enter to apply the data validation to the selected range.

4. Apply to Other Cells:

  • The data validation is now applied to the email addresses in Column B.
  • If you have more email addresses in other cells or columns, you can drag the corner of the selected range to apply the validation to those cells as well.

Now, the email addresses in the specified column will be validated based on the provided formula. If an email address doesn’t match the regular expression pattern, an error message will be displayed, ensuring the authenticity of your email data. Keep your email list accurate and reliable with this simple yet effective method in Google Sheets.


In conclusion, verifying email addresses in Google Sheets is a hassle-free and budget-friendly method to ensure the accuracy of your email data. This step-by-step guide empowers you to personalize your emails effortlessly by integrating Google Sheets with Gmail. Now, you can confidently engage in personalized bulk email communications, delivering relevant content to your recipients with ease. Take advantage of this free and straightforward solution to enhance your email campaigns and boost communication efficiency. Happy emailing!

Spread the love

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *