See all How-To Articles

Send Email From Excel With Formula (No Macros!)

This tutorial demonstrates how to send an email from Excel with a formula (no macros necessary).

 

sendemail example

 

In Excel, you can send an email by running a macro, but if you prefer not to use VBA, keep reading.

Create Hyperlink Formula

In Excel, it’s possible to click on a cell and send an email automatically. This can be done with a formula using the HYPERLINK Function. You can create a hyperlink formula that uses the mailto command and auto-populates fields like to, subject, cc, and the body of an email.

The HYPERLINK formula is made up of the following parts:

Link Location

The Link_Location is the first part of the formula. This example sets mailto, subject, and body.

Mail To

The “mailto:” part of the formula sets the email recipient. This needs to be a valid email address entered into the formula – or, in this example – linked to a cell (C3) that contains a valid email address.

Subject

The “?subject=” can have text between the equals sign and quotation marks, link to a cell, or contain a formula. In this example, there’s a formula to check the status of an order. If the status is 1, use “Thank you for your order” from cell I3.

To do that, use the following IF statement for the subject part of the formula.

Note that there has to be a ? symbol before the word “subject.”

CC

If you want to add a cc to your email, insert it into the formula after the subject in quotation marks. You need an ampersand (&) before the “cc.”. You can either type in the email address to cc the mailto, or you can link it to an external cell as with the subject and body of the email.

Body

The “&body=” part of the formula can contain additional text before the closing quotation mark. You can also create a customized IF statement with a formula:

This IF statement looks at the status of the order, and then adds a message to the end of the email body (Thank you for your business!).

As with the cc, you need an ampersand (&) before the word “body.”

The message at the end (in cell J7) contains the text:

where the “%0A” adds blank lines. The text in the email is displayed as follows:

sendemail thankyou

 

Note: In the formula, the cells that refer to the lookup columns (for the subject and body text) have been put in the formulas as absolute (or “locked”) cell addresses. This lets you copy the formula down to the rest of the order records.

Friendly Name

The final part of the formula is what displays in the hyperlinked cell in Excel – this is the text you click on to create the email. This example uses the text “Send email,” but any text can be entered, as long as it is within quotation marks.

View the formula in the Function Dialog box by clicking on the formula, and then clicking the Insert Function box in the formula bar.

 

sendemail insertfunction

 

The Link_location and Friendly_name are shown in the function arguments.

 

sendemail friendlyname

 

Therefore, the formula:

produces this email:

sendemail cc

 

Copy Formula Down

Since the formula uses absolute cell addresses to refer to the subject and body “lookup” cells, you only need to type in the whole formula once. Then, copy the first cell down to the rest of the rows of your range.

 

sendemail copydown

 

Click any of the “Send email” hyperlinks to send a customized email to the client.

 

sendemail on its way