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.
The following is a step by step guide on how to send an email based on cell value using Microsoft Excel.
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.
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
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
.To = "Email Address"
.CC = ""
.BCC = ""
.Subject = "send by cell value test"
.Body = xMailBody
.Display 'or use .Send
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
Do make sure to leave the quote marks at the beginning and end, out of the code.
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.
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.