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 = "firstname.lastname@example.org"
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
strbody = strbody & vbCrLf & "Please send emails to parents."
.To = strto
.CC = strcc
.BCC = strbcc
.Subject = strsub
.Body = strbody
Set OutMail = Nothing
Set OutApp = Nothing
Any help is appreciated.