Exceljet

Quick, clean, and to the point

Send email with formula

Excel formula: Send email with formula
Generic formula 
=HYPERLINK("mailto:"&email&"?"
&"cc="&cc
&"&subject="&subject
&"&body="&body,
"link text")
Explanation 

To send an email with a formula, you can build a "mailto:" link with the HYPERLINK function. In the example shown, the formula in G5 is:

=HYPERLINK("mailto:"&C5&"?"
&"cc="&D5
&"&subject="&E5
&"&body="&F5,
"link")

When the link is clicked in Excel, the default email client will create a new email with the information supplied. The link text ("link") can be customized as desired.

Note: the formula above is entered with line breaks for better readability.

Mailto link protocol

The mailto link protocol allows five variables as shown in the table below:

Variable Purpose
mailto: The primary recipient(s)
&cc= The CC recipient(s)
&bcc= The BCC recipient(s)
&subject= The email subject text
&body= The email body text

Notes: (1) separate multiple email addresses with commas. (2) Not all variables are required.

The variables are presented as "query string parameters", delimited with the ampersand (?) character. For example, a fully formed mailto: link in an HTML document might appear like this:

Example mailto link

When a user click the link text, the default email application opens a new email with the variables filled in.

How this formula works

In Excel, the HYPERLINK function can be used to create links. The basic syntax is:

=HYPERLINK("link","link text")

The link itself is a text string that represents a valid link. The link text (called "friendly name" in Excel) is the text displayed to a user.

Ultimately, the goal for the formula in G5 is to build a string like this:

mailto:aya@aa.com?cc=bb@bb.com&subject=subject&body=body

Because the mailto link use several pieces of information, it must be assembled with concatenation.

The formula is bit tricky. While the ampersand is the operator for concatenation in Excel, it is also used to delimit the mailto link parameters (cc, bcc, subject, etc.). This means that some ampersands (&) are used to join text in the formula, and some are embedded in the final result. In the code below, the ampersands in yellow are used for concatenation in Excel. The white ampersands are embedded in the final result:

=HYPERLINK("mailto:"&C5&"?"
    &"cc="&D5
    &"&subject="&E5
    &"&body="&F5,
    "link")

Empty mailto parameters

For the sake of simplicity, the formula above does not try to exclude empty parameters from the final result. In quick testing with Gmail and Outlook, missing parameters seem to be handled ignored gracefully. The behavior of other email applications may vary.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.