Send Email from Excel

with a Hyperlink

This post is about creating an email with an Excel formula.

“But I already know how to do that; it’s called Insert – Hyperlink”.

Don’t click away so fast.  We will put our typical XelPlus spin on this and perform an action you may never have known possible with Excel.

Our hyperlink with be smarter.  Along with filling in the “To:” field of the email message, our hyperlink will have also fill in the subject line and the body text.

And to make this extra special, we will even have different subject lines and different messages in our body text depending on the status of the link being selected.

And this is all WITHOUT touching Outlook (well, maybe one click… SEND)

All this will be created using cell references and formulas to create dynamic emails.

The Wonder of the HYPERLINK Function

The HYPERLINK function in Excel allows us to use cell references to create an email.  The referenced cells can contain other functions, like IF and XLOOKUP, which allows us to create incredibly dynamic messages.

In our sample below, we have a list of sales representatives, their email addresses, current sales, and their sales goals.

We will create a dynamic HYPERLINK that will send either a motivational email if they missed their goal, or a congratulatory email if they achieved their goal.

By clicking the dynamic hyperlink, we generate one of these two email messages.  Each message contains the following:

  • Sales representative’s email address in the TO: field
  • Custom subject line
  • Custom messaging in the body text area

Creating the Supporting Formulas

To achieve this behavior, we need to create some “helper columns” that will contribute to the construction of the final hyperlink.

The first “helper column” called Exceeded Goal (starting in cell E2) will use an IF function to determine if the sales representative met their sales goal.

=IF(C2 >= D2, C2 – D2, “”)

If the representative’s sales are greater than or equal to their goal, we calculate the difference (overage).  If not, we display nothing in the cell (two sets of double-quotes).

NOTE: For more information on the IF function, check out this post.

The second “helper column” called Subject (starting in cell F2) will also use an IF function to determine which subject to place in the Subject field of the email.

=IF(E2 = "", "", "Thank You " & TRIM(LEFT(A2, SEARCH(" ", A2))))

This formula uses some of Excel’s text manipulation functions, such as LEFT, TRIM, and SEARCH, to extract the representative’s first name from column A and concatenate it with pre-defined text.

NOTE: An alternate version of the formula using only the LEFT and SEARCH functions is as follows:

=IF(E2 = "", "", "Thank You " & LEFT(A2, SEARCH(" ", A2) - 1))

The third “helper column” called Body (starting in cell G2) will use an IF function to create the custom message that will be placed in the Body field of the email.

=IF(E2 = "", "", "You achieved your goal of " & D2 & " .%0A You even exceeded your goal by " & E2 & "!")

No; that’s not a typo.  The .%0A will be discussed later in this post.

The final “helper column” called Send Email (starting in cell H2) will use the IF and HYPERLINK functions to generate the clickable link.

=IF(E2 = "", HYPERLINK("mailto:" & B2 & " ?subject=Let's do this & body = Try harder next month!", "Send Motivation"), HYPERLINK("mailto:" & B2 & "?subject=" & F2 & " & body=" & G2, "Send Email"))

The HYPERLINK Function

The HYPERLINK function has the following structure:

HYPERLINK(link_location, [friendly_name])

  • Link_locationrequired. This can be a path and file name to a document to be opened, a universal naming convention (UNC) path on a server, or a Uniform Resource Locator (URL) path on the Internet or an intranet.
  • Friendly_nameoptional. The jump text or value that is displayed in the cell.  The Friendly_name is displayed in an underlined, blue font.  If not defined, the cell displays the link_location as the jump text.

The Problem with

the HYPERLINK Function

The HYPERLINK function requires special syntax when creating an email hyperlink, and Excel is not exactly generous in the Help Department when creating this form of hyperlink.

Let’s start with a smaller example.

We have a list of email addresses and we want to create dynamic hyperlinks.

If we performed this in a traditional way, we could right-click on a cell and select Link.

In the Insert Hyperlink dialog box, we select the E-Mail Address option from the “Link to:” column on the left.  From here, we can type the email address in the “E-mail address:” field.

The downside of this method is that we can’t place cell references in the “E-mail address:” field.

If we enter a valid email address, notice how Excel automatically adds the special syntax mailto: to the email address.

If we add text to the “Subject:” field, Excel will again insert special syntax in the form of ?subject= to our text.

It is the above special syntax that we need to manually create in our formula.

The Question Mark is a character that defines the beginning of the additional attributes of the email.

Each additional attribute will need to be prefaced with an ampersand “&” symbol.

If we have hundreds of email addresses, it will take a lot of time to create each hyperlink individually.

Or, we could write the following formula:

=HYPERLINK("mailto:" & B5 & "?subject=" & $B$1 & "&cc=" & $B$2 & "&body="&$B$3, "Send email")

Breaking Down Our HYPERLINK Formula

Let’s break the formulas into smaller, more manageable pieces.

=HYPERLINK("mailto:" & B5 & "?subject=" & $B$1 & "&cc=" & $B$2 & "&body="&$B$3, "Send email")

Because we are inserting text in our HYPERLINK function, we need to place the pieces within double-quotes.

We start with the “mailto:” special syntax.

"mailto:"

We add to that the cell reference for the cell holding the email address.  In this case, cell B5.

"mailto:" & B5

If we weren’t adding any custom subject lines or body text, we can move directly to the Friendly_name argument.  We want the cell to display the message “Send email”.

"mailto:" & B5, "Send email"

The final version of the formula appears as follows:

=HYPERLINK("mailto:" & B5, "Send email")

Testing the Link

When we click on the hyperlink, we are presented with the following message window.

We see the email address has been placed in the To field.

Adding a Subject Line

