How to write a VBA Code that auto-sends an email when values in certain cells change to a SPECIFIC value?

Hello.

I'm having trouble writing this code and getting it to run. It will not run at this point.

What I'm trying to do is have an email auto-send (I do not want to have to click run the macro every time) any time a cell value in column C changes to "POOR".

This is the code I have

Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)

Dim OutApp As Object
    Dim OutMail As Object
    Dim strto As String, strcc As String, strbcc As String
    Dim strsub As String, strbody As String
    Dim myRange As Range
    Dim Cell As Range
    Dim row As Integer
   
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
   
     Set myRange = Range("A2:B7")

    strto = "cweatherford@txtav.com"
    strcc = ""
    strbcc = ""
    strsub = "Send Emails to Parents"
   
    strbody = "Students with poor performance and grade received:" & vbCrLf & vbCrLf
   
      row = 2
    For Each Cell In myRange
        row = row + 1

  If Not Intersect(Target, Cells(row, "C")) Is Nothing Then
   
        If Cells(row, "C").Value = "POOR" Then
          strbody = strbody & Cells(row, "A").Value & " " & Cells(row, "B").Value & vbCrLf
        End If
     Next Cell
     
     strbody = strbody & vbCrLf & "Please send emails to parents."
   
 
    With OutMail
        .To = strto
        .CC = strcc
        .BCC = strbcc
        .Subject = strsub
        .Body = strbody
        .Send
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing

End If

End Sub


Any help is appreciated.
Charli WeatherfordContracts AssociateAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Try something like this...

Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim OutApp As Object
Dim OutMail As Object
Dim strto As String, strcc As String, strbcc As String
Dim strsub As String, strbody As String

If Target.Column = 3 And Target.row > 1 Then
    If Target.Value = "POOR" Then
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        
       
        strto = "cweatherford@txtav.com"
        strcc = ""
        strbcc = ""
        strsub = "Send Emails to Parents"
        
        strbody = "Students with poor performance and grade received:" & vbCrLf & vbCrLf
                
        strbody = strbody & Cells(Target.row, "A").Value & " " & Cells(Target.row, "B").Value & vbCrLf
        
        strbody = strbody & vbCrLf & "Please send emails to parents."
                
        With OutMail
        .To = strto
        .CC = strcc
        .BCC = strbcc
        .Subject = strsub
        .Body = strbody
        .display
        '.Send
        End With
        
        Set OutMail = Nothing
        Set OutApp = Nothing
    End If
End If
End Sub

Open in new window

0
NorieAnalyst Assistant Commented:
Charli

How is the value in the cell being changed? Is it manually or via a formula?
0
Charli WeatherfordContracts AssociateAuthor Commented:
Via a formula!

 =IF(B2>=70,"GOOD","POOR")

I have attached my file here.
if_then_else_finished.xlsm
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Charli WeatherfordContracts AssociateAuthor Commented:
@Subodh:

Why when I click the play button, it pulls up the Macro box and asks me to create a macro? Every time I do Private Sub codes, they never work. It's like it won't recognize it as code??
0
Charli WeatherfordContracts AssociateAuthor Commented:
With the formula above from @Subodh, nothing happens when I change a value to "POOR", either by formula or manually.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
The Sheet Change Event code triggers automatically when you change the cell content explicitly and the code is placed on the Sheet Module not on a Standard Module like Module1.

Right click the Grades Tab --> View Code and paste the code given below into the opened code winow.
The below code will automatically send an email if the Grade entered in column B is less than 70.

Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim OutApp As Object
Dim OutMail As Object
Dim strto As String, strcc As String, strbcc As String
Dim strsub As String, strbody As String

If Target.Column = 2 And Target.row > 1 Then
    If Target.Value < 70 Then
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        
       
        strto = "cweatherford@txtav.com"
        strcc = ""
        strbcc = ""
        strsub = "Send Emails to Parents"
        
        strbody = "Students with poor performance and grade received:" & vbCrLf & vbCrLf
                
        strbody = strbody & Cells(Target.row, "A").Value & " " & Cells(Target.row, "B").Value & vbCrLf
        
        strbody = strbody & vbCrLf & "Please send emails to parents."
                
        With OutMail
        .To = strto
        .CC = strcc
        .BCC = strbcc
        .Subject = strsub
        .Body = strbody        
        .Send
        End With
        
        Set OutMail = Nothing
        Set OutApp = Nothing
    End If
End If
End Sub

Open in new window

if_then_else_finished.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Charli WeatherfordContracts AssociateAuthor Commented:
That works!! Thank you!!
0
Charli WeatherfordContracts AssociateAuthor Commented:
Thanks!!
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Charli!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.