title: How to stop code from sending email when task is marked completed?
body: <p>The following code works to send emails 7 days on or before the past due date but in order for it to run, it needs to be assigned to a button within Excel. When the file is opened, I want it to run the code automatically and send emails to those that have upcoming or overdue tasks. It needs to stop sending emails where tasks have been marked "Completed". </p>
  
  <pre><code>Sub eMail()
   Dim lRow As Integer
   Dim i As Integer
   Dim toDate As Date
   Dim toList As String
   Dim eSubject As String
   Dim eBody As String
  
       With Application
           .ScreenUpdating = False
           .EnableEvents = False
           .DisplayAlerts = False
       End With
  
       Sheets(1).Select
       lRow = Cells(Rows.Count, 5).End(xlUp).Row
  
      Set OutApp = CreateObject("Outlook.Application")
  
       For i = 2 To lRow
  
           If Cells(i, 5) &lt;&gt; "" Then
  
               toDate = Replace(Cells(i, 5), ".", "/")
  
               If Left(Cells(i, 18), 5) &lt;&gt; "Mail" And toDate - Date &lt;= 7 Then
                   Set OutApp = CreateObject("Outlook.Application")
                   Set OutMail = OutApp.CreateItem(0)
  
                   toList = Cells(i, 7)
                   eSubject = "ACTION ITEM - " &amp; Cells(i, 3) &amp; " is due on " &amp; Cells(i, 5)
                   eBody = "NOTICE for " &amp; Cells(i, 6) &amp; vbCrLf &amp; vbCrLf &amp; "You have task(s)coming due or ones that are past due. Please complete your tasks as soon as possible, then notify the Quality Administrator when the task is complete."
  
                   On Error Resume Next
                   With OutMail
                       .To = toList
                       .CC = ""
                       .BCC = ""
                       .Subject = eSubject
                       .Body = eBody
                       .bodyformat = 1
                       '.Display
                       .Display
                   End With
  
                   On Error GoTo 0
                   Set OutMail = Nothing
                   Set OutApp = Nothing
                   Cells(i, 9) = "Mail Sent " &amp; Date + Time
               End If
  
           End If
  
       Next i
  
       ActiveWorkbook.Save
  
       With Application
           .ScreenUpdating = True
           .EnableEvents = True
           .DisplayAlerts = True
       End With
  
   End Sub
  </code></pre>