How to Send Email from Excel with Just One Click?

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

With ThisWorkbook.ActiveSheet

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)

With OutlookMail

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

.Display

End With

Set OutlookMail = Nothing

Set OutlookApp = Nothing

End If

Next i

End With

End Sub

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.