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!
- 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
- 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.
- 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!
- 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.
- 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.
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
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.
- 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)