Reminder Mail Trigger Criteria Based

[fareedexcel]

Dear Experts,

I need a solution for a day to day activity where a particular column to be read and accordingly a reminder mail to be sent with a Subject and body of the message in outlook mail. (In total 2 Reminder Mails - One at Day 60 and other at Day 29).

To check the Days remaining to review Column (Column M),

1. If the day reaches 60 days - a reminder mail to be sent to the person as per Email ID (Column F). Subject of the mail - (Column N) and Body of the Mail - (Column O). - Only One Reminder.

2. If the day reaches 29 days - a reminder mail to be sent to the person as per Email ID (Column F). Subject of the mail - (Column N) and Body of the Mail - (Column P).

 

[Logit]

.
Here is code that checks Col A for a date 30 days from current date. You can modify the code to check a second column as well, setting that at 60 days out.

Option Explicit

Sub chkdate()
Dim n As String
Dim Today As Long

n = Sheets("Sheet1").Range("A1").Value

If n < (Now() + 30) Then
    'MsgBox "Time !"
    Send_Mail_From_Excel
End If

End Sub


Sub Send_Mail_From_Excel()
    Dim OutlookApp As Object
    Dim OutlookMail As Object
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookMail = OutlookApp.CreateItem(0)
    'Send Mass Email Using Excel VBA Macro Code
    With OutlookMail
        .to = "youremail@some.com"
        .CC = ""
        .BCC = ""
        .Subject = "30 Day Check"
        .Body = "Time to check the date !"
        '.Send  ' or just put .Send to directly send the mail instead of display
        .Display
       
    End With
    Set OutlookMail = Nothing
    Set OutlookApp = Nothing
End Sub

 

[fareedexcel]

.
Here is code that checks Col A for a date 30 days from current date. You can modify the code to check a second column as well, setting that at 60 days out.

Option Explicit

Sub chkdate()
Dim n As String
Dim Today As Long

n = Sheets("Sheet1").Range("A1").Value

If n < (Now() + 30) Then
    'MsgBox "Time !"
    Send_Mail_From_Excel
End If

End Sub


Sub Send_Mail_From_Excel()
    Dim OutlookApp As Object
    Dim OutlookMail As Object
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookMail = OutlookApp.CreateItem(0)
    'Send Mass Email Using Excel VBA Macro Code
    With OutlookMail
        .to = "youremail@some.com"
        .CC = ""
        .BCC = ""
        .Subject = "30 Day Check"
        .Body = "Time to check the date !"
        '.Send  ' or just put .Send to directly send the mail instead of display
        .Display
      
    End With
    Set OutlookMail = Nothing
    Set OutlookApp = Nothing
End Sub

The code should check only Column M, and when the day comes to 60 and 29. A mail to be trigerred. If the status (Column H) is "Completed". Then the reminder mail should not action

 

[Logit]

Yes the macro code provided can be edited to fit your needs.