Automatically Generate Reminder E-mail Based on Date

[Justinian]

I have a spreadsheet for preventative maintenance items. These items are to occur one a year so if an inspection happens 1/21/20, the next inspection should occur 1/21/21. I also have conditional formatting so that the row containing the maintenance item is highlighted when today's date is within thirty (30) days of the due date. So on 12/21/20, the row with this item will highlight, reminding me that an inspection is due by 1/21/21.

My question is how can I get Excel is automatically generate an e-mail when the conditional formatting highlights this row? Or how can I get Excel to generate an e-mail when an item is within thirty (30) days of its inspection date? So any date between 12/21/20 and 1/20/21 will generate a reminder to do this inspection.

 

[Logit]

Option Explicit

Sub Tony_Henry_Email()

' Send e-mail to Tony
Dim OutlookApp As Object
Dim OutlookMail As Object
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
On Error Resume Next
With OutlookMail
.To = "john.smith@aol.com"
.Subject = "Tower Boiler Tuning Reminder"
.HTMLBody = "John," & "<br>" & "<br>" & "A tower boiler tuning reminder alert has been triggered for the following tower:" & _
"<br>" & "<br>" & "<B>Tower: </B>" & "<br>" & "<br>" & "<B>Last Tuning Date: </B>" & "<br>" & "<br>" & "<B>Tuning Deadline Date: </B>" & vbLf & _
.Display ' Use .Send to skip e-mail display
End With
Set OutlookMail = Nothing
Set OutlookApp = Nothing
End Sub

Option Explicit ... forces you to declare your variables. It also helps to identify coding errors elsewhere in your macros.
Using it can save you time troubleshooting your code. There is a setting in the OPTIONS section to add it automatically
to every macro window you open.

 

[Justinian]

That is perfect!

Thank you so much!

 

[Logit]

You are welcome.