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"))