• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 54
  • Last Modified:

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.
0
Charli Weatherford
Asked:
Charli Weatherford
  • 5
  • 3
1 Solution
 
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
 
NorieVBA ExpertCommented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now