Simple Bulk Emailing from Google Sheets

• Caveats
• Credits
• Instructions
  • Testing
  • Emailing
  • Customise

Send better looking emails to a bulk set of email addresses, straight from Google Sheets.

If you need to send small batches¹ of ‘bulk’ emails, then this spreadsheet provides all the code you need to make it happen! The email address list is stored in the sheet, and you can customise the email itself by writing simple Markdown. You can even send tabular data straight from your sheet, which will be re-formatted as HTML.

Caveats

Designed as a learning exercise, this shouldn’t be considered as a production ready system. It is simple, and easy to tweak for your own needs, but you should probably only jump into it if you are comfortable with concepts such as HTML and scripting. It won’t send bulk emails by default until you tweak the configuration to put it into live mode. Any bulk-mailing code should be considered dangerous to play around with unless you know what you are doing!

There are also limits on the quantity of emails you can send from your Gmail account, so you should have a look at these first to ensure you’re not likely to exceed them.

Credits

• This builds upon the excellent Cerberus responsive email patterns to create a simple HTML email template for sending. If you want to refine the design of the template, have a look at the project site first to see what is possible.
• The code behing this spreadsheet (Tools -> Script Editor …) includes the versatile showdown parser. This converts Markdown to HTML, and is freely available under an open-source license. The code itself is copyright (c) 2007, John Fraser and all rights have been reserved.
• If you wish to know more about some of the techniques used in this script, you can read about how to create menus and use configuration.

Instructions

Testing

Firstly, you need to author your email. A straightforward example has been provided, but you can alter this straightaway. The content of the message can be found in the MESSAGE tab. The tabular data comes from TABLE. If you delete all the cell values, the table will disappear from your output email. If you need a larger table, adjust the size of the TABLE named range. The message content can be authored in plain text, or Markdown if you would prefer more precise control over how will look to your recipients.

Once you have authored your message, you can use the Email menu to send yourself a test message by clicking Send Test Email. If it is the first time you have run the script, you’ll be prompted to authorise the script to access your email account (required to send the emails). It’s good security practice to have a look at the script first, to make sure you’re happy with what it will be doing. The critical parts are in the Code.gs file, and any command that interacts with your email will be prefixed with GmailApp. This allows you to quickly find the important lines and review their intended actions.

Having sent yourself a test email, you can review how it looks and make any customisations to the details, colours and fonts.

Emailing

The email list will initially contain randomly generated test data). The list of email address to send to is column F in the LIST tab. This is referenced as a named range called EMAILS for easier reference. A simple parser is also included to help generate an email list from a TO or BCC string containing many email addresses. You don’t have to use this; it’s just there to help you to migrate from a standard BCC emailing without having to re-type all the addresses in again. If you have a string containing all your addresses in this format, paste them in cell E2 on the parser sheet to extract the email addresses from them (there is an example in there to help you).

When you are happy with your email, and your list, you can set the various configuration options explained in the CONFIG tab. Remember to send a final email to yourself before switching the LIVE configuration value to TRUE. Without doing this, the script will not send any emails other than the test one.

Having sent emails, they will appear in your sent items, so you can verify that they have been correctly sent. Any bounces or replies will come straight to you.

Customise

If you would like to change fonts or colours, you can do so on the CONFIG tab. You can also add logo images and an email signature. If you wish to alter the layout or perform more advanced customisation to the email template itself, edit the Email.html file in the script editor. Make sure you test it properly before sending out any live emails (you may wish to change the LIVE config mode back to FALSE while you do this, just in case).