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.

January 21, 2022
Finding The Best Online Email Verifier: Unlimited and Free

Yes, we get your frustration. The constant worry of finding a time-saving and accurate online email verifier is real. You have to scour endless online platforms, offering unlimited checks, without burning a hole in your pocket. And why won't you be frustrated? The right tool can do wonders for you and your business’s reputation. Image […]

Read More
January 14, 2022
The Best Free Tool For Email Verification In 2022

Verified email addresses are essential for any company hoping to succeed with email marketing. As a result, it's critical to double-check that your email address list is correct, error-free and that they actually exist. Mistakes happen, and it's not uncommon for your subscribers to make errors in their email addresses while subscribing to your list. […]

Read More
January 8, 2022
Bulk Email Verifiers: Your Complete Guide To Email Checkers

B2B sales reps rely heavily on email marketing. Being one of the fastest and least expensive methods to address the mass target audience, its popularity is unsurprising. However, the key to success here is a sound sender reputation. Your reputation to the internet service providers(ISPs) determines the deliverability proficiency of your bulk emails. Misinformation may […]

Read More
December 29, 2021
How To Do Email Verification For Free In 2022

No one wants to end up in a spam folder, and no one wants to receive spam emails in the first place. In the B2B world, email verification is a must. To avoid delivering your emails to malicious or fraudulent addresses, you must evaluate the quality and deliverability of your email list. Photographer: Stephen Phillips […]

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