Sending emails via Mail Merge was easier at my previous school where Outlook was the default mailing service. But we use Gmail at my current school so I have had to install a unique account for just mail merge on Outlook. This has been hit or miss given our mail server settings and over time, has been inconsistent. I have tried turning to services like YAMM (Yet Another Mail Merge) but the challenge was making the recipients my contacts which was not always possible. Often times the recipients are parents who do not have a school account so importing them into the contacts can be tricky. Plus, to send a quick message with basic information it seemed like a lot of work to install and run the add on. We do use services like SendGrid but mainly for all school mass emailing that doesn’t require any personalized element.
Given my natural affection towards writing my own solutions I have used good old Google scripts to read data from a Sheet and fire off emails based on content that is built into the script. Now, it may not look as visually appealing since the formatting takes some effort. But if I want to send off a set of emails to kids and their parents letting them all know of their grade data or of some event that requires them to be in groups etc, then this approach is my preferred one. Maybe you already have a system in place for this and if so please treat this blog post as a way to learn about Google Scripts!
Instructions
Step 1: Get your Google Sheet prepared with basic recipient information. You can add whatever unique information you want here. This is just a sample.
Step 2: Now, we need to write the code which sends off the emails. So click on Tools >> Script Editor for the Sheet.
Step 3: This will open up your Script Editor where you will type in this code needed to send off the emails.
function sendEmails()
{
//Open the spreadsheet to get the data.
var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");
//Now load all the data in that sheet into a 2D array (matrix)
var data = sheet.getDataRange().getValues();
//Now make a loop that will go through this data array ONE ROW AT A TIME
//This line means start at index 1 (since 0 is the column header we dont care about)
//Loop all the way to the end of the data
//Increment by 1 row each time
for(loop = 1; loop<data.length; loop++)
{
var message = ""; //blank message which will hold fresh data for each row/student when emails go off.
var studentName = data[loop][0]; //will load the name "Noemi Blevins" since it is in 2nd row and 1st column.
var team = data[loop][3]; //will load the TEAM name for each row into this space.
var dueDate = data[loop][4]; //will load the unique due date for this student's team.
var studentMail = data[loop][1]; //will load student email for later email sending
var pEmail = data[loop][2]; //will load parent email for later email sending
//Now it is time to create your unique message for the student/parents.
message = "Hi " + studentName + "! For the Mission to Mars Robotics project you have been put in Team " + team + " and your due date for the submission"
+ " is - " + dueDate + ". All the best and please use the class space to read updates on your team's tasks!";
//This next line sends an email with the message above to the student and CC to the parent.
MailApp.sendEmail(studentMail, "Mission to Mars Information", message, {
htmlBody: message,
cc: pEmail,
});
}//end of loop that has scanned all data entries in the sheet
Browser.msgBox("All Emails Sent!"); //displays message on browser.
}//End of program
Step 4: Once done, click on the PLAY button as shown below. You will be asked to authorize the app script since it sends off emails. Please go ahead and allow it for the script to work. Since it is a custom script nothing will go wrong.
Step 5: Once it is done the script will end with no errors. This means emails were sent as expected. Given below is an example email to show you what was sent.
Note:
- GMail’s daily quota of emails sent is limited (500 per account, I think). So this method is useful for a list that is less than that. Else it is batched and sent out the next day.
- This program uses a loop. Loops in programming are notorious for getting out of control. I would test the feature multiple times (with your own email address in the student and parent email columns) to be a 100% sure you got the value indexes right.
- If you are keen to improve your programming skills then this is a good exercise to start with since:
- It introduces you to the concept of programming a Google sheet.
- It lets you get used to the interface and sending emails.
- It forces you to pay attention to good documentation (within the code itself).
Conclusion
At the end of the day Outlook or YAMM may still be the best way to send out Mail Merge. But hopefully this post has engaged you enough to play around with the basics of Google Scripting. I have worked with it extensively for almost 5 years and have learnt that there is very little you can’t do with it. In a later post I will share how dynamic charts can be sent out to recipients based on their own data!
-Shashi
Compute Thought 