Mail Merge in Google Sheets & Gmail (for free)
HTML-код
- Опубликовано: 28 май 2024
- In this step-by-step tutorial, learn how to Mail Merge using Gmail together with Google Sheets. This solution to mail merge is free.
👋 Additional resources:
- Get Mail Merge spreadsheet: developers.google.com/gsuite/...
- Gmail Daily Recipient Limits: developers.google.com/apps-sc...
⌚ Timestamps
0:00 Introduction
1:06 Get Mail Merge spreadsheet
1:54 Choose location for spreadsheet
2:19 Prepare spreadsheet for Mail Merge
4:50 Prepare draft email for Mail Merge
7:13 Authorize script
8:18 Run Mail Merge
8:50 Review Mail Merge
9:59 Advanced capabilities, like cc and bcc
11:37 Email send limits
12:15 Wrap up
📃 Watch related playlists and videos
- Playlist with all my Google product videos: • How to use Google Prod...
- Playlist with all my Mail Merge videos: • Mail Merge
🚩 Connect with me on social:
- LinkedIn: / kevinstratvert
- Twitter: / kevstrat
- Facebook: / kevin-stratvert-101912...
🔔 Subscribe to my RUclips channel
ruclips.net/user/kevlers?...
🎬 Want to watch again? Navigate back to my RUclips channel quickly
www.kevinstratvert.com
🛍 Support me with your Amazon purchases: amzn.to/3kCP2yz
⚖ As full disclosure, I use affiliate links above. Purchasing through these links gives me a small commission to support videos on this channel -- the price to you is the same.
#gmail #sheets #MailMerge - Наука
Continue learning Google Sheets with FREE Official Training offered by Google:
✅ Google Sheets Getting Started course: bit.ly/3UwekAu
✅ Google Sheets Advanced Topics course: bit.ly/3tpxDj2
✅ Google Sheets Functions, Formulas, and Charts course: bit.ly/3UNI9vV
Hi all, a really useful tip. When drafting your email, make sure you've changed to plain text mode, otherwise the {{}} tags in the body of your email won't get read by the script and will appear blank when you hit send. Like me, if you want your email to have a fancy footer and some nice fonts, you can copy and paste your plain text email body into a new draft in normal mode. As long as the {{}} tags are in plain text the script will read them and the merge will work. It's basically taken me about a day to work this out, in which time I could have written all the emails I needed to send out by hand!
Thanks for the info, I was struggling with this as well.
Please help me .. everything is coming in one line .. I have formatted the mail but still everything is coming in one line in mail body.
@@shikhagoli Hi, I’m afraid I don’t know what’s going on there. So sorry. Does it happen when the whole message is in plain text or when you’ve switched back to normal mode? Maybe try just putting the {{}} tags in plain text.
Hey can anyone indicate why after row 8 it stops printing the time just the date any way to fix this? Please
@@shikhagoli Did you ever solve this one? Everything on my end looks great except the Signature section, which all comes on one line. Trying to get it to be vertical which is the natural format for Best regards, name, phone # and website.
Kevin, your videos by far are some of the best and most valuable tutorials. We are testing a few markets and wanted a free solution, do to being a startup and pretty much just testing options. And once again you come through with straight forward legitimate solutions that work well. KUDOS!
I have believe I have changed everything to Plain text but my script still won't run. The RECIPIENT field in the email still has the red dots under it indicating something is not working. Any other suggestions, please?
My brother came out with this problem of sending multiple mails to different members in his club with individual details. Though I had worked with Mail merge in MS Word, this was a challenge. I found the right solution here. I tried it out myself with test mails before communicating to my brother. Thanks a lot Kevin. Sivarajan, India.
Amazing Video Kev. I'm blown away. I've spent so many hours trying other methods that didn't work or had limitations. But this by far is the most cleanest and quickest method. Thanks a bunch!
Thanks a lot Kevin! This was the first video that I requested after viewing your channel! Your tips and solutions have been very useful indeed!
Hi everyone,
For those who were facing problem with the First Name not appearing in the email, try this out. In the link where you download the google sheets file you will see instructions on how to use Mail Merge. There you will find a highlighted text which says {{First Name}} . Copy that onto your email and then try. But make sure to format the font, size and background color to match the rest of your email.
I dont know the logic behind this but it worked for me.
THIS! Thanks so much for this info you are right and I don't understand why but, it finally worked!
it finally worked for me too! thank you!
OMG! This was awesome!
Hi Kevin, I was desperately searching for the Mail Merger procedure on RUclips. After encountering several long and talkative videos, I finally came across your short and precise video. It was really helpful and I was able to successfully complete my Mail Merge. Thanks a ton 😊
How did it go? Do the recipients see each other in the "To:" field? Gonna try this but Im nervous about it showing to all the recipients and them seeing eachother
@@noahthegreat190 Hi Noah! The recipient can see only his name in the 'To' field. So there is no way the particular recipient will get to see/know about the other recipients.
Thank you Kevin. This was clear and logically arranged. You have good attention to detail. And it all worked for me first try.
This still works three years later. Really great for non-profits trying to do constituent updates. Thank you!
Did you have any trouble with google blocking the script? I'm encountering that (it doesn't give me the option to allow, it just says Google blocked it for safety - i'm using a personal gmail account)
How do you have the "Mail Merge" option in Google Sheets? Mine does not have this.
@@user-tr1zt1mq7h Gabriel, just follow the video. Google sheets does not include the capability in its core. You'll need to download a _copy of that specific document,_ then allow the document to perform the incorporated script. The script actually puts the menu in the google sheet. After you authorize the script it becomes fully functional. (Security measure: 7:20) Good luck!
@@jawaadkhan5238 Huh! Maybe you need a business account?
@@jawaadkhan5238 I was able to make this work with a personal account. You have to go through a number of hoops. The directions show you how to allow the script to work. You may have to scroll down, or click the very tiny grey "Advanced" link and go through a few other hoops. It's okay because the script is shared directly from Google's support area. Just follow through all the hoops. When it's done, pull down on the Merge menu again and follow the prompts until you have sent an email. I think you're limited to 100 emails _sent_ per day as a regular non-business google account.
Thanks Kevin sir for this useful tutorial. Excellent.
Kevin, thank you very much! After watching your channel, this was the first video I asked! Your suggestions and solutions were extremely helpful!
Thank you very much for this, everyone else made it so confusing, but I followed along with you with no problem. You were clear and gave the best tutorial. Well done. Be safe.
SOLVED: Fix for missing fields in Body of Email:
1. Copy/Paste field from Sheet into Subject area of email - (adding curly bracket syntax - example: {{field_name}} )
2. Copy/paste again (from curly bracket to curly bracket) from Subject area into Body. Voila!
3. Delete field from Subject area if not needed.
4. Repeat for each field that you need in the body.
Basically, I believe we must strip the formatting from the field name when inserting it into our email body, and it seemed to work best when copy/pasting from Subject area to Body.
worked
It worked for me too? Any idea why?
Thanks, that helped
Any idea how many can i send
Thanks Kevin
Is it possible to send different attachments for different person using mail merge.
Great job, Kevin! I manage a group email list and am helping one of the group leaders personalize communication. The pre-formatted Sheet and your explanation will meet the need perfectly, thanks!
Nice one Kev. A real thumbs up there. I've actually been following and enjoying your video tutorials..
Thank you for the excellent video! Do you have video going into more depth regarding including attachments with the mail merge in Gmail?
This is very helpful. However, I am struggling to get the cc fields to work. Can you do a demo on how to trigger a column that holds email addresses for ccs or bccs? Much appreciated.
Kevin - Absolutely great tutorial. Saved me many, many hours of digging and trial-and-error. Thank you
Thank you Kevin. Your instruction were "Spot ON" and I subscribed so I can learn more!! Awesome!!
Kevin - clear and concise as always! Can you show how to add a unique attachment to each email please?
One alternative is to add a new column name Attachment and add a Drive link. In the draft mention {{Attachment}}
Thanks Kevin, the video was very easy to follow. I tested it and I received the message that my email was sent but didn't get the email with all the information. The only fields I had were recipient, student email, student password, email sent and the description. Tried it several different ways but none of them worked.
What Jesus Castellanos posted here worked.
same and I have said to call it a quit
You made this SO EASY, Kevin!!!
Thank you so much!!!
Thank you so much @Kevin. This was really helpful and a superb video. It is good that you covered small editing for the script as well as touched the limitations of the script (daily emails count). Very useful video indeed!
where did you find the script for daily emails count?
@@glenicemariano6939 I meant the "limitation to the daily email count". Google has a restriction and he points that out too.
Thanks for the turturioal I love it 🔰🔰
I love how your tutorials are easy to follow, and I'm sure I did follow your every step in this mail merge tutorial but whenever I receive the test email I created the other columns would not appear in the email. It's frustrating cause I don't know what I'm doing wrong. Nonetheless, Thank you very much Kevin!
I resolved this issue by copying my email draft and pasting it as plain text. I'm assuming my formating was causing the issue.
@@jesuscastellanos9985 Thanks! This solved my problem as well.
I also had fields retun blank which was resolved by turning everything to plain text.
I then added back in each element of formating to test the cause. I believe it was a table that I had created in docs and copied into the email. However, if I copied in a table from sheets, I was fine. Pictures, text size, bold, and highlights were non-issues.
Note that to copy the desired text and paste it as a text as "plain text"...
Windows: Right-clicking and choosing 'Paste as plain text' or using CTRL + SHIFT + V.
Mac: Right-clicking and choosing 'Paste and match style' or using CMD + SHIFT + V.
How do you change everything to plain text
Kevin,
your video provides a very good solution for Add-On, we worry about where the recipient's data goes. Now if this is a script within, it it awesome
Your tutorials are amazing. They are perfect!
This is a suggestion for a future video, and I'm putting this here because it's your latest video at the time. I have not found a good way to create a virtual water cooler in Teams. A meeting that anyone can join at any time when they've got some downtime, so we get some ad hoc social interaction, like we used to get when we would go get coffee in the office. They pop in, talk with a couple of other people for 5 minutes then move on. We would have to leave a meeting open all the time? Downsides I would like to avoid: having to schedule all day meetings on everyone's calendar. For whoever actually creates this constantly open, always available meeting, avoid having their calendar show all day meetings. Turn off notifications by default for the meeting/channel so that everyone doesn't get flooded with notifications.
I don't know if it is possible, or if it is possible, if it is just too much work or annoying, but I think everyone misses that social interaction when we're all working from home. A constantly open meeting that you can just pop in and out of for ad hoc and social conversations would be great.
Love the channel, love the content. Thanks for doing what you do.
Did you find a solution? I am trying to develop the same idea. Thanks.
You could just make a discord channel
Thank you Kevin, I'm new to mail merge. I was so afraid of it, Now I know with a little practice and your territorial I'll have it down in no time. Once again thank you. Nancy Berger
Thanks for sharing, Kevin.
It was really helpful.
Does Gmail Mail Merge allow individualized recipient attachments (like an invoice) to be sent along with each individual recipient email?
I wonder if you create a link within the spreadsheet if it would transfer?!?
Hi Kevin! Loved the video. I'm using the spread sheet, left the colums you said to leave, but want to add a cc so that I have a copy of the sent email for my records. When I go into Tools, I don't see the Script Editor option. Do I need to do something to be able to access that? Are there any other options available? Thank you!!
The script editor is under the name "Apps Script" which can be found on the Extensions menu.
Thanks@@jmdesiatco5729, worked for me. Still don't understand why I can't see Scripts under Tools.
Great video - followed instructions and worked perfectly! Thank you.
What an excellent video. Really well organized!
Great video. I was wondering if it would be possible to include individualized attachments for each email sent. I need to send out over 500 emails with individual certificates for students. Would this be possible. Could I use the spreadsheet somehow that would link to the certificates I have saved in Google drive? Thanks
Did you find a solution?
One alternative is to add a new column name Attachment and add a Drive link. In the draft mention {{Attachment}}
Hi Kevin. Thanks for sharing this video, super helpful. 2 questions:
a) What is the daily limit for the number of emails we can send through this method?
b) Is there a way to include a static email ID in CC. I tried but it's not working.
Pls let me know. Thanks again.
-ankit
I'm also looking for the same
a) the limit is in the video description.
b) I also want answers regarding this question. So far, the "TO" should be "{{Recipient}}" on the sheet and the Draft so maybe the CC only need to have its proper wording for it to work.
The limit is discussed in the "limitations" chapter of the video, near the end! Watch the video!
@@ChrisKChandler in reality at least for me the limits seem to be lower. Gmail seems to throttle how many emails you can send all at once so that limit might need to spaced out during the day.
Oh, this is a great video. I just wish I had read the comment below that said to switch to plain text mode. 30 emails sent without a name in the body = marketing disaster!
OMGosh!!!!! This is great. my husband said why don't u do a mail merge instead of spending hour sending out cusum emails with names. I said do what? Your video was great! saved me hours and hours.
This is so helpful to know how to do in gmail. Thanks Kevin! :)
Hello,
How to attaches personnel file for each récipient plz? Like school awards or diploma
I have the same doubt!
I had a script (thanks to the excellent Learn Google Spreadsheets RUclips channel), but needed extra functions. This script looks perfect! Thanks.
Thank you so much for this great video! I just sent my first mail merge message to 6 recipients. Yay!
Hope you are fine. how to get the mail merge option on the google spreadsheet because it is not present with the sheet by default? Thank you very much.
Hi Kevin, thanks for a teaching this wonderful function. I tried it but the parameters within {{}} only worked for Recipient and Title, but did not work for the body of text. Did I do something wrong?
I'm having this same problem. I want to add the First name but it is not showing up in the mail. Anyone can help with that. Thanks
Hi. I'm having the same problem as well. Can the author help? Thanks :)
I had the same issue. I re wrote the script in WORD with the {{cell heading name}} then copied and pasted it into my email draft. That fixed it.
Thank you for the great video! Whenever I try to add in the curly brackets and copy/paste the First name cell, it pastes bold (which is different from when you do it in the video) and then in the test emails I receive, the first name of the person doesn't show up. Am I doing something wrong? Or is something not working properly in the script of the google sheet? Thank you
@WTMinkema Same problem , it just works in the subject line , not in the body of the email
I was able to get everything to work except the recipient name. The name never populates in the email.
How can I export my entire contact list at once to google sheets?
Bethlehem Early Learning Center
11 hónappal ezelőtt
SOLVED: Fix for missing fields in Body of Email:
1. Copy/Paste field from Sheet into Subject area of email - (adding curly bracket syntax - example: {{field_name}} )
2. Copy/paste again (from curly bracket to curly bracket) from Subject area into Body. Voila!
3. Delete field from Subject area if not needed.
4. Repeat for each field that you need in the body.
Basically, I believe we must strip the formatting from the field name when inserting it into our email body, and it seemed to work best when copy/pasting from Subject area to Body.
Thanks you for always creating coherent videos.
Thank you Kevin. Your tutoring is super simple and I followed every step to the T. I even received the confirmation that the emails were sent, but no email was in fact sent. I don't know what I did wrong.
Hi, I am wondering why it is not capturing the First name and other scripts in the bracket? thanks!
I'm having this same problem. I want to add the First name but it is not showing up in the mail. Anyone can help with that. Thanks
I followed step by step. In email, the subject line was fine and pull the data from the googlesheets, but the email body was missing all those data (first name, description, due data, price). Not sure how to fix it. Any thought. Thanks.
It happened to me too, try to write word by word and take care of write without bold type
Very helpful on using a mail merge process, thanks
KEVIN YOU ARE DOING SO NICE FOR US
Sir it says "Exception: The starting column of the range is too small."
Hi Kevin, thanks for the video. I tried and was successful in sending email, but could not merge first name, last name columns. Can you help please?
You would have to do {{First}} {{Last}} as separate fields. Alternatively, you can add another column and the concatenate formula to merge the names into one cell.
Hope you are fine. how to get the mail merge option on the google spreadsheet because it is not present with the sheet by default? Thank you very much.
Thanks Kelvin...your videos helps a lot.
This worked great!! Thanks for the instruction and the links. Perfect!!
Great video but the first name doesn't show up. I use {{First name}} and I have tried for hours in all different scenarios of cutting and pasting and still no luck. Any help would be great.
did you find solution
@@cansam2905 yes, after hours of messing around and it never working correctly, I started using YAMM and it works as I would expect something like this to work.
and YAMM is native to google so there was even less messing around.
Thank you so much
@@cansam2905 hi, if your email font and font of the fields you copy are in different fonts it won't work. Rather copying the field, please type in the email exactly. It will work. Hope this helps.
Thanks a lot for this video Kevin, really helpful!
Anyone knows how can I make the script use MY NAME as the SENDER instead of my email? The recipient now receives an email from 'INFO' and not 'Wood Bern Carvings'
Thanks in advance, Bernat
Happy Holiday
beautiful you’re very hardworking
Keep going... And stay tuned 😉
big like
thankyou Kevin - this is amazing - as usual. You & my other fave Leila G ROCK!!!
Great Video Kavin! I am facing an issue. The custom fields are perfectly working in subject lines but not in the mail body. Can you put some light on this problem??? what can be the possible reasons???
I'm getting the same thing.....
I'm having the same issue. Did you get any help or were you able to make it work?
hi. i had this. try removing formatting in your draft email
@@anthonymorton3164 Thanks. Yes, I was able to get it to work once all formatting was removed.
Bethlehem Early Learning Center
11 hónappal ezelőtt
SOLVED: Fix for missing fields in Body of Email:
1. Copy/Paste field from Sheet into Subject area of email - (adding curly bracket syntax - example: {{field_name}} )
2. Copy/paste again (from curly bracket to curly bracket) from Subject area into Body. Voila!
3. Delete field from Subject area if not needed.
4. Repeat for each field that you need in the body.
Basically, I believe we must strip the formatting from the field name when inserting it into our email body, and it seemed to work best when copy/pasting from Subject area to Body.
Sir can you teach me how to unlock my pc I am stuck at login page and there is no password bar to enter password 😢😢
Please sir
Sir same problem
Sir my also same problem
Thank you, this is exactly what I was looking for.
Thank you very much straight forward an easy to follow.
Thank you so much for this clear video. Much appreciated!
This has been very helpful, it works now! Thank you :)
Thanks Kevin, I was stuggling with this. I was using an email marketing app but some of the emails went to the spam box. Keep on sharing the knowlegde.
Thanks Kevin for this helpful video.
Thanks a bunch! You did a great job dude. Enjoy the day!
Hey, this was really helpful. Thank you!!
Hey Kevin,
Just found this Video. It was really simple and very helpful.
Thanks for this.
This was super helpful. Thank you!
I had to try it 13 times while tweaking the tags before it finally worked.
I can't tell what really was the problem but the tags were returning nothing in the sent mail.
Thank you Kevin
this is a great tutorial... solved my problems! thanks a ton!!
Hi Kevin! This helps me alot. Keep up the Good Job. Thanks for this. Pressed Subscribed.
Just want to say thank you so much Sir, highly appreciated. South Africa
Thank you so much for this great tutorial!!!
Thank you Kevin- very helpful!
Thank you for creating a very helpful and easy to follow video! Great job!!! :) :) :)
Thank you Kevin, It would be helpful for my father's company
Thanks A lot Bro!, really easy to follow and yeah pin point explanation and direct, well you just earn a subscriber here
As always this one is one of your greatest works Kevin and if you
make the tutorials of web designing it would be so much great and your
subscribers will be double.
I'm searching for tutorials of the web designing on youtube but none of them can teach in the way that you do. I'm looking for your reply with a suggestion about the web designing tutorials.Thank you!
Kevin, thanks for these videos. 1. There are now extensions that can be installed to handle mail merge ("Mergo..." & "Yet another..." & many more). I started to install these two extensions but found in the permissions for both, they wanted access to view & download every file I have stored in my Google Drive. I stopped looking at the extensions & went back to this script's permissions - no such requirement (no mention of Google Drive files). Thank the gods b/c I had already used this script embedded Sheets file. If someone wants to use a mail merge extension and is using Google Drive for anything you don't want shared with the company issuing the extension, then setup a new Google account only for email merging!!
Good point but it's not just that. YAMM give a tiny free mail merge allowance of 50 emails which only seems to reset after you've used it all up rather than automatically resetting every 24 hours. YAMM used to allow you 50 extra emails for each person you referred to use it which got you up to a decent allowance but they took that away so you're more inclined to pay for an increased allowance.
The BEST of all the explanations
Thank you, very precise and the perfect solution to my issue. The snippet on the coding was very helpful.👏
It’s best video so far, thanks kevin!
This is really helpful. Thank you !
Thank you so much for this helpful video!
Thank you so much @Kevin. Wonderful, it all worked for me first try. Many thanks..
Very helpful tutorial Kevin!
Thank you for this video !! Awesome !!
Nice and clear, we're going to use this to contact players in our bowls club.
Thanks bro that’s very helpful for me
This video was very helpful, thanks alot i can confirm it works and is very simple yet efficient
Thank u for sharing Kevin, it do helped.
Awesome Kevin!!! Thanks a lot for this video
Such a helpful video, thank you so much!!
Thank you so much bro, this definitely worked
Hope you are fine. how to get the mail merge option on the google spreadsheet because it is not present with the sheet by default? Thank you very much.
Great. Thanks for sharing. It solves a problem for me
Happy new year Kevin. Thank you for this tutorial video on how to use google sheet for Mail Merge. It's the best video explaining how it works and hence is my thumbs up. Quick question if you can reply, how do you import contacts from Gmail into the mail merge google sheet? Would be great if you can share this. Thanks a lot.