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
Dim Sheets As Worksheet
Dim OutApp, OutMail As Object
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With
lRow = Cells(rows.Count, 6).End(xlUp).Row
For i = 2 To lRow
If (Cells(i, 1)) <> "" And CDate(Cells(i, 3)) - Date <= 7 And CDate(Cells(i, 3)) - Date > 0 And Cells(i, 7) = "" Then '<--------------------------------------------- change this range
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
toList = Cells(i, 4) 'gets the recipient from col D
eSubject = "Reminder to return tools on " & Cells(i, 3)
eBody = "Hello " & Cells(i, 1) & "," & vbNewLine & vbNewLine & _
"Do remember to return " & Cells(i, 6) & vbNewLine & vbNewLine & _
"Sincerely, " & vbNewLine & _
"Admin"
On Error Resume Next
With OutMail
.To = toList
.CC = ""
.BCC = ""
.Subject = eSubject
.Body = eBody
.Display ' ********* Creates draft emails. Comment this out when you are ready
'.Send '********** UN-comment this when you are ready to go live
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
Cells(i, 7) = "Mail Sent " & Date + Time 'Marks the row as "email sent in Column A"
End If
Next i
ActiveWorkbook.Save
With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
End With
End SubHi OZgrid, in the if statement i currently have the condition to be "whatever date is present in the cell in column 3 minus the current date is equal to smaller or equal to 7 days" then the code would execute. However i would like to change the condition to whereby the code would be executed lets say 5 hours before the date in column 3 would this be possible please do advice!