Extracting data is one of the powerful sides of Excel. Read this post and learn how web scraping in Excel saves a lot of time!
Table of contents
When you think of an email, it is likely that a tiny Outlook window pops up on your head; so does on your computer page. That is because we are familiar with Outlook when it comes to sending emails. Microsoft Outlook is a one good tool that arranges everything needed in sending your email. You can easily and safely send lots of emails by only a click. The best part, it makes it possible to send emails from Excel itself.
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
First of all, 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 check mark 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 with 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 from address by clicking “from” dropdown.
4. Edit your email all you want and when it is ready to go, just click send.
It is automatic created mail with attached current excel file you have open, and it can only create one email, there is not much of an automation.
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 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.
To send emails with hyperlink, you will use this formula:
The fundamental element of hyperlink is the email address but the formula is suitable to be extended; therefore you can add subject, cc or email body text into it. You need to use 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 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.
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.
3.Now, when you click on your mailto link, Outlook opens the email window with 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.
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.
a. Start with sub procedure. Type the following code into the window and enter.
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 = "firstname.lastname@example.org"
emilItem.CC = "email@example.com"
emilItem.BCC = "firstname.lastname@example.org; email@example.com"
emilItem.Subject = "Email on Excel."
emilItem.Body = "How to send emails on Excel."
To attach files to the email, type a similar code as follows.
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:
Display the email box before and click the send button yourself.
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 = "firstname.lastname@example.org" emilItem.CC = "email@example.com" emilItem.BCC = "firstname.lastname@example.org; email@example.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.
Sending Email Based On Cell Values
Emails sending 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 the 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
In conclusion, you can either use VBA to send emails from Excel or do it manually with Microsoft Outlook or even without using Outlook. There are quite various ways to do that. It is even possible to create special tools to serve that purpose. Take a look at the template example below, once created by us for sending emails. With the right techniques and the right tools, sending emails on Excel could be easier than you think.