Mail merge can be done with Google Drive and I’m going to show you how. Some of you out there might be thinking Patrick – what the hell is a mail merge? Well my friendly readers, a mail merge allows you to send out a form letter to a large number of people, but the letter is personalized using a database or spreadsheet populated with info. Don’t worry – it will make sense soon.
For this post, I will start from scratch and take you through each step. So why would you want to mail merge? In this example we have an overnight trip and this will tell the parents what teacher is responsible for their child, what activities their child has signed up for and who their roommate is. This is definitely info that parents and students will want, so instead of writing a bunch of individual emails – I will mail merge it!
Step 1 – Set up that spreadsheet
The first thing we need to do is create a Google Spreadsheet and fill it full of information. A good way to do this is to use a Google Form but for this instance, I’ve already got it populated. Check it out below.
Get that Add-on
OK, now let’s get to the magic. We are going to activate an Add-on and this will do all the heavy lifting for us. To do this click on the Add-on menu option at the top – then select Get add-ons…
Now we need to search for formMule and then install it. As you can see from my image I already have it. When you install it (you only need to do this once), it will ask for all sorts of permissions. Go ahead and give it what it wants.
Step 3 – Set up the mail merge
Now we are ready for the magic. Let’s set up the email. Take a look at the image below to access the formMule settings.
Ahh-I love technology. This add-on can let you set up automatic triggers that work with new submissions (like a Google Form would produce), but for this example we will be doing everything manually, so leave the triggers off and then make sure that proper worksheet is selected. I’ve renamed my worksheet as Trip Data. Then click on Next: Templates and send conditions.
Now we have to determine how many different templates we will use with this data. Since, I’m only sending out one email I will do only one template and then I’ll click Save template settings this will update that window and then I will click on Next: Edit templates.
Step 4 – Editing the template
This step is pretty important which is why I’ve made it a separate step, but don’t worry it’s not all that hard. Check out the image below and let me make some sense of it for you.
As you can see it looks like an email and that’s how we will treat it but the merge tags on the right hand side is where the cool stuff comes into play. For the To: area I want it to put in the parent email. So I click in that field and then select the parent email from the Add merge tags section. Check out the GIF below.
That will tell formMule to send an email to every email address in the parent email field. Now give it a subject and then type the email and add merge fields where necessary. This service is pretty good, but for some reason, when you add a merge tag it will add some unnecessary text in front of it. Check out the GIF below to see what I mean.
So here is what my final email looks like. Notice, that I removed all extra info from the merge tags (like seen above). So that is really the hardest part.
Now all we need to do is preview it and then send them out. To preview them, click on the Preview this button. It will show each email and you can cycle through to make sure the information matches up correctly. If it looks good, go ahead and click on Preview and send all.
It will give you a quick preview, let you know how much of your daily quote you’ve got left. According to this screenshot it looks like you get 1500 per DAY. Yep – if you as an educator are sending out more than 1500 emails per day you are certainly in the wrong line of work.
Now click Send now. It will send out all of the emails at once. In this case it will send out six emails. On your spreadsheet, formMule will add a column and give you a quick status.
Pretty simple! Now imagine you are in charge of a field trip with 100 or more students. This is definitely a huge time saver. I like to use Google Forms to collect this data. That way, it is the parents (or students) filling out the information and if they do it incorrectly, it is on them which helps take some pressure off your shoulders. Here is what the email looks like for me.
Some issues
Now formMule is pretty sweet, but there are some issues here. One, it will tell you it sent out but there is no follow up report. If it fails, it will email you about that, but unlike the MailChimp add-on it won’t tell you if it was ever opened or not or how many times it was opened.
Another issue I have is that if I want to resend this out. I have to delete the Template 1-Send Status column. Not erase, but remove the column completely. I may want to send a final confirmation out before the day of the trip. It’s not a bad idea, but I first have to delete that column and then send it out again. It’s not hard but just an extra step.
Finally, adding the merge tags adds that extra text in front of the tag. That is very annoying.
It’s not perfect but it works and it works well and it’s free and it’s reliable, so overall there isn’t all that much to complain about. If you want to read more about it check out their website.
Technology in the Classroom 