This tutorial demonstrates how to send an email from Excel with a formula (no macros needed).
In Excel, you can send an email by running a macro, but if you’d rather not use VBA, read on.
Create Hyperlink Formula
In Excel, it’s possible to click 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 automatically fills in fields such as to, subject, cc and the body of an email.
=HYPERLINK(“mailto:” & C3 & “?subject=”&IF(D3=1,$I$3,IF(D3=2, $ I$4,$I$5)) &”&cc=abclimited@abc.com”&”&body=”&SI(D3=1,$J$3,SI(D3=2,$J$4,$J$5&E3)) & ” ” &$J$7, “Send Email”)
The HYPERLINK formula is made up of the following parts:
Location of the link
The Location_of the link is the first part of the formula. This example sets mailto, subject, and body.
Mail To
The “mailto:” part of the formula sets the recipient of the email. Must be a valid email address entered into the formula or, in this example, linked to a cell (C3) containing a valid email address.
“mailto:” & C4
Subject
The “?subject=” can have text between the equal sign and quotes, link to a cell, or contain a formula. In this example, there is 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 this, use the following IF statement for the subject part of the formula.
“?subject=”&IF(D4=1,$I$3,IF(D4=2,$I$4,$I$5))
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 of the subject in quotation marks. You need an ampersand (&) before “cc”. You can type the email address to cc the mailto, or you can link it to an external cell like with the subject and body of the email.
“&cc=abclimited@abc.com”
Body
The The “&body=” part of the formula may contain additional text before the closing quotation mark. You can also create a custom IF statement with a formula:
“&body=”&IF(D5=1,$J$3,IF(D5=2,$J$4,$J$5 &E5 )) & ” ” &$J$7
This IF statement parses the order status and then adds a message to the end of the body of the email (Thank you for your business! ).
As with cc, you need an ampersand (&) before the word “body”.
The message to the end (in cell J7) contains the text:
“%0A%0AThank you for your business! %0A %0A Best regards%0A ABC Limited”
where “%0A” add blank lines. The email text is displayed as follows:
Note: In the formula, cells that reference the lookup columns ( for subject and body text) have been put into formulas as absolute (or “locked”) cell addresses. This allows you to copy the formula to the rest of the order records.
Friendly name
The final part of the formula is what is displayed in the hyperlinked cell in Excel: this is the text you click to create the email. This example uses the text “Send Email”, but any text can be entered, as long as it is enclosed in quotes.
See the formula in the Function Dialog box. by clicking the formula, and then clicking the Insert Function box in the formula bar.
The Link_Location and Friendly_Name are displayed in the function arguments.
Therefore, the formula:
=HYPERLINK(“send to:” & C3 & “?subject=”&IF(D3=1,$I$3,IF(D3=2,$I$4,$I$5))) &” &cc=abclimited@abc. com”&”&body=”&IF(D3=1,$J$3,IF(D3=2,$J$4,$J$5&E3)) & ” ” &$J$7, “Send corr eo email”)
produces this email:
Copy formula down
Since the formula uses absolute cell addresses to refer to the subject and body “lookup” cells, you only need to type the entire formula once, then copy the first cell to the rest of the rows in your range.
Click any of the “Send Email” hyperlinks to send a personalized email to the customer.
.