Automatically Send Emails from Excel Based on Cell Content (2 Methods)

The sample dataset below contains the information of several sales representatives of Armani Group. The name of the sales representatives, their Identification Number, types of Products and the Revenue Earned by the sales representatives are given in Columns B, C, D and E.

Method 1 – Run a VBA Code to Send Email Automatically from Excel Based on Cell Content

Step 1:

• Open a Module. From your Developer tab, go to,

Developer → Visual Basic

• Click on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications will open. Insert a module for applying our VBA code. Go to,

Insert → Module

Step 2:

• The Send Mail from Excel module will appear. Enter theVBA code below,

Dim R As Range
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set R = Intersect(Range("D6"), Target)
If R Is Nothing Then Exit Sub
If IsNumeric(Target.Value) And Target.Value > 400 Then
Call send_mail_outlook
End If
End Sub
Sub send_mail_outlook()
Dim x As Object
Dim y As Object
Dim z As String
Set x = CreateObject("Outlook.Application")
Set y = x.CreateItem(0)
z = "Hello!" & vbNewLine & vbNewLine & _
"Hope you are well" & vbNewLine & _
"Visit our Exceldemy site"
On Error Resume Next
With y
.To = "Address"
.cc = ""
.BCC = ""
.Subject = "send mail based on cell value"
.Body = z
.Display
End With
On Error GoTo 0
Set y = Nothing
Set x = Nothing
End Sub

• Run the VBA. 

Run → Run Sub/UserForm

• If the cell value in cell D6 > 400, an email in Outlook will generate automatically with specific recipients. Click on the Send button to send the email as shown in the image below.

Read More: How to Send Automatic Email from Excel to Outlook

Method 2 – Use Mail Merge Command in Word to Send Email Automatically from Excel Based on Cell Content

Step 1:

• Open your Word file and enter your message.

• From the Mailing tab, go to,

Mailings ➤ Select Recipients ➤ Use an Existing List

• The Select Data Source dialog box will pop out. Select the Excel file where the Email addresses are stored. Click Open.

• The Select Table dialog box will pop up. Choose the sheet and press OK.

Step 2:

• Select the word you need to replace for each mail. In this example, we chose Carl. Under the Mailings tab, go to,

Mailings → Write & Insert Field → Insert Merge Field → Sales_Rep

• It will return the message as shown in the image below.

• Click Preview Results to see a preview of the mail from the recipient’s perspective.

• From the Mailing tab, go to,

Mailing → Finish → Finish & Merge → Send Email Messages

• The Merge to E-mail dialog box will pop up. Choose the header Sales_Rep in the To field and enter your Subject line (Greetings). Press OK to dispatch the mails.

Read More: How to Send Excel File to Email Automatically

Things to Remember

You can open Microsoft Visual Basic for Applications window by pressing Alt + F11.

If a Developer tab is not visible in your ribbon, you need to add it. Go to

File → Option → Customize Ribbon

Download Practice Workbook

Related Articles

• How to Send an Editable Excel Spreadsheet by Email
• How to Send Bulk Email from Outlook Using Excel
• How to Automatically Send Email from Excel Based on Date
• How to Send Email Automatically When Condition Met in Excel
• How to Send Email If Conditions Met in Excel
• How to Send Multiple Emails from Excel Spreadsheet

<< Go Back To Send Email from Excel | Learn Excel