How to Send Customized Bulk Emails with Mail Merge in Google Sheets and Gmail

Welcome to Teacher's Tech my name is Jamie and it's  great to have you here. Today I want to show you   how you can save time by using mail  merge so I'm going to show you how you can use   mail merge in Google Sheets and Gmail for free so  take a look at my Google Sheets right here I have   in my menu mail merge I'm going to show you how  you can add that so when you just go ahead and   run this script it will send out all your emails  with a customized email to it so let's get started   on how to mail merge with Google Sheets in gmail  on teachers Tech question I was at a school the   other day when somebody asked me if I knew how to  do a mail merge using Gmail and they were looking   for a free option so then I was doing a little  search and I looked at other ones like autocrap   before but then I came across this page so this is  the Google app script and I liked it because they   trusted this because it was coming from Google so  I'm going to put a link to this uh resource Down   Below in the description because once you click  on the link and scroll down a bit you're going

to see where you can make a copy of the Google  Google Sheets that I was showing in the intro so   just go ahead and click on make a copy and then  you can view the script if you want but I'm just   going to go ahead and hit make a copy here because  this is going to bring me to the first step that   I need to have done so this opens up the Google  Sheets here with the different column or fields   and then what's going to happen is it's going to  have mail emerge in it with what the script was   written so make sure you get that open up then you  can follow along with me so this is where we're   going to put all the different emails in here in  the names in the description and we can go ahead

and edit these columns well most of them anyway  two different ones that we cannot edit we need   to make sure we have the recipient one because  this is where you're going to collect the emails   to send it to and we need this one over here but  the other ones we can modify and we're going to   do a little modifying on this test here so in this  I am going to leave the first name last name the   same here so but then what I want to change first  is going to be if I go over to uh discount code I   don't want this to be discount code anymore I  actually want this one to say Quantity so I'm   just going to put quantity in here just like this  and just click off of it and the other thing I   want this one isn't going to be discount amount  it's just going to be a mount like so I do need   another uh column here and it because I want  to be able to total up so I'm going to have a   little equation in here that I can do and it will  reflect in the email that I send out so I'm going   to just right click here and insert one column  to the right and I'm going to put in total here   and actually I'm going to add one more because I  want to have a due date in here also so I'm going   to right click and insert one column to the right  again and this is going to be due date so you can   see I'm making some changes here to my different  columns so these are going to be the different   fields that I'm going to be able to put into  my email when I craft it across the top here so   the other thing is let's just go ahead and delete  this in so if you were putting you could put more   people in I'm just going to do a demo of one here  and it's always good to just to start with one to   make sure everything's working read the email  if you need to make any changes to it so let's   go ahead and feel fill out some data here just  for some examples you can change the email that   you put in so you get the email on it but I'm  just going to fill in some of this information   so I filled in some information you can see just  for an example here I haven't filled this one in   yet so I can add a formula here so I'm just going  to go ahead and click in here put my equal sign in   and it's going to be it even suggests the right  one if you don't know much about Google Sheets   I'll put a link to my Google Sheets tutorial  here too so you can get more familiar with   that so check Below in the description up or up in  the card I'm selecting this so we have 46 dollars   that's correct in this email sent we don't put  anything because when we run the script it will   actually go through and tell me when it was sent  in here and we'll stamp it through here okay so   we have our different fields across the top now  we're ready to craft our email so I'm going to

