Search
  • Caden Armstrong

Send Emails From Google Sheets

You’ve made a complex spreadsheet to automate a good portion of your business. Great! But you still have the mundane task of sending the same emails over and over again. The good news is that you can send emails directly from a Google Sheet, automating yet another portion of your work. Sending emails is actually quite easy, so I’ll go through it step by step.

A couple reasons you might want to send emails from a good sheet include:


Automated Report Generation - Share reports with the team as they are created.


Reminders - Managing projects in google sheets, send reminders for deadlines and overdue items, send notifications when items are completed.


Invoice Automation - You’ve made the invoice in Google Sheets, skip a step and have it send automatically too! No more sending the wrong invoice to the wrong person.


How to send emails from Google Sheets

In your spreadsheet, go to tools > script editor.

Create a new google script

Google will now open a new Scripts project for you, with a blank function.

The blank function can be left in or removed.

Blank Script Project

Next, we are going to create a send email function.

Now, this function is a bit silly as it only has one line, but it is setting us up for later,


Basic Send Email Function

Our next step is that we need to check if email quota remains. Google limits gmail users to 100 emails per day, and G Suite users to 1,500 emails per day. We can check this and alert the user when no quota remains.


Send Email with Quota Check

Now we need to actually get the email addresses, subjects, and messages.

Assuming you already have a sheet that can generate this information, we will start by loading the data from this range.


To actually get the range, we have two options: specify the range in code, or use the active range. The active range is the currently highlighted set of cells in our sheet.

If the range never changes, specifying the range manually will yield better consistency, but using the active range gives a bit more flexibility.

Get Email Info from Spreadsheet

Next we get the data for each email. We are making the assumption here that 3 columns have been selected, and that the order is email, subject, message. With this data, we will use our send function.


Get email data values and send email

Now, save the script, select our getEmailInfoAndSendEmail function, and hit run.


Run the script

Your recipients should soon be greeted with their emails.


Wait for emails to send

What else can you do with Emails from Google Sheets?


Add a menu to launch our email script

Its a bit annoying to have to open the scripts page to run our script. Adding the following function onOpen will add a menu shortcut under Add-ons when the sheet is loaded.

File Attachments

You can attach files from the Google Drive to your emails. Just remember to change the MimeType.PDF if you are sending something other than a PDF.

Send an email with an attachment

Add a CC recipient


Send an email with a CC

Change the Reply To address

Send email with reply to address

To get a complete list of options for using the MailApp, checkout the google documentation: https://developers.google.com/apps-script/reference/mail


If you have any questions about automation or software development, feel free to reach out to me via email at caden.armstrong@smartbenchsoftwaree.com

0 views
  • Facebook
  • YouTube

©2019 by SmartBench Software.