Macro Autorun based on present Data

[GW89]

I currently have a code written to send and "Email_Notice" based on a value present condition for the last row of data entered. The data is transferred from a user form via a manual button macro. I am only wanting to execute this "Email_Notice" macro when the text value "Fail" is present in one of the multiple cells on the last row of data.

My current code will send the email when I manually start it as I do not have it configured to Loop yet but it will execute the "Email_Notice" sub whether or not "Fail" is within the data range. Can anyone tell me what I am doing wrong? (code below)
______________________________________________________________________________
Sub OOS_Finding()
'***File sends email when "Fail" is present in last row of recorded data. [Columns F-N]

On Error Resume Next
LastRow = Range ("A" & Rows.Count).End(xlUp).Row
Set Target = LastRow
If Target.Text = "Fail" Then Call Email_Notice

End Sub
_________________________________________________________________________________
Sub Email_Notice ()
'***Send Email to Identified Auditors When unit Fails Quality Check
Dim emailApplication As Object
Dim emailItem As Object
Set emailAppplication - CreateObject ("Outlook.Application")
Set emailItem - emailApplication.CreateItem(0)
emailItem.to - "user@email.com
emailItem.Subject = "Test"
emailItem.Body - "Quality Finding"
emailItem.Send
Set emailItem - Nothing
Set emailApplication - Nothing
End Sub
_______________________________________________________________________

 

[NoSparks]

My current code will send the email when I manually start it

Perhaps Autorun by calling the OOS_Finding sub at the end of the macro that tranfers the data to the sheet

I do not have it configured to Loop yet but it will execute the "Email_Notice" sub whether or not "Fail" is within the data range.

maybe something like this

Sub OOS_Finding()
'***File sends email when "Fail" is present in last row of recorded data. [Columns F-N]

Dim LastRow As Long, rng As Range, cel As Range

With ActiveSheet
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    Set rng = .Cells(LastRow, "F").Resize(, 9)
End With
    
For Each cel In rng
    If cel.Value = "Fail" Then
        Call Email_Notice
        Exit For
    End If
Next cel

End Sub

 

[GW89]

Perhaps Autorun by calling the OOS_Finding sub at the end of the macro that tranfers the data to the sheet


maybe something like this

Sub OOS_Finding()
'***File sends email when "Fail" is present in last row of recorded data. [Columns F-N]

Dim LastRow As Long, rng As Range, cel As Range

With ActiveSheet
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    Set rng = .Cells(LastRow, "F").Resize(, 9)
End With
   
For Each cel In rng
    If cel.Value = "Fail" Then
        Call Email_Notice
        Exit For
    End If
Next cel

End Sub

NoSparks,

I tried this but it didn't identify the "Fail" value on the line. While you were working on this I was able to apply an IF statement inline with my raw data to generate a CountValue for "Fail" appearances. I then created the macro below to pull that value and process the Email_Notice macro from that. I was able to add the Call OOS_Finding to the end of my initial Macro for the autorun though so much thanks there as you save me some trial and error time.

Sub OOS_Finding()
'Looks for Integer Value created by imbedded IF statement which COUNTS "Fail" occurences [=COUNTIF((F2:N2),"Fail")]

LastRow = Sheets("Database").Range("A" & Rows.Count).End(xlUp).Row

Target = Range("S" & LastRow).Value

If Target > 0 Then Call Email_Notice

End Sub

 

[NoSparks]

Glad I could help some, good luck with your project