✉ How to Mail Merge in Gmail & Google Sheets (for free)

hi everyone kevin here today i want to show you how you can mail merge using gmail together with google sheets and this is entirely free now first off what does mail merge even mean well with mail merge you can send out customized bulk email messages so just to use a real example at the kevin cookie company i have 50 employees and i want to send them all a holiday greeting now nothing shows you care like sending out customized emails to each employee so i could say things like hi nester i hope you have a happy holiday now i could go through and i can manually compose each one of those emails but that would take a long time and i don't care quite that much instead i could let the computer do the heavy lifting for me and let the computer customize all of those messages on my behalf now another way to think of it is it's like the electric bill you get in the mail every single month your electric company is using a very advanced or fancy mail merge today we're going to see how we could do that for your own personal or maybe small business emails all right let's jump on the pc and let's see how can we do this as a first step open up a web browser and head to the following website i've also included this link in the description of this video so you can simply click on that to navigate to this site this website includes instructions on how you can run a mail merge using gmail and google sheets but don't worry you don't have to read it i'll walk you through step by step how you can pull this off before we really jump into it though i do want to give thanks to martin hoxie he pulled together this script that allows us to run the mail merge thanks a lot martin if we scroll down about halfway down the page we see a gmail sheets mail merge spreadsheet we're going to use this spreadsheet to run the mail merge and this has all of the logic and the script within let's click on this this opens up a prompt asking if we want to copy the document over into our drive account let's click on make a copy this opens up google sheets and here we see a whole bunch of sample data populated in the sheet in a moment we'll come through and we'll modify some of this before we do that though one item i want to call out you can place this spreadsheet wherever you want in your drive account if you click up here on the folder you can place it in a different location now right now i'm satisfied leaving it in the main folder so i'll leave it there but you can move it if you'd like to next let's shift our attention down to the spreadsheet below and here we see a whole bunch of different column headings and we see some sample data the way to think of this is each row within the spreadsheet represents one email that we are going to send out and right now we see a whole bunch of sample data for now i'm going to delete the sample data and next we have all of these different column headings the way to think of a column heading is this is a field that you can insert into your email so let's say in my email i want to say hi and then the first name i want to include this column and i'll pull the name from this column now in my mail merge today i'm not going to use all of these different columns some of these are just here as examples so for instance in my mail merge i don't want to include a last name so i'm simply going to delete this column you can go through and you could add columns and you could also delete columns here if i right click i can also insert additional columns you can also go through and change the names of some of these columns in my mail merge i'm not going to have a discount code so i'll simply change this to due date for this next column i'm going to change this to price so you can go through and modify the names to represent the different fields that you want to insert into your email now there are two columns though that please do not modify this first one here is the recipient column and this is for the email address we're doing a mail merge so you need an email address and the script specifically looks for this column so make sure you have a column called recipient that contains all of the different email addresses also there's another column here at the end called email sent and this isn't a field that you're going to use in the email instead when the mail merge goes through and sends out the email if it's successful you'll see a note here letting you know that it was successful and it'll show you the date and time that the email went out so there are two columns the recipient column and the email sent column that you have to make sure that you leave in place now that we've talked about the column headers i want to include just some sample data so we have someone to send our mail merge to in today's example i want to use the mail merge to send out invoices for the kevin cookie company so here in column a i'll put down the customer's name in this case it's going to be a test so i'm going to send this invoice to me kevin also next to that i need to put down an email address so i'll put the email address in the recipient column next i put down a description a due date and also the price once again i'll leave email sent blank because this is where i'll see the confirmation that the email went out successfully now that we've prepared this spreadsheet next open up your gmail account and open up a new message now we're going to start pulling together the email message that'll take in the different fields from the mail merge up above we need to indicate who we want to send this to and once again in the sheet we want to insert the recipient in the to field so up here in the to field insert two curly brackets i'll insert two curly brackets and if we go back to the spreadsheet here i want to insert the recipient a best practice is simply go back to the spreadsheet copy recipient from here go back to your email and then you can paste it in then we're going to close it with two curly brackets so what gmail will do is it'll go through the sheet row by row and it'll insert the recipient in here next we can also customize the subject for the subject i can do the exact same thing and i mentioned that i want to send out an invoice so i'm going to start typing in some text i've typed in your bill 4 and now i want to describe what the bill is for and i want to insert the description so here i'll go back to the sheet and i'll copy the description header let me go back to my email and i'll insert two curly brackets insert description and then close my curly brackets so here it'll insert whatever the description is next i want to continue typing in my subject i now inserted is due on and i want to insert the due date so i'll go back to my spreadsheet here i'll copy due date and next i'm going to insert this in curly brackets and then i'll close the curly brackets so now i'm able to have a completely customized subject for my email i went through and i typed some additional text for my email bill that i am going to be sending out and here again i am also inserting different custom fields from my spreadsheet here for instance i'm saying hi and then i insert the first name i have some generic text at the beginning and then here once again i say your bill for the description is due on this date and then i also say submit a payment for this price so i'm using all of these different fields from my spreadsheet and i'm inserting them into my email now my email is all good and it's ready to go but we're not going to do anything with the email yet instead before we go back to the spreadsheet let's take the subject and let's copy this we're going to use this in a moment when we kick off the mail merge because we need to tell the mail merge which email draft we want to use and the subject will help us identify the draft email so let's copy this and then let's go back to google sheets back within google sheets we now want to kick off our mail merge our email is all ready to go we have a bunch of data in our spreadsheet so to kick it off at the top of google sheets there's an option now called mail merge when we click on that we see an option to send emails let's click on this next we see a prompt telling us that it needs authorization this spreadsheet has a script associated with it and we have to give authorization to be able to run this script let's click on continue next this opens up a sign in prompt and we have to indicate the account that we want to use to send out the emails i want to use this account so i'll click on that next we see a notification that this app isn't verified however this app is provided by google so i think you should be fairly certain that it's a trustworthy app also in a moment we'll go in and we can actually look at the source code of the script and everything is legit in there so let's click on advanced and down below we can continue on with this mail merge next i see a screen asking me for access and once again this is a legit script so let's go ahead and click on allow now that i've given it authorization i'm ready to run my mail merge once again we have to go up to mail merge on top and let's click on send emails again it's now running the script and it shows a prompt telling us to type in the subject line of the gmail draft message that we would like to mail merge with once again we copied this subject so you can simply paste that in here this helps google sheets identify which email it's going to do the merge with so once you paste that in it'll know the exact draft message once you paste it in let's click on ok here now the script has finished and here within the email sent column i see a confirmation that the email was successfully sent so i see the date and the time back within my gmail inbox i now see two new messages first i see a security alert just letting me know that i granted access to this script to run my mail merge next here i see my custom email that arrived let's click into this to see how it worked here within the message i can see that the mail merge successfully sent it to my email address i could also see that i was able to successfully customize the subject and here down below i could look at the email message and it inserted all of the different information from my spreadsheet so worked really well and it was actually pretty easy to pull this off back within google sheets now that i confirm that the email sent out properly and that all the fields inserted into the email correctly i can go through now and i can add more rows to my spreadsheet and once i add all of them once again i can go up to mail merge and send out the emails now it's a very good practice to just use a sample email first just to ensure that everything is working properly here you can add thousands of rows to your spreadsheet and then you can send out your mail merge before we wrap up i do want to show some advanced capabilities of the mail merge and the mail merge is just a script that's running on this google sheet to be able to see the script and to use some of the advanced features let's go up to tools on top and then let's go down to this option called script editor i know it might sound a little bit scary but we'll walk through it and i'll show you what you can do with this within the script editor we can see exactly how martin hoxie pulled together this script and all the logic behind the mail merge when you authorize the mail merge you're really just authorizing that this script can run and there's nothing malicious within the script so once again it's completely legit to run this if we scroll down about halfway there's some interesting functionality that we can take advantage of once again about halfway down here's an option for bcc so let's say you're sending out your mail merge and you want to bcc someone you can remove these forward slashes that basically indicates that this is a comment and when you remove it it's no longer a comment here now you can type in a bcc email address also if you want to cc someone you can remove the comments or the forward slashes and then you can insert an email address in here for the cc if you have an alias set up you could also indicate a from field you can also put down the name of the sender once again remove the comment and then in this field you could type in the name of the sender you could also indicate the reply to email address or if you don't want to have replies down below there's also a section that says attachments and with this mail merge technique you can also include attachments within gmail when you're composing your message you can attach a file here and this file will be attached to any of the merge messages that you send out with this mail merge you might be wondering well how many emails can i send per day and there are some limits in place with a consumer or a free account you can send out emails to up to 100 per day with a g suite account so this is a paid account you could increase that limit up to 1500 per day so there are some limits in place so you can't send out 40 or 50 000 emails per day but if you're sending out that many emails you're probably using a more professional mail merge solution but once again for personal use or maybe an education environment or a small business this mail merge solution works extremely well and best of all it doesn't cost anything alright well that's how you can use mail merge using gmail and google sheets if you now know how to mail merge please give this video a thumbs up to see more videos like this hit that subscribe button and if you want to see me cover any other topics leave a note down below in the comments alright well that's all i had for you today i hope you enjoyed and i hope to see you next time bye [Music]

Channel: Kevin Stratvert

Views: 136,840

Rating: 4.9466372 out of 5

Keywords: kevin stratvert, mail merge, mail merge in gmail, mail merge in sheets, gmail, google sheets, sheets, google, merge, mail, email, e-mail, sheets to gmail, how to mail merge, how to use mail merge, how to do mail merge, mail merge tutorial, tutorial, mailmerge, mail merge email, how to do a mail merge, gsuite, g suite, what is mail merge, how to, help, how to mail merge in gmail, send email from sheets

Id: mpdF5PFkNN0

Length: 12min 52sec (772 seconds)

Published: Thu Dec 17 2020