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)