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 Nestor, 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 could 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 of 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 in 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 the 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 could 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 could 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 for 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 the 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 okay. 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 it 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 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 comments 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 1,500 per day. So there are some limits in place, so you can 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. All right, 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. All right, well, that’s all I had for you today. I hope you enjoyed, and I hope to see you next time. Bye.