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"))Featured Course
Black Belt Excel Package
The HYPERLINK Function
The HYPERLINK function has the following structure:
HYPERLINK(link_location, [friendly_name])
- Link_location – required. 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_name – optional. 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:" & B5If 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=" & B1The 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$2NOTE: 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$3The 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”.
%0AWe 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.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
