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.

 

[Justinian]

Not sure what vbcrlf is but this code will not run as i get a compile error the second I add "."

 

[Justinian]

Adding those B's.

 

[Logit]

vbCrLf = visual basic Carriage Return Line Feed

It creates in code the same thing as if you were using a TYPEWRITER.

What do you mean B's ?

I ran the email macro here and it runs perfectly.

?????

 

[Justinian]

In your code, you added brackets with a B inside at the beginning and end of the line of code.

 

[Justinian]

I added the vbCrLf and nothing changed.

 

[Logit]

I understand what you are saying now. In that post you saw :

.Body = "John," & vbCrLf & vbCrLf & "A tower boiler tuning reminder alert has been triggered for the following tower:" & _
vbCrLf & vbCrLf & "Tower: " & vbCrLf & "Last Tuning Date: " & vbCrLf & "Tuning Deadline Date: " & vbCrLf & _

This ......................................................................................................................................................................................................... And this
are leftovers from the FORUM SERVER applying HTML CODE to the macro. It is HTML for 'Make This Bold'.

Disregard those altogether. The line of code should be identical to the code you have in your Post #9, highlighted in
BOLD TEXT.

vbCrLf & vbCrLf & "Tower: " & vbCrLf & "Last Tuning Date: " & vbCrLf & "Tuning Deadline Date: " & vbCrLf & _

 

[Justinian]

This code does not bold anthing:

vbCrLf & vbCrLf & "Tower: " & vbCrLf & "Last Tuning Date: " & vbCrLf & "Tuning Deadline Date: " & vbCrLf & _

 

[Logit]

.
Forget the BOLD. Disregard the BOLD. The BOLD has nothing to do with the code.

Now ....

This macro works as is :

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"
.Body = "John," & vbLf & vbLf & "A tower boiler tuning reminder alert has been triggered for the following tower:" & _
vbLf & vbLf & "Tower: " & vbLf & "Last Tuning Date: " & vbLf & "Tuning Deadline Date: " & vbLf & _
.Display ' Use .Send to skip e-mail display
End With
Set OutlookMail = Nothing
Set OutlookApp = Nothing
End Sub

What changes if any are required ?

Download working file : Email MrExcel.xlsb

 

[Justinian]

I must not have explained myself correctly. When I run the code, an Outlook e-mails opens and in the body, it shows like this:

John,

A tower boiler tuning reminder alert has been triggered for the following tower:

Tower:
Last Tuning Date:
Tuning Deadline Date:

What I want to do is to have "Tower", "Last Tuning Date", and "Tuning Deadline Date" appear on bold in the e-mail so they stand out.

 

[Justinian]

I am not sure why Option Explicit is on tis code. Forgive me as I do not know what that means when added to my code.