You could use this code that check situaation and send email before than you close the workbook.
The code put the word 'sent' in M column to indicate that email has been sent and it's not necessary to send another time.
To use the code right click on the name of your worksheet in the bottom, paste the code and then click on Tools menu, choose Reference and select Microsoft Outlook xx.x Object Library.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim objOutlook As Object
Dim objMailMessage As Outlook.MailItem
Dim emlBody, ctr As Integer
Dim r As Long, lastRow As Long
Dim c As Integer, mySh As Worksheet
Set objOutlook = CreateObject("Outlook.Application")
Set mySh = ThisWorkbook.ActiveSheet
Application.ScreenUpdating = False
lastRow = mySh.Cells(Rows.Count, 1).End(xlUp).Row
For r = 2 To lastRow
ctr = 0
If mySh.Cells(r, 2) <= Int(Now()) - 180 And mySh.Cells(r, "m") = "" Then
For c = 1 To 10
If mySh.Cells(r, c + 2) <> "" Then
ctr = ctr + 1
End If
Next c
End If
If ctr = 10 Then
mySh.Cells(r, "m") = "sent"
'send email
Set objMailMessage = objOutlook.CreateItem(0)
With objMailMessage
'Mail address:
.To = "myEmail.com"
'Mail body:
.Body = "Part number: " & mySh.Cells(r, 1) & " is safely launched, " _
& "it started on date: " & mySh.Cells(r, 2) _
& ", " & Int(Now()) - mySh.Cells(r, 2) & " days ago."
'Mail subject:
.Subject = "Part number " & mySh.Cells(r, 1) & " is safely launched."
'to show email
'.Display
'to send email
.Send '.Attachments.Add ActiveWorkbook.FullName
End With
End If
Next
Application.ScreenUpdating = False
End Sub
Regards,
Antonio
Bookmarks