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