Use Google Sheets together with your Gmail account to generate and distribute customized bulk messages quickly and easily. The method explained in this tutorial is ideal for sending personalized seasonal greetings to friends or employees, for customer outreach, or for issuing transaction documents to lists of clients/customers. The obvious advantage of this, is that your computer does the repetitive work, saving you time and frustration, and it's completely free for personal or small business use.
Mail Merge takes a draft email message, selects information from a spreadsheet of data, (each row containing details pertaining to an individual client/customer), and combines these inputs into individual emails.
Since Google Sheets doesn't have any native email functionality and we don't want to deal with Google Apps script, we can use Lido to mail merge and send emails with your Google Sheets data. Create a free account here.
In order to make things work smoothly, clean up your Google Sheet so that it meets these criteria:
Create a new Lido file. Then, click the Connect Data button in the top left.
Select Google Sheets, then paste in the URL of your Google Sheet from step 2. You will be prompted to log into a Google account with access to the Google Sheet the first time that you connect a Google Sheet to Lido.
Next, select the columns that you want to connect. Make sure you include every column that you want to include in the message of your mail merge email.
Click Add Data. This will create a table in Lido. Your Google Sheets data will stay connected and refresh automatically.
Next, we will draft our email subject and body. To save time, we'll use Lido's computed columns to apply the same template to every row in the table that we want to send an email to.
Hover over the table, then click the menu and select Add Computed Column. This will create an empty blue column.
Inside of the new computed column, we can use the [@Column Name] syntax to reference values from different columns directly in our email. For example:
Remember to put your text in quotes (" "). Notice how First name from each row has been substituted into where [@First name] is in the formula.
We'll use this method to make one column for email subject, and another for email body. You can reference as many different columns as you want inside of the email template.
Your file at this stage should look like this:
Next, we will use Lido's SENDGMAIL() formula to send out our emails to every row.
First, create another computed column.
Inside of the new computed column, type =SENDGMAIL
If it's your first time using this formula in Lido, you will need to click + Add Credential to connect your Gmail account.
After you have successfully added your credential, we can continue with the rest of the formula.
The formula for SENDGMAIL is:
```=SENDGMAIL(<sender-credential>, "recipient@email.com", "Subject", "Body")```
In this example, we want to pull our email recipient, subject, and body all from our spreadsheet contents. So we can reference them with spreadsheet cell references. For example:
In this image, in row 2 my recipient is in cell C2, my email subject is in cell F2 and the email body is in cell G2.
Since this formula is inside of a computed column, the references will dynamically populate for every row in my table.
Now all that's left to do is send our emails!
In the column with your SENDGMAIL formulas, click on the 3 dot menu at the top. Then select Run column now.
Note that this will actually send all of the emails in the column when you click it! If you're not ready to send all your emails, you can send yourself a test email by right clicking any SENDGMAIL formula cell and either previewing the email or running the single action.
You can unlock advanced features including BCC and CC fields, a custom Sent From email address, Name of Sender, Reply To address and No Reply fields.
Lido's SENDGMAIL formula is very robust. All of the fields in [] are optional, and you can skip to the one you want in the formula by adding a comma (,). The formula supports cc, bcc, attachments, different sender emails, and reply to.
You can create a tracking pixel for each email with the pixel_id_cell argument in the SENDGMAIL formula. Simply choose a different cell in your spreadsheet where the tracking pixel will be outputted (e.g. H2). Then, when SENDGMAIL runs, cell H2 will automatically be populated with a tracking pixel ID.
You can then used the Lido formula =PIXELOPENED() to check the pixel to see if it has been opened. Just wrap the formula around your pixel id cell, e.g. =PIXELOPENED(H2)
If you enjoyed this google sheets mail merge article, you may also enjoy some of our other articles:
Automatically Send Email From Google Sheets
Google Sheets Mail Merge Labels
