Sending personalized bulk emails for free

When we talked about email etiquette, we agreed to use bcc for bulk emails. However, what if you have a fundraiser and want to email your contacts for contributions? Or, you run a small business and want to email your contacts about a new product or sale? Christmas is next week and you’re about to send that mass text to all your contacts? Such emails have a higher response rate when they are personalized. Today, I’ll show you how to send personalized bulk emails for free. However, please do not abuse this hack and message multiple lovers. January is almost here and I don’t think you have the facilities for that, big man.

Mail Merge

The technical term for sending personalized bulk emails from some list or database is mail merge. Save this for a lull in conversation at the next party. You won’t be invited again, but at least you know things. In order to follow along, you will need a Google account, a web browser and an internet connection.

Prepare the spreadsheet

1. Follow this link to this spreadsheet created by Google developers and create a copy of the sheet for your personal use. Once you click Make a copy, the document below opens:

Recipient and Email Sent columns should not be deleted

2. Update the Recipient column with the emails of the contacts you want to message. The Email Sent column will update automatically with timestamps of when the emails were sent. DO NOT type anything in this column.

3. If the current format of the spreadsheet is not satisfactory, you can add, edit or delete any columns but DO NOT delete the Recipient and Email Sent columns. If you want to change the names of the Recipient and Email Sent columns, you must change the code on the back end of the spreadsheet that produces our mail merge magic: On the Toolbar in the Google Sheet, Extensions ▶︎ Apps Script.

4. Update the other columns with the relevant information of your contacts. Below, I edited the default sheet to illustrate my point. Think of each row in the spreadsheet as an individual email to be sent out.

Always start with your email as a test run

Create an email draft

In order for the mail merge to work, we need an email draft within Gmail. To create the email draft:

1. Click compose or “C”. To add data from the spreadsheet in your email, use the column headers surrounded by double curly brackets. For example: {{First Name}}. The double curly brackets merge the email draft with the spreadsheet by telling Google to peruse the email draft and replace the items in the curly brackets with the corresponding item in the spreadsheet. For example, {{First name}} will be replaced by Shem. I recommend you copy the column header from the Google sheet and paste the text between the double curly brackets in the email draft so you don’t have any problems.
2. Copy the subject line of your email draft and keep it somewhere you can find it easily. Tip 💡: Google Keep helps me with stuff like this; check it out! Or, the Windows Button + V shortcut will show you all your recent copied items.

Here is my draft, rife with wishful thinking and sage wisdom:

Wherever you see curly brackets, the mail merge will replace them with corresponding entries in the individual rows

Send emails

To send your email(s), do the following:

1. In the toolbar at the top of the spreadsheet, click Mail Merge ▶︎ Send Emails. You might need to refresh the page for the Mail Merge menu to appear.
2. Complete the authorization steps. Some of the permission requests look scary but rest assured this sheet was created by Google Developers, so using it won’t be any more harmful than using Google daily.

Yes to everything!

3. Once the script is authorized, click Mail Merge ▶︎ Send Emails again.

4. Paste the email template’s subject line that we copied earlier and click Ok.

My pasted subject line looks like this. Notice my double curry brackets to capture the first name in the sheet

After clicking Ok, the script will run and the email(s) will be sent. I recommend you always test the mail merge by sending the first email to yourself. This will ensure you don’t include any typos and other errors. My test email is shown below. I am ready to bulk email this, though reading it back now, it reads like an email scam:

Texts surrounded by curly brackets in the draft have been replaced by the corresponding entries in the spreadsheet

Important things to know

1. You can anywhere between 400 – 1,500 emails a day using mail merge. Though, if you’re consistently emailing over 400 people daily, you may need to invest in an actual bulk email software. Cheap things will cost you.
2. Rename the Google Sheet and save it on your Drive for future use. In order to resend emails, delete the timestamps in the Email Sent column, refresh, rinse and repeat.
3. Always use double curly brackets for the placeholders with no spaces. 99% of the time, the mail merge doesn’t work well because of some error in the way the curly brackets are used.
4. You can add attachments to the email draft and have them sent to all contacts
5. You can add additional options to the email, such as the name of the account the email is sent from, reply-to email addresses, as well as Bcc and Cc email addresses, by editing the source code. Visit Extensions ▶︎ Apps Script and review the code. While the code may look intimidating, the developer annotated it well. You can follow along quite easily and make your desired changes.
6. This mail merge sheet was created by Martin Hawksey, Learning Design and Technology Lead at Edinburgh Futures Institute, blogger, and Google Developer Expert
7. Always send test emails to yourself before bulk messaging

Conclusion

Mail merge allows you to send at most 100 personalized bulk emails a day without typing each email manually or struggling with cc or bcc. If this isn’t a Christmas miracle, I don’t know what is.

Have a blessed week, be kind to people and Merry Christmas!