Have you ever wondered that we can even send an email from excel using an excel formula by just a simple mouse click? In this blog, we would unlock this technique to automatically send an email using an excel formula in an excel sheet.
In the below image, you can see that there is a list of recipients (MailTo and CC), subject line, and the body text of an email in Excel worksheets.
The purpose is to insert a link in cell F4, F5 and F6 (Click Here), such that the Mail To, CC, Subject line and the Body content gets automatically filled in the default email application. And you just want to bother about clicking “Click Here” text.
Don’t you think it would an amazing excel automation that would save a lot of your time by manually sending emails?
You may be thinking of using the HYPERLINK dialog box (Keyboard Shortcut – Ctrl+K) to send the email. However, the biggest drawback of this feature is that you cannot give a reference to a cell in the HYPERLINK dialog box. You need to type the recipient’s addresses and subject manually in this dialog box one by one.
Surprisingly, excel has provided with the HYPERLINK formula, where we can easily give reference to a cell to send an automatic email to the recipient(s) in Excel. The destination location may be within the existing workbook or outside the workbook.
HYPERLINK Formula Structure
The structure of HYPERLINK Formula is like this – HYPERLINK(link_location, [friendly_name]) where:
- link_location is the path of the destination folder or file or the URL of the web page. It is a required attribute in the formula structure.
- friendly_name denotes the text that you want to display in the lin cell. Unlike the first one, it is an optional attribute.
Email From Excel Using HYPERLINK Formula
As mentioned in the above section, the first attribute of the HYPERLINK formula structure (“link_location”), is where we enter the destination path. Therefore, in the first attribute itself, we need to give the MailTo, CC, Subject as well as Body of the email.
But How to Achieve This – This must be the question in everybody’s mind. To answer this, firstly try using the HYPERLINK dialog box to create a link to email (as shown in the below image).
When you type an email address in the “E-mail Address”, the excel automatically prefixes it with the word ‘mailto:‘. Similarly, the subject line is prefixed by ‘?subject=‘ (Highlighted above). The question mark “?” here means the start of the new attributes (like subject, body, cc, etc.) after mailto. Each new attribute needs to be separated by an ampersand (&) symbol. Using all these, we would now create our dynamic hyperlink to send email in Excel.
Email Addresses (MailTo)
To insert an email address within the HYPERLINK function, enter the following formula in cell F4:
The text “mailto:” is concatenated with the email in cell B4 using the ampersand (&) symbol. The result would be mailto:[email protected].
To insert a subject, continue the above formula with subject syntax like shown below:
The above formula would result in this – mailto:[email protected]?subject=Welcome To Excel Unlocked
Entering CC Email Addresses
Similarly, to add the CC recipients Email addresses, use ampersand followed by &CC= within double inverted commas like below and concatenate it with the reference cell address (C4).
Add Body Content
Just like we entered the CC syntax, we can enter the body content in the HYPERLINK function like below:
Add Text To Display
Finally, let us give a text to display “Click Here”. This is the second attribute (optional) of the cell.
As a result of the above formula, excel would insert the text ‘Click Here’ in blue color with an underline.
Testing HYPERLINK in Excel
Finally, let us now test this complex formula 🙂
Take your mouse cursor over the text in cell F4 and as a result, the mouse cursor changes to a hand like symbol. Click on the “Click Here” text, and the default system mailbox would appear open with all the sections filled. See the image below:
Surprised !! Isn’t it amazing?
But wait, don’t you see something strange in the body text? The original body text in column E in the Excel worksheet had a line break after the text “Hey!!”, but it seems that it did not work over here in the email body section.
Inserting Line Break in Email Body Content
As we saw in the previous section, the line break (Alt + Enter) that we entered in the excel cell did not turn up here in the outlook email body area. This is because Alt+Enter is not a function in the outlook email application.
However, using the combination of special characters, you can achieve this.
The %0A (Percentage-Zero-A) is a line break in Outlook application.
Enter the %0A at relevant place in the body text reference cell where you want to insert a line break in outlook. Below image is self-explanatory.
Mass Email From Excel
If you want to insert more than one recipients in Mail To or CC, it is best to enter all of them in the cell B4 separated by semi-colon (;). This is because, the outlook application uses semi-colon to separate email addresses in To or CC or BCC.
Important To Know This
Before ending up, it is important to keep in mind that any formula in Excel can accept maximum upto 255 characters. So make sure that you do not exceed this limit, else the formula would not work.
And this is the reason, we write the email recipients, subject, and body content in cells and then give the cell reference in the HYPERLINK function instead of writing the subject and body content within the formula itself.
With this we have reached to the end of this blog. Share your views and comments in the section below.