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

We store different kinds of information in our Excel worksheet. For instance, it can have Email addresses of important people or other companies. While working with Microsoft Excel, sometimes we need to send Emails. Usually, it’s easy to send an Email from Outlook or Gmail, but when you want to send an Email based on cell content, you can use Microsoft Excel effectively. Today, in this article, we’ll learn two quick and suitable ways to send emails automatically from excel based on cell content effectively with appropriate illustrations.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

2 Suitable Ways to Send Email Automatically from Excel Based on Cell Content

Let’s assume, we have an Excel large worksheet that contains the information about 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 respectively. We will send Emails from Excel based on cell content using the Mail Merge command in MS Word, and VBA Macros also. Here’s an overview of the dataset for today’s task.

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

Now I’ll show how to send emails automatically from Excel based on cell content by using a simple VBA code. It’s beneficial for some particular moments. From our dataset, we will send emails automatically from Excel based on cell content. We will write a code that will send an email automatically if the cell value in cell D6 is greater than 400. Let’s follow the instructions below to send emails automatically from Excel based on cell content!

Step 1:

• First of all, open a Module, to do that, firstly, from your Developer tab, go to,

Developer → Visual Basic

• After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,

Insert → Module

Step 2:

• Hence, the Send Mail from Excel module will appear in front of you. In the Send Mail from Excel module, write down the below VBA code,

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

• Hence, run the VBA To do that, go to,

Run → Run Sub/UserForm

• After running the VBA Code, from now when the cell value in cell D6 > 400 an email in Outlook will generate automatically with specific recipients. We have to just click on the Send button to send the email which has been given in the below screenshot.

Read More: How to Send Email Automatically When Condition Met in Excel

Similar Readings

• Send Reminder Email Automatically from an Excel Worksheet Using VBA
• [Solved]: Share Workbook Not Showing in Excel (with Easy Steps)
• How to See Who Is in a Shared Excel File (With Quick Steps)
• Send Bulk Email from Outlook Using Excel (3 Ways)
• How to Apply Macro to Send Email from Excel with Attachment

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

In our first method, we will use MS Word and the Mail Merge feature in Word to Send Multiple Emails from Excel Spreadsheet. Let’s follow the instructions below to send Emails!

Step 1:

• First of all, open your Word file, and write down your desired message.

• Hence, from your Mailing tab, go to,

Mailings ➤ Select Recipients ➤ Use an Existing List

• As a result, the Select Data Source dialog box will pop out. Subsequently, select the Excel file where the Email addresses are stored. At last, press the Open option.

• After that, the Select Table dialog box will appear in front of you. There, choose your desired sheet and press OK.

Step 2:

• Now, select the word you need to replace for each mail. In this example, choose Carl. Hence, under the Mailings tab, go to,

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

• Thus, it’ll return the message like it’s demonstrated below.

• Moreover, if you want to see a preview of your mail from the recipient’s perspective, click Preview Results.

• After that, from your Mailing tab, go to,

Mailing → Finish → Finish & Merge → Send Email Messages

• Accordingly, the Merge to E-mail dialog box will appear in front of you. Choose the header Sales_Rep in the To field and type your Subject line (Greetings) as required. Lastly, press OK and it’ll dispatch the mails to all the recipients.

Read More: How to Send Excel File to Email Automatically (3 Suitable Methods)

Things to Remember

👉 You can pop up Microsoft Visual Basic for Applications window by pressing Alt + F11 simultaneously.

👉 If a Developer tab is not visible in your ribbon, you can make it visible. To do that, go to,

File → Option → Customize Ribbon

Conclusion

I hope all of the suitable methods mentioned above to send emails automatically from Excel based on cell content with VBA code will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.

Related Articles

• How to Send Email If Conditions Met in Excel (3 Easy Methods)
• How to Send Email from Excel with Body Using a Macro (with Easy Steps)
• Excel Macro: Send Email to an Address in Cell (2 Easy Ways)
• How to Automatically Send Email from Excel Based on Date
• Macro to Send Email from Excel with Body (3 Useful Cases)
• How to Send Email from Excel List (2 Effective Ways)