Placeholder Image

字幕表 動画を再生する

  • 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

字幕と単語

動画の操作 ここで「動画」の調整と「字幕」の表示を設定することができます

B1 中級

SINGLE FORMULAでExcelから完全に動的なメールを作成! (Fully Dynamic Emails from Excel with a SINGLE FORMULA!)

  • 9 0
    Amy.Lin に公開 2021 年 01 月 14 日
動画の中の単語