How to Send Email From Excel Automatically

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.

Sample Data

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:

=HYPERLINK(“mailto:”&B4)

The text “mailto:” is concatenated with the email in cell B4 using the ampersand (&) symbol. The result would be mailto:[email protected].

Inserting Subject

To insert a subject, continue the above formula with subject syntax like shown below:

=HYPERLINK(“MailTo:”&B4&”?subject=”&D4)

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

=HYPERLINK(“MailTo:”&B4&”?subject=”&D4&”&CC=”&C4)

Add Body Content

Just like we entered the CC syntax, we can enter the body content in the HYPERLINK function like below:

=HYPERLINK(“MailTo:”&B4&”?Subject=”&D4&”&cc=”&C4&”&body=”&E4)

Add Text To Display

Finally, let us give a text to display “Click Here”. This is the second attribute (optional) of the cell.

=HYPERLINK(“MailTo:”&B4&”?Subject=”&D4&”&cc=”&C4&”&body=”&E4,”Click Here”)

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.

With this we have reached to the end of this blog. Share your views and comments in the section below.