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.