All,
Below is a copy of the code that I am using to try to automatically send an email to a list of people if any value between I14 and l:400 meets or exceeds the value in F10. I am very close to getting it to work, as it will send an email if I14 exceeds F10, but if the first time the value exceeds is past I14 it will not send the email. I have a For loop in it which will work if I remove the exit for statement, but then I get 386 emails. Clearly, that is not what I want. I have very limited programming knowledge and would love some assistance on this. Thanks 
Sub auto_open()
ThisWorkbook.Worksheets("OUTTIME LOG").OnEntry = "Update"
End Sub
Sub Update()
Dim KeyCells As String
KeyCells = "A14:A400, B14:B400, C14:C400, D14:D400, E14:E400, G14:G400, H14:H400"
If Not Application.Intersect(ActiveCell, Range(KeyCells)) Is Nothing Then Decider
End Sub
Sub Decider()
Dim cell As Range
Dim MaxOutTime As Integer
Set wb1 = ActiveWorkbook
MaxOutTime = wb1.Sheets("OUTTIME LOG").Range("F10").Value
For Each cell In wb1.Sheets("OUTTIME LOG").Range("I14:I400")
If (cell.Value > MaxOutTime) Then
Mail_workbook_Outlook_2
Exit For
End If
Next cell
End Sub
Sub Mail_workbook_Outlook_2()
'Working in 2000-2007
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim OutApp As Object
Dim OutMail As Object
Set wb1 = ActiveWorkbook
If Val(Application.Version) >= 12 Then
If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will" & vbNewLine & _
"be no VBA code in the file you send. Save the" & vbNewLine & _
"file first as xlsm and then try the macro again.", vbInformation
Exit Sub
End If
End If
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Make a copy of the file/Open it/Mail it/Delete it
'If you want to change the file name then change only TempFileName
TempFilePath = Environ$("temp") & "\"
TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
FileExtStr = "." & LCase(Right(wb1.Name, _
Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))
wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = "[email protected]"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add wb2.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
On Error GoTo 0
wb2.Close SaveChanges:=False
'Delete the file
Kill TempFilePath & TempFileName & FileExtStr
Set OutMail = Nothing
Set OutApp = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
Exit For
End Sub
Bookmarks