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.

 

[unknownymous]

No email was sent and all are still in "Pending" form.

 

[DanteAmor]

In column G you must have "Pending" and in column H you must have today's date

 

[unknownymous]

You're right Dante.

See example for a clearer version if it helps:

H1: 03/19/2019 ----- Where the date today is located

Column A Column G
A2: 03/19/2019 G2: Pending
A3: 03/19/2019 G3: Pending
A4: 03/20/2019 G4: Pending


For this example, macro will only send a reminder if "A" column matches with H1. So in the example above, A4 will remain as Pending.

Thanks in advance for the help.

 

[DanteAmor]

Try

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, "A").value = range("H1").value 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

 

[unknownymous]

This is brilliant. Thank you so much Dante!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[FARAHSHABAN]

Hello dear

DanteAmor,​

The code works great for sending out emails. Can I kindly use it for sending out Outlook Appointment/Invite instead of an email? I hope you can help please.

Thank you,

 

[Benurahman]

Hello Dante,
Thank you so much for your solution.
My issue is similar to this one.
My question is:
1. Can this be automated like, the reminder will be sent even if the workbook is closed? (Not using the Power Automate).
2. I am using the last Macro you posted, and seeing that only reminders are sent according to present date and status is pending. If the date is previous and status is pending (if the workbook has not been opened for some times), then no reminders are sent.

So basically if my Point 1. is solved then I do not need a solution of Point 2. Or else can you please give me a solution that present and previous date of "Pending" status will get an email when user open or close the workbook?

 

[Kuerguan]

Hi All

I have a simular query but need it to look at payments from staff for a lotto syndicate.
I have a spreadsheet set up in columns and would like the ability to send a weekly email to those that have not got a value in the column for the specified date as a reminder.

I am learning and have done an emailing macro previously ( extremely messy ) but only using one column value not the 10 that i have here.