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)
