Placeholder Image

Subtitles section Play video

  • Today's video is about creating

  • an email with an Excel formula.

  • Here's the best part,

  • we're not just going to be adding the send to address

  • to the email but we're going to be adding

  • a subject line and a body text without you touching Outlook.

  • Guess what?

  • All of these are cell references

  • so you can create dynamic emails with formulas.

  • This means you can prepare different email templates

  • depending on the cell value.

  • Values are too low, send out the motivational email.

  • Values are great, send out the celebration email.

  • Pretty neat, right?

  • Let me show you how you can set this up.

  • (bouncy music)

  • Here's the great thing about Excel's hyperlink formula.

  • You can use cell references in there

  • to get this to be super dynamic.

  • You can even combine it with other functions.

  • So for example here, I have the name of the person.

  • I have their email address.

  • The sales that they achieved in the month

  • and the original goal they had.

  • Based on this, we're going to see if we send them

  • the motivational email or the other email.

  • So Gary in this case, he didn't reach his goal.

  • He's going to get the motivational email.

  • Now when I click on this,

  • Outlook automatically opens,

  • puts his email address in the correct field.

  • The subject line is automatically there and a body text.

  • Richard, for example, he's going to get

  • a different type of email because he achieved his goal.

  • He even exceeded his goal by 500.

  • And when I click on send email,

  • I get a personalized subject line

  • that says "Thank You Richard.

  • "You achieved your goal of 6000.

  • "And you even exceeded it by 500."

  • All these calculations are done with formulas.

  • There's no VBA

  • here. So if Richard for example didn't achieve his goal,

  • if he only made 5000, he's going to end up getting

  • the motivational email.

  • If he achieved it by a bigger amount, so let's by 1000,

  • he's going to get a different body text:

  • you exceeded your goal by 1000!

  • Now where is all this coming from?

  • Well you've probably noticed I have

  • some hidden columns here.

  • These are the helper cells

  • that help me get this to be dynamic.

  • In the first column here, I'm calculating

  • by how much the goal was exceeded, if at all,

  • so I'm using an if function here.

  • Now if you're not familiar with this function,

  • I have a video on that.

  • I'm going to add the link below this video,

  • so check it out.

  • The subject line uses some of Excel's great text functions

  • like the trim and the left function

  • to grab the first name from the full name here.

  • And I have the dynamic body text in there,

  • which is a combination of typed in text together

  • with cell references to bring in the numbers in here.

  • And the end result here uses the hyperlink function

  • together with the if function,

  • which then decides which type of email

  • this person should receive.

  • Now here's the problem with using the hyperlink function

  • is it needs special syntax when you want to send email

  • and Excel doesn't give you help while you're typing this,

  • so there is no special arguments

  • that you can just work your way through.

  • Let's take a look at this step by step.

  • Here I have a list of emails

  • and I want to create dynamic email hyperlinks to these.

  • Now if you were going to do this manually

  • with just right-mouse click here,

  • go to link, select email address

  • and type in the email address right here

  • 'cause in this box here we can't do cell references.

  • So I'd actually type in kim at say hello dot com

  • and notice that the moment I start typing

  • Excel automatically adds this special syntax.

  • Now if I start typing the subject line

  • it again adds this special syntax.

  • It starts with a question mark then subject equals

  • and then it puts the text that I typed in.

  • Now this is the syntax that we need inside our formulas.

  • So the question mark is the start

  • of defining the attributes.

  • If we have more attributes in here,

  • we need to use the ampersand

  • but don't worry I'll take you through this step by step.

  • So let's make this dynamic because you could have

  • a lot of emails and it's going to cost you

  • a lot of time if you had to do each one manually.

  • So we're going to use the hyperlink function.

  • All the syntax we need for email

  • we have to put in the first argument here

  • called link location.

  • So remember, Excel gave us that mail to syntax

  • that's the syntax we need here.

  • And since we're typing text inside a function,

  • we need to use the quotation marks

  • so that was mailto and then there was a colon.

  • Now add quotation marks again

  • and because we want to combine this to a cell reference

  • I need the ampersand and that's my cell.

  • Now let's say I just want the email address,

  • I don't have a subject line or body text here,

  • so I'm just going to go to the next argument

  • which requires the friendly name,

  • so basically what I want my link to be called

  • and I'm going to put send email,

  • quotation, close bracket, press enter.

  • Now when I click on this, I get Kim's email address

  • in the to field right here.

  • Okay, so let's expand on this and add a subject line.

  • One option is to just type the text directly

  • in your formula or make it dynamic.

  • So let's just make it dynamic.

  • Up here, I'll add the subject to this.

  • Now all we have to do is to expand

  • on the link location argument by adding in the subject.

  • Now again, because I'm combining some syntax,

  • some text in my formula, I need to use

  • the ampersand, quotation.

  • Now the syntax we saw before starts with a question mark

  • then it was subject and equal sign

  • and now I'm going to add the quotation

  • and combine those together with this cell.

  • Sinc I'm planning to drag this down,

  • I'm going to fix this reference by pressing F4.

  • So let's just test this.

  • The subject is right there.

  • What about adding Cc to this?

  • So before we add the body text,

  • let's take a look at Cc.

  • Let's add it here and make sure our boss is on Cc.

  • How do we update this?

  • We just continue writing here this time

  • because I'm adding more attributes to this

  • I need the ampersand.

  • Syntax for Cc is just cc, add the equal sign

  • and combine it with this cell reference

  • and I'm going to fix it with the F4 key.

  • Let's test.

  • Boss's email is right there.

  • Last, let's add a body text to this.

  • Let's add another attribute for the link location.

  • This time I'm going to add the body text.

  • So again, &body equals and now let's combine it

  • with this cell.

  • And let's fix it and press enter, double check.

  • It's all there.

  • Now one thing you might want to do is

  • to add a line break to this.

  • The problem here is if you add a line break

  • in here using alt-enter, it doesn't pull through

  • to your Outlook email

  • so when I click on this, I still see it on the same line.

  • The way you need to do this is to add

  • the carriage return character code to your text.

  • So let's just put this back.

  • The code you need is %0A.

  • This is recognized by Outlook and it tells it

  • to add a line break to this, so now when I click on this,

  • it puts my second sentence on a new line.

  • Okay, so now that I have it all set up

  • I can just drag this down and send an email to Tom.

  • And just like we saw in the previous example

  • you can have different body texts,

  • different Ccs, different subject line for each person.

  • Depends on how you want to set it up.

  • The only thing you need to take into account is

  • the character code limit in Excel formulas.

  • So that's how you can use Excel's hyperlink formula

  • to create a complete email and save yourself a ton of time.

  • If you enjoyed today's tip, give this video a thumbs up.

  • Thank you for tuning in

  • and if you haven't subscribed to this channel,

  • consider subscribing.

  • (bouncy music)

Today's video is about creating

Subtitles and vocabulary

Operation of videos Adjust the video here to display the subtitles

B1 US email syntax excel subject dynamic email address

Fully Dynamic Emails from Excel with a SINGLE FORMULA!

  • 11 0
    Amy.Lin posted on 2019/12/27
Video vocabulary