Automatically Send Emails From Excel Based on Cell Content.

August 14, 2019

Sending emails from a particular content of a cell in Microsoft Excel is no easy task for most. Here you’ll learn how to do it with relative ease. Let’s say you want to send an email through Outlook to a certain recipient based on a value in an Excel’s cell. Below we’ll be following the example with the cell D7, if for instance, the value of the cell is over 200, then an email is created and send automatically.

The method described below is VBA, as it allows a quick resolution to this process and it is very easy to follow. If you’ve never done something like this before, then this is the best time to learn.

Automatically send email based on a cell content with a VBA code in Microsoft Excel.

The following is a step by step guide on how to send an email based on cell value using Microsoft Excel.

Step 1.

Open Microsoft Excel and then open the worksheet you need to send the email based on its cell value. In this example we are using the D7 cell, right click the sheet tab and then select View Code. You should see something like the image below.

cell content with a VBA code in Microsoft Excel

Step 2.

Following there will be a popping up of Microsoft Visual Basic for Applications, in it you need to paste the following VBA code into the sheet code window.

“Dim xRg As Range
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Intersect(Range("D7"), Target)
If xRg Is Nothing Then Exit Sub
If IsNumeric(Target.Value) And Target.Value > 200 Then
Call Mail_small_Text_Outlook
End If
End Sub
Sub Mail_small_Text_Outlook()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2"
On Error Resume Next
With xOutMail
.To = "Email Address"
.CC = ""
.BCC = ""
.Subject = "send by cell value test"
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub”

Do make sure to leave the quote marks at the beginning and end, out of the code.

Step 3.

Now, you should press Alt + Q keys together to close the Microsoft Visual Basic for Applications window.

With the code in place whenever the value of the D7 cell is higher than 200, an email for the intended recipient will be created automatically using Microsoft Outlook. You can click the Send button to send it. You should see something like this.

cell content with a VBA code in Microsoft Excel

Note of caution.

  • Remember that this VBA code will only work when you’re running your Outlook client.
  • Be careful of entering text value in the specified cell.
  • In the code from step 2, remember that the cell is specified as D7 and the value as value>200. This can, and should be, changed to suit your needs.
  • Be sure to replace the email address in the code with the actual email address your recipient has. This is in the code from step 2.
  • In the code provided you can specify Cc and Bcc recipients in case you need to.
  • Do make sure to change the subject of the email form the code provided.

Conclusion.

We have provided a quick and easy way to automatically send emails from excel based on cell content. Now you can begin to save time and effort by automatizing the boring work using Microsoft Excel. If you like this article and would like to know more from us please be sure to visit out blog to gain insights about email marketing and more.

July 1, 2022
How To Ping An Email Address To Validate It

Do you ever wonder whether an email address is a legitimate or a false email address when it bounces back? Probably the easiest method to handle this is to ping an email address, and in this article, we will demonstrate how to ping and validate an email address. Validating emails by pinging different addresses is […]

Read More
June 29, 2022
The Best Email Services To Not Ask for Phone Verification

With everything becoming tech-based around us, it only makes sense that we turn to the internet for required services and needs. However, that is also when we truly feel the importance of "Emails." Today, an email account is the one thing that can help you sign up for any service or platform. It is one […]

Read More
May 16, 2022
How Email Verification Works: The Ultimate Guide

Following the creation of your email lead list, email verification is one of the most critical phases in any email marketing campaign. If you don't verify your emails - all the hard work you put into your email marketing campaign essentially becomes worthless. Now you, along with many others, might be scared of your email […]

Read More
May 14, 2022
How to Clean Out Old Emails In Bulk From Your Gmail Account

If you have been getting notified of your account storage space recently, and your Gmail looks something like this, then take this as a sign to clean up your old emails. If you’ve been running out of space in your google account, you have two options to fix it. First, you can start paying for […]

Read More
envelope-ophoneinstagramgraduation-hatstorephone-handsetchart-barseartharrow-right