go ahead and I'm just going to go over to my Gmail  I already have one uh ready to go and we'll start   be we'll start putting in the information that we  need to be able to send our bulk email I just   want to point out too with this Google sheet you  can change the name of this so if you don't want   it to be called copy go ahead and change that the  other thing is to you can go ahead and move this   to a different location so if you do want it to go  into a different folder in your Google Drive just   go ahead and make one and just put it where you  would like okay so what I'm going to do I'm going   to work side by side here so I have on the left  I have my Google Sheets and then I have a Gmail   a message new message to open so the first step  is going to be this recipients here who is that   well this is where we need to be using the curly  brackets or curly braces depending on what you   call them I'm just going to refer to them as the  curly braces if you hear me talking about these I   need to start with two curly braces here what do  I need to put in here it's going to be recipient   here I could type it in into here  or I could just go back over here Ctrl C and go   back over here and Ctrl V and I need to close the  bracket here so I'm just going to go ahead and put   that two more curly braces on so it knows when I  send out my message it's going to go down these   are all the emails that it would send to I  only have one in there so it's only going   to send to the one so the next thing is going  to be the subject and we're going to use a few   more descriptors in here so I you could start  typing regularly so if I was just going to say   upcoming fees for and what is it going to be well  it's going to be the uh I want them to know that   the quantity and the description so if I have  quite the quantity first I need to start with   my curly braces again here and if I look over  I have quantity right here again I could copy   paste it or I could type it so I'm just going to  go Ctrl C and Ctrl V and put my curly braces in   again and then I'm going to go ahead and give  it a space because I want a space between the   number and the description so what's going to be  what's it going to be quantity of what well it's   going to be descriptions so again I'm going to  put my curly brackets or braces there and go over   here Ctrl C and just Ctrl V here and end my braces  here to close it up so now I have my recipient and   my in my subject line I can go ahead and craft  my email now so you're usually going to be saying   hello to somebody and this is where using a mail  merge is uh works so much easier and saves you so   much time if I say hello well who do I want to say  hello to well say hello to first name   last name I could do that I could just do uh first  name if I go all the way over here so what do I   want to say hello to well let's do uh well let's  do our braces first here and we'll just type so   like I'm going to type in first name like this and  just end my bracket so it's good would say hello   in this case it's going to say hello hairy because  it's going to take what I have in the spreadsheet   and replace where I have all these fields putting  in so I would go from there I can put a comma if I   wanted to have last name in I could do this but  just remember to have a space in between first   name and last name then I can go ahead and craft  an email here so I have this little email here you   recently indicated that you would like to purchase  and I have quantity I have a space between here I   have description so it's going to pull this from  the Google Sheets for a total of and this is   where it's going to pull from Total here so this  part right here so it's going to look for a total   please submit payment by due date and I have due  date it's going to pull from here so people could   have different quantity different due dates on it  it's going to pull from each row in here so now I   can end my email I can just say thank you and now  at this point we're ready to kind of get started   for sending but I don't send it from here what I  need to do is make sure I have this subject

because I'll need to be using it to find this  email so I'm just going to go ahead and select   this and copy this Ctrl C and I'm going to go over  to this and just make this larger and I'm going to   go ahead and choose mail merge now so when you  go to mail merge for the first time it's going   to run the script and you have to authorize this  so I'm going to hit continue and like I said since   it was coming from Google I didn't mind where it  was uh where why I was using it I'm going to hit   my account I'm going to use and then I have to  make sure I allow this if you want it to work   and then at that point I've gone through all those  steps so I haven't sent anything yet now I have it   allowing to connect with my account if I go back  to mail merge I can click on send emails this is   where I put the subject line so I'm just going  to paste this uh so this is make sure if you're   typing this out you have the correct spaces  because that's it would identify if I had Extra   Spaces it won't identify it so I just use the  copy and paste so now I'm going to hit OK and it's   running the script and it's finished the script  let's go over to this email that I have to see   what we got so here's the email that I received  here in my account and I can show you a mistake   and so this is why it's always good to just send  one as a test and notice it just says hello and   there's a comma let's look for other things it  seems like the two school yearbooks this is all   correct you recently indicated that you like to  purchase two school yearbooks for a total of 46   dollars please sub it your payment this all seems  correct so what went wrong with this hello well I   have first name but I want to show you something  here so I'm going to go back to the Google Sheets   here so with it open look at this right here  last name and uh or sorry first name and we look   over here first name what's the difference well  this does not have this is lowercase and I put   an uppercase so copying pasting I didn't copy  and paste this one so just so you know I made   that mistake so you could see that so you would  need to make sure that you correct this remember   this email that you are creating here this just  saves as a draft on it so I've made the changes   to this if I go ahead and just maximize this so  we can see this again notice it said the email   was sent here this is what happens after I run  the script if I'm going to resend it I'm going   to actually delete this here and now let's let's  go back again to mail merge and send that email   again we need to make sure we put our description  in here so copy paste it over our subject line   I hit OK and it's going to run this and it's  finished let's check that email one more time   so now I have the email open here and you can see  it says hello Harry because I made that adjustment   in the email draft so I just wanted to point out  make sure that you have the different fields named   correctly spaces and upper lowercase do matter  with this all right I just want to show you one   more thing before we end this video here today I  just want to emphasize make sure you don't delete   the draft of the email that you created because  you do need that so make sure that stays in your   drafts now I'm just going to pop it back over to  this right here the original site that I showed

you on and I just want to point out something  for you to check out and that's going to be   on the quotas for the Google services here so  uh just check this depending on what size of   business or how many emails and everything you're  sending just check the limitations and everything   if this will match what you need it to do so I  hope you like this walkthrough today of how to   use Google Sheets and Gmail for mail merge for  free I found this like for myself it just that   worked very easily and worked well but check  to see if those quotas will work for you thanks   for watching this time on teachers Tech I'll see  you next time with more Tech tips and tutorials