Here's a new tip for Google Sheets, allowing you to quickly send predefined emails from a spreadsheet. This little feature will save you time and productivity, since it will save you from having to type your emails manually, especially when they are recurring and boring emails linked to your managerial or operational activity.
Personally, I use it to inform my colleagues that the schedule for the week or month is ready. Planning that I share with them in a fragmented way, as I explain in this tutorial. Once the schedule is finished, I invite my partners to consult it and confirm their presence directly from Google Sheets, without having to multiply the tasks and means of information.
Here is the relatively simple procedure to follow:
Create your newsletter
Open a new Google Sheets document, then enter the email addresses of your future recipients in the first column. Then, in front of each recipient, type the message you would like to send to each:
Create and run the email sending script
Click “Tools > Script Editor” and copy and paste the following script:
function sendEmails() { var sheet = SpreadsheetApp.getActiveSheet(); var startRow = 2; // Type the first row to process var numRows = 2; // Type here the total number of rows to process var dataRange = sheet.getRange(startRow, 1, numRows, 2) // Fetch values for each row in the Range. var data = dataRange.getValues(); for (i in data) { var row = data[i]; var emailAddress = row[0]; // 0 if the emails are in the first column var message = row[1]; // 1 if the message is in the second column var subject = "Subject of your email"; // Type the subject of the email you are going to send MailApp.sendEmail(emailAddress, subject, message); } }
Save the script. When you need to notify the recipients of your email, all you have to do is click on “Script Editor”, then select “SendEmails” from the list of functions (especially if you have other functions in your editor ), then click on the “run” button (shaped like a play button).
Google will ask you to authorize the function, then the emails will be sent to the recipients set up in your table.
In the case of a schedule: be notified of confirmation of recipients
If, like me, you want to use this script to send a schedule to your colleagues, you can also invite them to type “OK” in their schedule so that they confirm it. And for you to be notified of their confirmation, you must go to each person's schedules, and click on “Tools > Notification rules”.
I advise you to check the box “be informed of changes that are made”, as well as “E-mail (daily summary)”, in order to have an email encompassing several confirmations and not an email each time someone confirms the planning.
Source : Google Developers.
EN 
FR 
ES 
AR 
ZH 
IT 
DE
7 Responses
Hello, thank you for this script.
I'm looking for something in particular, and I can't find it: can you help me?
In fact, with the same principle, I would like to send a list of emails from sheet, but specifying a sending date (which can be different for each email), with a recurrence (every month for example). Is it possible ?
Thank you Best regards.
Good morning ! great script thank you! Is it possible for the script to detect the number of lines that contain an e-mail? (to avoid getting an error message if there are empty lines)
Good morning,
I get this error:
Invalid email: test (line 13, file “Code”)
How can I do?
THANKS!
Good morning
Following your explanation, I would like to send the complete file (Leave request file).
This file would be distributed to 4 people: 3 people + the person who created the request.
would it be possible to have the code to send the complete file
Thanks for your help
I would like to create an automatic reminder message for the reporting of certain data from
different worksheets in one workbook
to different people addresses
different monthly ok
example
Please let me know the hours address 1
Please let me know the mileage address 2
Please let me know the water consumption address 3
Hello, thank you for your great work!
Is it possible to keep a certain layout in the messages (new line, color, bold etc.) or should I settle for simple text?
Thank you very much in advance for your response,
Yours sincerely,
Louis JAubert
Hello Louis
I figured out how to make line breaks but still need to know how to do the rest of the layout. For example, the text is like squeezed into a non-visible column, it does not span the width of the gmail message page.
for a line break insert the command \n\n between apostrophes
here is my command line
var message = Hello + '\n\n' +message1 + '\n\n' + courtesy + '\n\n'+ signature;