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.
Google will now open a new Scripts project for you, with a blank function.
The blank function can be left in or removed.
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,
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.
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.
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.
Now, save the script, select our getEmailInfoAndSendEmail function, and hit run.
Your recipients should soon be greeted with their emails.
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.
Add a CC recipient
Change the 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