VBA Send Reminder Email For Outlook Email From Excel

[unknownymous]

Hi Excel gurus,

Do you have any thoughts on how to get an alert based from due date (date today) from excel to outlook? I can't seem to find a definite one.

See below what I have on excel sheet.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]02/14/2019 (Today)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Owner[/TD]
[TD]Subject[/TD]
[TD]Type[/TD]
[TD]Email[/TD]
[TD]CC[/TD]
[TD]Status[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]02/14/2019[/TD]
[TD]Sean[/TD]
[TD]Report 1[/TD]
[TD]Prelim[/TD]
[TD]sean@abc.com[/TD]
[TD]taylor@abc.com[/TD]
[TD]Sent[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02/14/2019[/TD]
[TD]Taylor[/TD]
[TD]Report 2[/TD]
[TD]Mid[/TD]
[TD]taylor@abc.com[/TD]
[TD]ben@abc.com[/TD]
[TD]Sent[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03/17/2019[/TD]
[TD]Ben[/TD]
[TD]Report 3[/TD]
[TD]Final[/TD]
[TD]ben@abc.com[/TD]
[TD]sean@abc.com[/TD]
[TD]Pending[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The sample body email sent to Sean:

------------------------------------------------------------------------------------------
Date: 02/14/2019
Email: sean@abc.com
CC:taylor@abc.com
Title: Report 1 - Prelim

Body:

Hi Sean,

This is to remind you that Report 1's Prelim Report is due today.


Cheers!

----------------------------------------------------------------------------------

Once the email was generated, the status cell on excel sheet will change from "Pending" to "Sent"


Any help will be much appreciated.

 

[DanteAmor]

I suppose your data is like this:

	A	B	C	D	E	F	G
Sean	Report 1	Prelim	sean@abc.com	taylor@abc.com	Pending
Taylor	Report 2	Mid	taylor@abc.com	ben@abc.com	Sent
Ben	Report 3	Final	ben@abc.com	sean@abc.com	Pending

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]
[TD="bgcolor: #92D050"]Date[/TD]
[TD="bgcolor: #92D050"]Owner[/TD]
[TD="bgcolor: #92D050"]Subject[/TD]
[TD="bgcolor: #92D050"]Type[/TD]
[TD="bgcolor: #92D050"]Email[/TD]
[TD="bgcolor: #92D050"]CC[/TD]
[TD="bgcolor: #92D050"]Status[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]
[TD="align: right"]02/14/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]
[TD="align: right"]02/14/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]
[TD="align: right"]03/17/2019[/TD]

Try this code, it reviews each status and if it is pending then send the mail.

Sub Send_Reminder()
    Dim wStat As Range, i As Long
    Dim dam As Object
    
    For Each wStat In Range("G2", Range("G" & Rows.Count).End(xlUp))
        If wStat.Value = "Pending" Then
            i = wStat.Row
            Set dam = CreateObject("Outlook.Application").CreateItem(0)
            dam.To = Range("E" & i).Value
            dam.Cc = Range("F" & i).Value
            dam.Subject = Range("C" & i).Value & " - " & Range("D" & i).Value
            dam.Body = "Hi " & Range("B" & i).Value & "," & vbCr & vbCr & _
                       "This is to remind you that " & Range("C" & wStat.Row).Value & " - " & Range("D" & i).Value & " " & _
                       "Report is due today." & vbCr & vbCr & _
                       "Cheers!"
            '
            dam.Send
            wStat.Value = "Sent"
        End If
    Next
    MsgBox "Sent items"
End Sub

 

[unknownymous]

Works like a charm Dante! By the way, what If I want to only send reminder which are based on date today only like dated 02/14/2019 and 03/17/2019 will be send out when its due (same day to date). Thanks for any help.

 

[DanteAmor]

Something like this:

Sub Send_Reminder()
    Dim wStat As Range, i As Long
    Dim dam As Object
    
    For Each wStat In Range("G2", Range("G" & Rows.Count).End(xlUp))
        i = wStat.Row
        If Cells(i, "A").Value = Date And wStat.Value = "Pending" Then
            Set dam = CreateObject("Outlook.Application").CreateItem(0)
            dam.To = Range("E" & i).Value
            dam.Cc = Range("F" & i).Value
            dam.Subject = Range("C" & i).Value & " - " & Range("D" & i).Value
            dam.body = "Reminder"
            '
            dam.Send
        
        ElseIf wStat.Value = "Pending" Then
            
            Set dam = CreateObject("Outlook.Application").CreateItem(0)
            dam.To = Range("E" & i).Value
            dam.Cc = Range("F" & i).Value
            dam.Subject = Range("C" & i).Value & " - " & Range("D" & i).Value
            dam.body = "Hi " & Range("B" & i).Value & "," & vbCr & vbCr & _
                       "This is to remind you that " & Range("C" & wStat.Row).Value & " - " & Range("D" & i).Value & " " & _
                       "Report is due today." & vbCr & vbCr & _
                       "Cheers!"
            '
            dam.Send
            wStat.Value = "Sent"
        End If
    Next
    MsgBox "Sent items"
End Sub

 

[unknownymous]

Thanks Dante but it's not working. It still sends out email for the line dated 03/17/2019 which shouldn't. The macro will send only email to those line which is based on date today (example is 02/14/2019). I have a date (today) indicated on column H1, maybe we can use it as a reference.

 

[DanteAmor]

Better tell me what I have to check in column G (status) and what I have to check in column H (date) and in what cases it is sent and what data goes in the mail. It also explains each of the possible cases.

 

[unknownymous]

Hi Dante,

Thanks a lot for helping me on this one.

At first, all status in G column are in "Pending" format. What the macro will do is to send out email reminder based on those which are due today which is in our example as of Feb 14 . So on dates not dated Feb 14, no email will be sent out.

 

[DanteAmor]

The date to review is the columan H?

Which of the 2 macro should I adjust?

 

[unknownymous]

The first macro. Yes, H1 is where the current date is located. Thank you.

 

[DanteAmor]

Try this and tell me:

Sub Send_Reminder()
    Dim wStat As Range, i As Long
    Dim dam As Object
    
    For Each wStat In Range("G2", Range("G" & Rows.Count).End(xlUp))
        If wStat.Value = "Pending" Then
            i = wStat.Row
            if cells(i, "H").value = date then
            Set dam = CreateObject("Outlook.Application").CreateItem(0)
            dam.To = Range("E" & i).Value
            dam.Cc = Range("F" & i).Value
            dam.Subject = Range("C" & i).Value & " - " & Range("D" & i).Value
            dam.Body = "Hi " & Range("B" & i).Value & "," & vbCr & vbCr & _
                       "This is to remind you that " & Range("C" & wStat.Row).Value & " - " & Range("D" & i).Value & " " & _
                       "Report is due today." & vbCr & vbCr & _
                       "Cheers!"
            '
            dam.Send
            wStat.Value = "Sent"
            end if
        End If
    Next
    MsgBox "Sent items"
End Sub