How to Send Email from Excel with Just One Click?
Updated: Sep 27, 2020
Are you tired of typing an email with similar content repeatedly every day? Why not make it automatic? In this section, you will learn how to write an email in Excel and send it with just one button.
"An arrow can only be shot by pulling it backward. So, when life is dragging you back with difficulties, it means that it's going to launch you into something great. So just focus, and keep aiming." - Paulo Coelho
Microsoft Outlook is the e-mail client application that is available in most of the Office 365 suites. This section shows how to control Outlook application from Excel via VBA. When Excel interacts with Outlook, you don't really have to retype the information manually into Outlook which already exists in Excel. Let us show you one example so that you will understand how it works.
First of all, suppose you have a customer order list with planned delivery date. Build one as follows if you don't have it yet.
In column D, it stores the order number for customers while in column E, it has the planned delivery date for each order. In this example, we will use just one button to check if it is less than 5 days before the planned delivery date for each order. If yes, send an email to that particular customer and tell him or her the order might be delayed. Today is 28-Oct-2019.
You may add a command button "Send Email" on the worksheet. Then copy the below codes to the command button control. That's it.
Private Sub CommandButton1_Click()
Dim OutlookApp As Object
Dim OutlookMail As Object
For i = 2 To 4
If DateDiff("d", Date, ThisWorkbook.ActiveSheet.Cells(i, "E")) < 5 Then
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
.To = ThisWorkbook.ActiveSheet.Cells(i, "C")
.Subject = "Order Delay"
.HTMLBody = "Dear " & ThisWorkbook.ActiveSheet.Cells(i, "B") & "," & _
"<br><br>" & _
"We are sorry to inform you your order " & ThisWorkbook.ActiveSheet.Cells(i, "D") & " will be delayed." & _
"<br><br>" & _
"Excel Magic World"
Set OutlookMail = Nothing
Set OutlookApp = Nothing
The codes loop through the customer list from row 2 to row 4. It checks the planned delivery date against today. If it is less than 5 days, initiate an email via Outlook. As you notice, information like Customer Name, Email and Order Number are automatically populated into the email. It help you avoid thousands of times of "Copy" and "Paste" if you have a long list of customer orders to track. Does that make sense to improve efficiency of your work? Share it if you find it useful so that others will benefit from it too.