VBA Code to Email if a certain date is shown

[bamalovell5]

Hey Guys,

I am trying make a VBA code for when the date on the Reminder Date hits it send an email to the people in column R.

 

[Proulxs]

Hi,
Was there a question or you just are asking for a full code custom made for you?

Testing is needed and many adjustement because of unknown variables but here is a start of example code;

Sub EMailReminder()
Dim rng As Range
For Each rng In Range("D7:D100")
If rng = Range("$A$5").Value Then
    Dim xOutApp As Object
    Dim xOutMail As Object
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    On Error Resume Next
    With xOutMail
        .Subject = "Reminder for Fixture Carts"
        .Body = "Cart #" & rng.Offset(0, -2) & "is due"
        .To = rng.Offset(0, 14).Value
        .send
    End With
    Set xOutMail = Nothing
    Set xOutApp = Nothing
Next
End Sub

Until you are done testing I suggest putting the .send line into comment to avoid sending emails :D
I use .offset to grab the cart id 2 columns prior and the email 14 columns later. You would have to adjust that 14 for the next sections to fit distance to R.
I strongly suggest you check if rng is equal to the date to avoid sending a reminder each day you try the dates. Also depending how you get the date in A5 be careful that it does not contain hours/minutes as it would mean the date would be different in A5 and in the other cells even if they look the same (trailing digits).

 

[bamalovell5]

Thanks this worked Great!!