We can either type the subject line directly into the HYPERLINK formula, or we can make the subject line dynamic.

We’ll place some text in cell B1 that says, “Welcome to the team”.

Here’s where it gets a bit tricky.

We need to use an “&” to concatenate the B1 cell reference to the existing “mailto”: & B5 argument.

Because we are trying to add text for the subject field, we must precede the additional argument with special syntax.

"?Subject="

The question mark is the beginning of additional arguments which need to be place in double-quotes.

Next, we concatenate (&) a cell reference (B1) to our subject line.

"?Subject=" & B1

The updated formula appears as follows:

=HYPERLINK("mailto:" & B5 & "?Subject=" & B1, "Send email")

If you plan to repeat the formula to adjacent rows or columns, you will need to lock the B1 reference, so it does not change during a Fill Series operation.

=HYPERLINK("mailto:" & B5 & "?Subject=" & $B$1, "Send email")

Testing the Link

When we click on the hyperlink, we are presented with the following message window.

We see the email address has been placed in the To field and the subject line has been filled with the text from cell B1.

Adding Cc Recipient(s)

If we always want our boss to the Cc’d on all emails, we can add a reference to cell B2 that contains the email address of our boss.

To update the HYPERLINK formula, we will add the following to our Link_location argument:

& "&cc=" & $B$2

NOTE: Because we are concatenating additional special syntax, we need an “&” symbol within the double-quotes.  This will attach the CC instruction to the Subject instruction.

The updated formula appears as follows:

=HYPERLINK("mailto:" & B5 & "?Subject=" & $B$1 & "&cc=" & $B$2, "Send email")

You can visualize the completed Link_location argument as follows:

"mailto:kim@hello.com ? subject=Welcome to the team & cc=boss@hello.com"

Testing the Link

When we click on the hyperlink, we are presented with the following message window.

We see the email address has been placed in the To field, the subject line has been filled with the text from cell B1 and the Cc field has been populated with the boss’ email address from cell B2.

Add Body Text

If we always want to include a message in the Body portion of all emails, we can add a reference to cell B3 that contains the message that will appear in the body of the email.

To update the HYPERLINK formula, we will add the following to our Link_location argument:

& "&body=" & $B$3

The updated formula appears as follows:

=HYPERLINK("mailto:" & B5 & "?Subject=" & $B$1 & "&cc=" & $B$2 & "&body=" & $B$3, "Send email")

We can visualize the completed Link_location argument as follows:

"mailto:kim@hello.com ? subject=Welcome to the team & cc=boss@hello.com & body=It’s great..."

Testing the Link

When we click on the hyperlink, we are presented with the following message window.

We see the updated email with the message from cell B3 in the Body portion of the email.

Adding Line Breaks in the Body Text

Because in-cell carriage returns (ALT-Enter) in an Excel cell are not recognized by Outlook, we must use a special character sequence to tell Outlook to implement a carriage return.  The carriage return character code is ”percentage-zero-A”.

%0A

We need to update the text in our body text reference cell to appear as follows:

Before:

It's great to have you on our team.  We hope you have a wonderful start.

After:

It's great to have you on our team.  %0AWe hope you have a wonderful start.

Testing the Link

When we click on the hyperlink, we are presented with the following message window.

We see the text from cell B3 in the Body portion of the email with a carriage return to break the text into multiple lines.

Time for Mass Production

If we fill the formula down the column, we can click on any of the new HYPERLINK formulas and produce a custom message for our selected user.

By using more creative formulas (see above: Creating the Supporting Formulas) to alter the subject lines and body text, we can create very dynamic emails based on a variety of scenarios.

Word of Warning

An issue to be aware of is the formula character limit of 255 characters.

This means you can’t write a very long email or include too many recipients. One way to optimize the formula is to keep the [friendly name] as short as possible.

Practice Workbook

Feel free to Download the Workbook HERE.

Excel Download Practice file

Learn when you like, where you like.

Check out my bestselling Excel Courses

Learn anytime that fits your schedule.

Download files. Practice. Apply.

Visit Courses
Free Tutorials

9 Comments

  1. Pawan October 31, 2019 at 8:48 pm - Reply

    Too good. Thanks.

    One question, any possibility of inserting signature as well while composing this mail?

    • Chris November 8, 2019 at 10:47 am - Reply

      If you signature is included by default for a new email you should have it.

  2. Kanishk November 1, 2019 at 8:23 am - Reply

    It’s Great formula for short email.
    Any other alternative to send long email id
    Thank you Madam it’s Very Great

    • Chris November 8, 2019 at 10:50 am - Reply

      That’s the limitation in Excel. There is no way around that with a formula.

  3. Tony November 3, 2019 at 11:17 am - Reply

    Failed to do this due to the limitations of 255 characters. Anyway to send long body email with hyperlink? Thanks

    • Chris November 8, 2019 at 10:45 am - Reply

      That’s a limitation in Excel. Unfortunately, there’s no way around that.

  4. Natalia Greene November 8, 2019 at 12:40 am - Reply

    Hi Leila! Thank you so much for all your videos and helpful tips and tricks! I am currently taking your dashboard class and loving it :)
    Regarding dynamic email, it is possible to add attachment in the email dynamically as well? For example have another column with file path location and have this file be attached to the email? What is the syntax for Attachment?
    Thank you,
    Natalia

    • Chris November 8, 2019 at 10:57 am - Reply

      We are glad you like the course Natalia.
      Regarding attachments you can use VBA. If you’re in Leila’s VBA course, check out sections 15 & 18 (to attach any PDF document).

      • Natalia Greene November 8, 2019 at 9:05 pm - Reply

        Thank you Chris! I will check it out :) Not there yet.

Leave A Comment

Share This