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.

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


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

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

  • 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

automatically send emails from excel based on cell content

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

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

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


Similar Readings


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

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

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

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

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.

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

  • 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

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

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

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

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

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo