How to send emails from Excel

When you hear the word 'email', many of you will gravitate towards Outlook as your go-to tool (other email clients are available).

However, have you ever thought about organising, structuring, and sending emails from Excel?

Admittedly, with a little help from Outlook, it's possible and can certainly save you time in the office.

Let's dive into how you can construct your very own customisable template so you can tailor the email you send to a set of people.

Example

A list of employee names alongside their respective email addresses are present in A2:B5.

These two fields are variables, as they change for each person. However, there are several other components that must be considered.

The text for the subject and body, as well as the CC email address are fixed values, as they apply across the board. These are placed at the side ready to be referenced.

Back in the original data, we need to add a column with a 'Send Email' link adjacent to each name and email address.

To do this, let's use the HYPERLINK function and string each part of the email together with the help of parameters, which indicate what part belongs to what. 

They are:‍

mailto:
‍The recipient email address‍

?subject=
‍The email subject text‍

&body=
The email body text‍

&cc=
‍The CC recipient email address 

In the link_location argument, each parameter must be specified between quotation marks, while the ampersand (&) concatenates it with the corresponding range or cell reference.

For example, "mailto:"&B2:B5 references the email addresses in B2:B5.

"?subject="&E2 points to the subject text in E2.

After including the rest of the parameters and a couple of additional strings, the email structure is now complete.

In the second argument, [friendly_name], putting "Send Email" ensures the clickable links appear with this text.

The final formula in C2 is:

=HYPERLINK(
"mailto:"&B2:B5
&
"?subject="&E2
&
"&body="&"Hi"
&
A2:A5&","
&
E4
&
"&cc="
&
E11,
"Send Email"
)

These are the references that specify where the values are stored:

• B2:B5 — Email
• E2 — Subject
• A2:A5 — Name
• E4 — Body
• E11 — CC

To ensure all the links are identifiable, use Format Painter to copy the formatting from the first to the rest.

After clicking the first 'Send Email' link, Outlook opens with the email prepared, except there are no line breaks in the body text, despite including them in E4.

This is because they're not recognised in the cell. However, there is a way to combat this problem. 

The solution is to include %0A twice consecutively on every other line to create separation between the parts. This special code represents a line break.

When 'Send Email' is tried again, the issue is resolved. The emails can now be sent!

Watch the video

Conclusion

Who would have thought Excel could be so useful for sending emails? I didn't, but I should have known better.

As clever as it is, of course there are limitations to the method I showed you, such as how long your emails can be, the inability to send multiple emails at once, and the lack of support for attachments.

These are possible to overcome with the use of VBA, but that's for another time.

For simple requirements, it does everything you need and is much better than going through the tedious task of typing each email out individually.

So go on — give it a go and let us know how you get on!