Can Excel Send Emails? Complete Guide to Send Email from Excel
Do you know that you can send emails from your spreadsheets? Here’s a complete guide to send email from Excel. With definitions, examples and template.
Table of contents
- Non-Macro Way of Sending Emails From Excel
- Macro Way of Sending Emails From Excel
- Sending Email Based On Cell Values
- FINAL WORDS
When you think of an email, it is likely that a tiny Outlook window pops up in your head; so does your computer page. That is because we are familiar with Outlook when it comes to sending emails with Excel. Microsoft Outlook is a good tool that arranges everything needed in sending your email. You can easily and safely send lots of emails with only a click. The best part, it makes it possible to send emails from Excel itself.
Here’s quick tutorial first:
You can make that happen by using macro ways or non-macro ways. We will analyze them both. Check those solutions out and decide which one is more suitable for you. Here we go.
Non-Macro Way of Sending Emails From Excel
Firstly, before you start make sure that you have Microsoft Outlook installed and ready to use; as it is the source that Microsoft Office uses for sending emails. If you have it already; great. Now you will see how you can send the file you are working on as an email. You will do this manually because this is the non-macro way of doing that. Here is how:
1. Go to the toolbar above your Excel sheet, click on the dropdown arrow, and click “email”. Then there will be a checkmark next to it and an email icon will be displayed on the toolbar.
2. Click the email icon and you will have an email window opened:
3. Your file will be automatically attached to the email and the rest is the same as a regular email. You can write your message into the blank below or fill in the parts “to”, “cc” or “subject” and even change the address by clicking the “from” dropdown.
4. Edit your email all you want and when it is ready to go, just click send.
Then, it is automatically created mail with the attached current Excel file you have open, and it can only create one email, there is not much automation.
Mailto Hyperlink Function
However, a little function won’t hurt anybody. Even though it is possible to send an email by hand, as a matter of course, an Excel formula does it more quickly. By using the Mailto hyperlink function, it becomes easier to send certain emails to certain addresses. All you have to do is create the formula and apply it to the relevant cells.
Hyperlink Formula
To send emails with hyperlinks by using Excel, you will use this formula:
=HYPERLINK(link_location, [friendly_name])
The fundamental element of hyperlinks is the email address but the formula is suitable to be extended; therefore you can add a subject, cc, or email body text into it. Moreover, you need to use a special syntax to create the formula. Any additional attribute is defined with a question mark symbol (?) and they begin with an ampersand symbol (&). In that case, if you want to add a subject or body section your formula looks like the following:
=HYPERLINK(“mailto” & C3 & "?subject=" & B2 & "&body=" & B3, “link”)
Then, when you click the link you will get that:
“Link” here refers to what you want your link to be called. You can name it as you prefer.
So, to create your hyperlink formula, follow the instructions below.
1. Into sequential cells, enter your data such as recipient address, subject, or body.
2. Select a blank cell to locate the hyperlink. Copy and paste your hyperlink into the formula bar of the selected cell then press enter to send emails by using Excel.
3. Now, when you click on your Mailto link, Outlook opens the email window with the recipient, subject, and body parts automatically created.
We can make the hyperlink even more complex:
=HYPERLINK("mailto:" & D3 &"?cc="& E3 &"&bcc=" & F3 &"&subject="& G3& "&body=" & H3; "Create Email")
Macro Way of Sending Emails From Excel
The Macro part is a bit more complicated in fact, yet allows you to do more things as well. VBA is the key player here. It makes everything automatic and is able to send emails to multiple users at a time. You can send anything from a cell to anybody and do lots of different things with only a couple of coding. Coding means writing your own functions. Again, ensure first that Outlook is configured on your computer. Then follow the necessary steps to arrange your VBA.
Steps to send emails by Excel using VBA
1. Open Macro on your Excel sheet. You can use the shortcut “Alt+F11” to open the VBA window.
2. You need to set Outlook as your reference. Go to Tools > References and select “Microsoft Outlook 16.0 Object Library” from the available references.
3. Coding part comes to the stage now to send emails by Excel.
a. Start with sub-procedure. Type the following code into the window and enter.
Sub send_email
b. To declare the Outlook application, set an object by using the CreateObject function. Type the relevant codes below.
Dim emailApp As Object
Set emailApp = CreateObject("Outlook.Application")
c. To declare the email item, set an objective for that as well.
Dim emailItem As Object
Set emailItem = emailApp.CreateItem(0)
d. Time for building the email. Enter the codes for each item in the email. If you need to add more addresses, separate them with (;).
emilItem.to = "example1@hello.com"
emilItem.CC = "example2@hello.com"
emilItem.BCC = "example3@hello.com; example4@hello.com"
emilItem.Subject = "Email on Excel."
emilItem.Body = "How to send emails on Excel."
To attach files to the email by using Excel, type a similar code as follows.
emilItem.Attachments.Add ActiveWorkbook.FullName
Instead of entering the addresses or texts one by one, you can type the cells with relevant data and make things easier. Check the example.
emilItem.to = Range("B1").Value
e. Now you can send the email but you have two options for sending.
Either send the email directly with:
emilItem.Send
Or
Display the email box before and click the send button yourself and send emails by using Excel.
emilItem.Display
Total working code:
Sub createEmail()
Dim emailApp As Object
Set emailApp = CreateObject("Outlook.Application")
Dim emailItem As Object
Set emilItem = emailApp.CreateItem(0)
emilItem.to = "example1@hello.com"
emilItem.CC = "example2@hello.com"
emilItem.BCC = "example3@hello.com; example4@hello.com"
emilItem.Subject = "Email on Excel."
emilItem.Body = "How to send emails on Excel."
emilItem.Attachments.Add ActiveWorkbook.FullName
emailItem.Display 'emailItem.Send
End Sub
4. Run your code and your emails will be sent.
Can Excel Send Email Based On Cell Values
Additionally, Emails sent from Excel can be specified based on the cell values. Let’s say that you want to send emails for the cells that are greater than a certain value. If so, you must arrange your VBA according to that info.
Assume that for each value which is greater than 100 an email will be sent and you will check it on cell C3. Right-click on the very cell and select “view code”. Then you need to have a similar code to the one below to paste there.
Dim xRg As Range
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Intersect(Range("C3"), Target)
If xRg Is Nothing Then Exit Sub
If IsNumeric(Target.Value) And Target.Value > 100 Then
Call YourMacro
End If
End If
End Sub
Sub Mail_small_Text_Outlook()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2"
On Error Resume Next
With xOutMail
.To = "email address"
.CC = ""
.BCC = ""
.Subject = "test succeeded"
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub
FINAL WORDS
Finally, yes you can send emails with Excel either use VBA to send emails from Excel or do it manually with Microsoft Outlook or even without using Outlook.
>> Download Email Sender Excel Template
There are quite various ways to do that. It is even possible to create special tools to serve that purpose. Take a look at our Email Sender Excel Template which is created by us for sending emails. With the right techniques and the right tools, sending emails in Excel could be easier than you think.
Recommended Readings:
Complete List of Things You Can Do With Excel
How To Make A Work Schedule in Excel? Complete Guide and Free Template
Related Posts
