Send Fully Dynamic Emails from Excel with a SINGLE FORMULA | No VBA Required!
Vložit
- čas přidán 17. 07. 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Did you know you can create an Outlook email from Excel? You can even create it with an Excel formula using cell references for mailto, subject and cc? The Excel Hyperlink formula gives you the ability to create dynamic emails.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/send-email-...
In this tutorial, we explore the power of Excel's hyperlink formula to create dynamic emails directly from your spreadsheet. Perfect for users who frequently send out emails with varying content, this video guides you through the process of setting up automated emails that include customizable subject lines and body text, all driven by cell references in Excel.
🔗 What You'll Learn:
▪️ Automating Emails with Excel: Discover how to use Excel formulas to send emails without opening Outlook.
▪️ Dynamic Email Content: Learn to create emails with varying subjects and body texts based on specific cell values.
▪️ Using Excel's HYPERLINK Function: Master the use of the HYPERLINK function to craft emails, add CC recipients, and even include line breaks in the body text.
This Excel tutorial will also show you how you can get creative with the Hyperlink function. We will use the Excel IF Function to create two different types of emails depending on whether goals were reached or not. We will also use some of Excel's text functions like TRIM and LEFT function to grab the part of the text we need for our email.
00:00 How to Create Emails from Excel with a Formula
03:20 How to Use the HYPERLINK Function in Excel
05:29 Add a Subject Line
06:23 Add a CC Recipient
06:57 Add Body Text to the Email
07:22 Add a Line Break to the Body Text
08:27 Wrap Up
What to do if you need:
Excel email with Multiple Mail-to: If you'd like to have multiple recipients for the mailto address, just add the email addresses in the same cell and separate them with a semicolon.
Add a Line break to email body: To add a link break in the email body, you can us the carriage return character (%0A).
Beware of Excel's formula 255 character limit - you can't use the Hyperlink formula for extra long emails. Keep the [friendly name] as short as you can to allow for more characters in the rest of the email.
Excel IF Function Tutorial: • Excel IF Formula: Simp...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
Grab the file I used in the video from here 👉 pages.xelplus.com/send-email-excel-file
@LeilaGharani Can you help me with on doubt? In my formula description value is displaying very limited in email...for eg. If description in column is 250 words then in email it only limit to certain range like 50 or 100 or 150 and if it goes beyond it then it gives #value! Error...then i need add a formula to restrict upto 50 words to avoid erroe with below formula : &body="substitute($AH$309730&Left(H2,50)....
So above formula is not avoid value error to keeping H2,50, if i type H2,51 then it will throw error..😮so with this formula i giving command to only display 50charater to display so i wont get error
Regarding attachments: You can add the link to the file in your body text. To be able to add the actual file as an attachment you can use VBA. If you're in my VBA course, check out sections 15 & 18 (to attach any PDF document). I'll add this topic to my list of future videos for CZcams as well. Regarding multiple email addresses: You can add these to the same cell and separate them with a semicolon ;
can we .ore people to to or cc ?
Thanks leila for reply
Can you do one how do you send the emails automatic not one by one. ? Is great but still you click individually
Thanks
@@roxiserban what do you say i didnt ustand
Great Video. Appreciated.
Can charts from excel be pasted (as pictures) in email body using hyperlink?
Just when I thought Excel couldn’t get more awesome, you took it to the next level. Leila, you are the Best!!
I'm happy to hear that :)
@@LeilaGharani hi , how can i create mail .
@leila How to add multiple recipients in "to"
@@LeilaGharani please answer
My best Excel tutor. Thanks for all the videos you have made on Excel, following your tios and examples has made me better at work. I love your works..
Oh God - and all these years i was using hyperlink just to switch in between worksheets. Excel is cool but only when the teacher is Leila.
Thanks much for this. :)
This is really informative.
It will be much more beneficial if you can show other variations like adding a table, formatting text and sending email from a specific account
My 1st impression was "Wow, so this exists in Excel?" Thank you very much Leila for making a video on this. Very impressive
Thank you Shadrack.
@@LeilaGharani...it is mostly useful at my work..could you please help me in adding excel cells tooo...if you can do that
I have went through few videos of information on using formulas in Excel and it was really excellent, you cover each and every point and you start from the scratch so that everyone can easily understand. I learnt many things watching those videos and it is helping alot in my day to day work. Hope we get more information on Excel and accounting topics from you.
Thanks alot Leila 😊
Glad you like the teaching style :)
Wow! This is fantastic. I am working on excel everyday in office but never realised that there is possobility for this. Thanks a million for sharing this wonderful way of using excel.
Leila, you really hit a home run with this one - you could probably do a follow-up video on all the possible applications of the technique. Thank you once again!
Glad you like it Robert. Yes, maybe I'll do a follow-up.
"%0A" is the gem in this video.
How would one find this out otherwise?
Great work Leila, thank you for the %OA!
Do it 2x (%0A%0A) to make it as a paragraph 😀 instead of just a sentence.
Hi Everyone....I want to add 5 lines in the body but unable to and 5 line ..when I am addinf amall data its wirking but not for much data...can anyone help me
As one of the Mac Guru's in the 80's I was given beta copies of all of Excel software since Excel 1 so I have a very thorough knowledge since the very beginning. I sent my recommendations of all the beta's I received and some of them even got into the finished production of many of the Excel software updates. I enjoyed your video. It was very thorough and precise and moved at a good pace. Kudos for a fine job. Hoping to see more of these...
Many thanks for the kind feedback, Jesse!
Hi Leila, thank you so much for this video. It offers so many new possibilities for my daily work.
Hi Leila, great video ! is it possible to include an email signature when using this technique? as in you set up the email to include a signature you have on outlook - or would you need to use VBA to do this?
Great tutorial as always, Leila! I worked at a company that had a "click to email" cell like this and I'm sure it used a lot of what you mentioned. However, it also included graphs as part of the email. Is there a VBA-less way of doing that?
simply say love you leila the way you explain subject...great Leila, God bless you
This is super duper awesome Leila.. just love it!!
Hello Leila. This is a wonderful video. Very helpful indeed. I would just like to ask. Is there a limit on the number of email addresses in the "To" and "CC" fields. Seems as if I'm getting an error (#value) if i'm adding multiple email addresses (more than 3 or 4 email addresses). More power to you and I hope you continue sharing such wonderful tutorials.
Amazing.
Just love it.
Is there is any way of adding attachment dynamically ? It will be a great help.
#Waterresourcecivilengineeringandresearch
@@waterresourcecivilengineeringa this is not helping what Sajjad asked!!
Again a great video Ms. Leila. I was looking for something for a long time. Thank you so much
This has blown my mind Leila...you are awesome
This is AWESOME! Question - how do I automatically add my e-mail signature to this e-mail? Short of a lengthy explanation, this will make may day to day life so much easier, with or without the e-mail signature!
Hey, great tutorial. Is there a way where you can record a response when a user sends an email (or decides not to) ?
Holy cow, that was some advanced trick! Never knew this. Excel is so powerful, thanks for teaching!
Is there a way I can attach a document to the email also.
The character code %0A use in the subject, is there a list where i can find every single character code that i can use in subject?
Been watching your videos for a while, even new tasks I can find a video. Thanks!
You are the best! Thank you for making this complicated excel functionality so easy...
Love this! Is there a way to just click one button and then it sends all the emails instead of clicking "Send Email" individually?
Sounds like you need VBA for that and you will have to code SMTP send commands
Did you get this option, could you please send me if so
I'm also after a similar solution. We have a listing of shipping dates and these do change. The ideal solution sends out an email with dynamic content of product, old shipping date and new shipping date to an email address in the background
I'm guessing you found a solution to this by now, but I'd use the Mail Merge feature in Word to send all those emails rather than the process described in the video. We use Mail Merge to send emails to larger groups of people. Set it all up in Excel and then use Word to send everything. This also avoids the character limit you run into in Excel.
Hi and thanks for amazing video! Is there any option of including the data from 2 different cells into subject?
For example: I have data in cells A1 and B1 and i want to see both of them in the subject?
You can combine them in a third cell and reference that instead, or you can add them both to the formula.
@@nisemapi late but stilll valid (just tested) =HYPERLINK("mailto:" &C2 & "?subject=" &F2 &G2 &H2," send email ")
C2 being the emailaddress, F2 and G2 the parts that you want to combine. No need to combine them into another cell first.
Excellent tutorial as always Leila, thank you!!
Amazing! Thank you so much for this helpful tip :-)
Awesome! Implemented already.
BUT how do I get my email signature to show in the email automatically?
If you want the signature in the mail, don't use any body in the formula. I think whatever is passed through the *body* parameter supersedes the Outlook setup and hence the signature is not included.
It would be a trade-off between insert the signature manually or type the body manually.
pls who can tech me how to make email
Whenever I add the body text my signature disappears from the email. Is there a way to add the signature through the hyperlink function, or should I just manually add it each time?
Additionally, is there a way to mark the email as "High Importance" through the hyperlink function?
Thank you for these videos, I thought I was good at excel but watching your videos I have learned a lot!!
Thank you so~ much. Your video is the top as usual.
You are too amazing. Thank you very much for sharing your knowledge to the world and especially me. You made me feel more values about the knowledge of education
Can we also automate sending each of these customised emails? Clicking each cell would become cumbersome and we'll have to go for mail merge then?
Vivek S. Narayan I see no one responded yet but have you found a solution on your own that you’d be willing to share?
@@ryannoe86 The common way is to create the email and the use hot keys to simulate pressing send (when I have tried this it failed a lot)
Another method that I came up with, was instead of excel trying to send the email, it just saves it in Outlooks draft box with a subject line that starts with a string that you would never use for anything else e.g "XYZ123"
Then you have a second macro, in Outlook, that is triggered when a new email is saved - and this macro searches the draft box for emails with subjects beginning with "XYZ123" and sends them.
@@gareth6326 That's interesting that you use hot keys. I use AutoHotKeys to send out emails now but I know it isn't the best way of getting it done. I'd like to get it sorted in VBA and Excel if possible. Thanks for the reply!
@@ryannoe86 I don't use hot keys, I was just saying that is the common method. When sending emails in excel in use the second method.
The easiest way to automate sending an email is to use .NET instead of excel.
I recommend Mail Merge feature of MS Office. Works like a charm, every time. MS Word, MS Excel and MS Outlook come together in harmony to send emails. If you combine the excel possibilities, you can only imagine how good this feature is.
Hi Leila, is it possible to expand this method into adding tables, or font formatting onto Outlook dynamically?
That was my qtn too. We are in excel so 90% chance would be a requirement to include table into the email. @Leila help😊
Have you got this how can add table in mail automatically
Thank you Leila!
I'm gonna watch this over and over
My pleasure Shola :)
Appreciate, sharing knowledge with people, who struggle with their daily work.
If the email body text needs multiple cell like B2:E12 will it work?
#VALUE
Up to three cells, use "&" or "concatenate" function
How do we overcome the character limit (value)error in hyperlink. It’s when we create for a email and the body is more than 256 characters in length. Can you please suggest a solution. Thanks.
Ever figure anything out?
Did you find any workaround to fix (value) error in HyperLink?
Hi Leyla! Do you have solution for this?
255 is the max limit no workaround I believe, the only alternative I see is using a VBA.
Same issue here. I came to this as a work around for VBA and now need a different work around again
Again really cool Video. I am on a Help Desk staff and this is what I did. For my boss I made a rapid email response email workbook. The first section consisted of a listing of everyone's first name and Email accounts. Te following columns contained what would create a email directed to each individual of routine emails he sends like, Remember You Have Tasks Today, or Review your open. I also wanted to set up a series of Blast messages which go out to the group. The trouble is I ran into a problem when I tried to add more than 10 email addresses. We have a group distribution list in Exchange. So I used the Distribution list name with @our domain. It worked. My boss most likely will not use this as it is not his style. Still I enjoyed the time spent on it between calls.
WOW! This is amazing. I never even knew this could be done in Excel. Thanks Leila.
My pleasure. Glad you like it.
Nice excel tips! I am using excel 2019 and somehow it acts funny. I used the simple form of hyperlink function just to send email =Hyperlink("mailto:"&B4,"send email) the first time i used it it has opened outlook but the second time and on it just opens a empty web page. Any ideas?
Change the Default Browser to Outlook.
@@PremKumar-ef9mm i have the same problem
Please more information
@@PremKumar-ef9mm how?
I have the same problem
Thanks, this was helpful, it doesn't want you to exceed the 255 character though. Is there a solution around it?
Same problem
Same problem, is there a solution?
Some Keyboard shortcuts which improves productivity :- czcams.com/video/s5Zu6UoUodk/video.html
Leila, you are fantastic! Thank you very much!🌸🦋👍👍👍
Great :-)
How to add a range of cells in body text (say from range l6:m15)?
It wd be gr8 if you make a complete video on adding attachment, picture etc...
Kumar V
Yesssssss!!!!
Please see pinned comment.
Can we add attachment
How we can add attachment
@@surajdabhade9022 agree how to add an attachment
Just guessing but I wonder test the syntax &attachment="fullfilepathnamewithfiletype" referencing the path in a cell like the other attributes...
Please check the pinned comment above.
@@suzannegriswold7555 Pls write complete formula....
just started learning excel, what a great reference material! thank you very much.
Really I was a great video and absolutely helpful.
Requesting you to make more videos which are such helpful.
Hi Leila, is there a character limit on the hyperlink function? I notice when I use it for a longer sentence the function returns #VALUE. Any tips or workarounds for more lengthy email text?
Same happened to me too, it looks like there is a limit up to 255 characters, @Leila Gharani any advice please ? Also, how do I fetch multi-cells from excel into body text ?
Did you fix this issue?
even I faced the same. How to use longer sentences?
How to add different attachment to different recipients, If to keep it simple- Name of file will be the Name of Recipient within one of the folder.
How to add ?
Please see pinned comment.
@@LeilaGharani I am not seeing a pinned comment from you. Can you please share again?
@@LeilaGharani Hi, what pinned comment?
Stephen Boyce , the pinned comment in the beginning...
@@MarcosReisPT Am i missing something here, where in all of those links does it show you how to add in an attachment in the hyperlink formula? Its not possible is it, unless you do it in VBA.
Love your content. I wish I had this vid 2 years ago!! Thanks! Next vid should be about sending yourself text messages from excel!
Of Course!... it makes so much sense, but even I had never thought of this ... always done it with a tonne of code!
Thanks Leila, instant fave and I am sharing this with my two Excel Guru workmates!
My pleasure. Hope your workmates will like it too.
When the body text is long, i receive an error message...great video though!
Any solution for that?
@@HKNAGPAL7 There's a formula limit of 255 Characters. I guess you can only use this method for super short emails :/
Thanks
Great. Now tell us how to send all the emails at once without clicking ech of them.
Yes this is very important question.
You need to write up a macro for that
Thanks Leila. You are so helpful. I am learning a lot from you.
You are wonderful...with yours formulas in the Excel.
Outstanding work,LG
You always come up with new things I didn't know Excel could do. There is SO much to Excel. Thanks for sharing.
True, there is always something new to learn. For everyone.
awesome, this made my life easy. thanks much!
Thanks #Leila for this learning. I really appreciate. Your style of teaching is so amazing. :)
Exactly what I was looking for, thanks! :)
What a genious! It cannot perform all my task but at least a great porcentaje of it!! THANK YOUUU
Brilliantly explained! Step-by-step and in logical sequence! Thanks!
Glad you like it.
I teach computer classes at a elementary school and this is super amazing! Thanks for posting. And your delivery is very well done! Greetings from San Juan, Puerto Rico! Luis
You're very welcome, Luis!
very helpful! i am going to binge watch more!
First time seeing your videos, this is a great help thank you
Very simple solution.
Thank you so much!
Thanks Liela ... This is really great
Leila ... I guess I have to give my entire knowledge of Excel and MS Office applications credits to you only
Thank you so much for your efforts ...you have no idea how valuable this video is for me ...the task i am doing is so manual and tedious that I most probably procrastinate it until I am on fire
Really really thank you so so much Leila
May God bless you 🙏🏻
Many thanks for the kind feedback, Anand!
Amazing video! Really helpful!
I have went through few videos of Power Query and Macros and it was really excellent, you cover each and every point and you start from the scratch so that everyone can easily understand.
I learnt many things watching those videos and it is helping alot in my day to day work.
I would like you to plz make a video on how to add table in body of email if possible. It will be really helpful.
Is there any way to do this?
Hope we get more information on Excel and accounting topics from you. Thanks alot Leila
amazing ! thank you Leila to share it
Thanks Leila. It's really helpful.
Brilliant! Leila!
Awesome Leila. Thank you.
I just love the way you simply. I have subscribed to your bundle course on Udemy. Thank you🙏🙏🙏
I’m an analyst and been watching your videos and find them really helpful ma’am! Thanks alot!
My pleasure. Glad the tutorials are helpful.
Thank you, Leila!
The video is great and very useful! thank you so much!
Leila, you are the best ... Thankyou
I am following you since 3 to 4 months, you are giving so clear message to your viewers, in fact I love the way you explain. Simply I could say I love the whole.
I just want to know the code for attaching a file and how to show the path.
Wonderful video Leila! Lovedthe techniques.
Can't wait to use them. :)
Glad you like it. Let us know how it went :)
Hi Leila, it is an intteresting approach of excel to be able to integrate harmonically with such formula, and your ability to tweak the function. With this, we can update the excel dynamically, especially with Excel Online 365 with its limited function.
This is very useful for my current project at work... Thanks!
WOW, amazing. First time I knew there is such features in Excel to simplify tedious work
Glad you like it.
THANKS A TON, LEILA!!!
This is amazing.
Brilliant. Thank you for sharing.
Someone give her a noble prize 💕 Keep up the good work 🌹
This is awesome, thank you so much.
Great tutorial. Thank you lady
Great little tip
Thanks for sharing
Thank you for the great lesson.. i only recently really got an interest sparked in the full potential of excel.. before just the dead basic. However this lessin resolved a hurdle i faced, i attempted at my work desktop with 2016 and getting a formula error all the time even if followed to the T. On my personal pc with 2013 version its a success. Pondering the reason as should 2016 not be more advanced and giving lesser headaches. Same thing happens when converting to text 2016 gives few conversions while 2013 gives all.. subbed the channel as my thirst for knowledge cup runnith over .
This is a dope tutor. Many thanks.
Love it!
Thanks, Ms. Leila... This is very useful for me.