Installation

Simple Gmail Merge is a Google Sheets Add-on using which you can send personalized emails to multiple recipients in your worksheet, all in one go. Click here and follow instructions to install the Add-on.

Features

• Really free (no premium plan), unlimited number of emails can be sent.
• Simple Gmail Merge uses any draft in your Gmail as a template.
• It supports CC, BCC and attachments in the draft.
• It uses the data in a spreadsheet as the list of recipients to send an email to.
• The template can contain variables. For example, ${FirstName} will be replaced by a column named FirstName in the spreadsheet.
• You can send an email to yourself to do a dry run before sending it to everyone.
• There are lots of validations and tips to guide you through the process, so that you don't go wrong.

How to use Simple Gmail Merge

After you install the add on, create a new empty Google Spreadsheet, Click Add-ons > Simple Gmail Merge > Start Merge. The resulting dialog will guide you through the process. That's it!

If you have some time and want to know more about Simple Gmail Merge, read on.

Simple Gmail Merge uses data in rows, one per recipient to send out emails to. The first row in a sheet is used as the header row, as is normally done in Google Sheets. The values in the header row (column names) also form variable names which can be used in the template to personalize the email.

Start with a sheet like this:

Note that the columns Email and Status must exist in the sheet, otherwise, you will find validation errors while running the mail merge. The Email column must contain the recipient mail IDs, and the Status column must be left blank for it to be processed.

Next, create a draft in Gmail that you'd like to use as the template, like this:

Note that the above example uses ${company} in the subject as well as in the body of the email. The recipient was addressed by their first name using ${firstname}. We have not used LastName anywhere, but that's OK, not all columns have to be used. The variable names are not case-sensitive, so, you could also use ${Company} and ${FirstName} instead. You can also use the same variable as many times as you like.

A subject is very important, that's how the next few steps identify the draft.

Now, you're ready to start sending the mails to all the recipients. Click on Add-ons -> Simple Gmail Merge -> Start Merge. This will open up a dialog like this:

You'll find that the action buttons at the bottom are disabled and there is some error message. Note that Simple Gmail Merge shows all errors in red.

If you read the error message carefully, it's obvious that all you need to do is select a draft. The list of drafts will be automatically picked up from your Gmail account, and you can pick the draft you just created. It will be identified by the subject of the draft.

Now pick the draft that you just created. You should see that the validation error has not gone, there is a new error message instead:

Aha! So, it appears that the email draft had some issue. It has used ${name}, whereas it should have used ${FirstName}! Correct the draft, close the dialog and restart the merge and select the draft again. Now you'll find that all validations passed and the action buttons are enabled.

Spend a little time reading the information messages.

The first message tells you what the add on detected in terms of number of variable columns and the number of recipients. If any of the status fields weren't empty, they won't be processed, so the message also indicates how many recipients are actually marked for sending. Ensure that this is what you really want.

The second message is about the variables that were found in the email draft. This also meant to be a check -- that the intended variables have been detected and this is what you want.

Before sending out the emails, it's a good idea to send yourself a test mail. Click on Send test mail to Self. The add on will use the first recipient's variables and send the mail to yourself (the email ID that is shown as Sender's Email. Check the email message to ensure everything is in order. Once you are happy, click Send all mails, and all the recipients will get their personalized email.

The status column will be updated with the result of the sending. If there was any error while sending, it will be updated with the error messages, otherwise, it will be set to "Sent". This is so that another attempt does not re-send the mails already sent. For failures, correct the cause as indicated by the message and clear the contents of the status column for each row that you need to attempt sending again.

Tips and tricks

Copies

To add a CC or a BCC to the mail, just add it in the draft.

Sender Name

Most email clients show the Sender Name in the From column in the inbox. Simple Gmail Merge cannot automatically determine your name, so you have to set it. This is usually your full name as it appears in your Gmail settings.

Recipient Name

Sending a mail to the plain email ID like some.one@example.com will work, but if the recipients look at the details, they can see that the email is different from a normal email that is sent by you manually, because it does not have the name in the to field. If you want to prevent this, the email ID should be like Some One <some.one@example.com> instead.

Formulas

The data in any of the columns can be formulas. An interesting use of formulas is to generate the email ID with the recipient's name from the plain email ID and the full name like this:

=CONCATENATE(B2, " ", C2, " ", "<", D3, ">")

If column B contains the first name, C contains the last name, and D the plain email ID, the above formula will generate the email ID with the recipient's name in the required format.

Or, vice versa. If you already have the Name and email ID combined, you can use the SPLIT function to generate the individual components like this:

=SPLIT(A3, " <>", true)

If column A contains the full name and email combined, then the above formula in column B will result in the first, last and plain email ID in the columns B, C and D respectively. Take a look at the sample in this sheet:

Status

You can use the Status column in inventive ways to categorise your recipients. Only rows with an empty status are processed. For example, you could use Later to indicate you want to batch recipients and send some mails later. The add on automatically adds Sent in the status for all sent emails, so it prevents re-sending unless you explicitly clear the Status column for some rows.