VBA Send Reminder Email For Outlook Email From Excel

unknownymous

Board Regular
Joined
Sep 19, 2017
Messages
166
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.

02/14/2019 (Today)
DateOwnerSubjectTypeEmailCCStatusx
02/14/2019SeanReport 1Prelimsean@abc.comtaylor@abc.comSent
02/14/2019TaylorReport 2Midtaylor@abc.comben@abc.comSent
03/17/2019BenReport 3Finalben@abc.comsean@abc.comPending

<tbody>
</tbody>


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.
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,758
Office Version
2007
Platform
Windows
I suppose your data is like this:


ABCDEFG
1DateOwnerSubjectTypeEmailCCStatus
202/14/2019SeanReport 1Prelimsean@abc.comtaylor@abc.comPending
302/14/2019TaylorReport 2Midtaylor@abc.comben@abc.comSent
403/17/2019BenReport 3Finalben@abc.comsean@abc.comPending


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

Code:
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
 
Last edited:

unknownymous

Board Regular
Joined
Sep 19, 2017
Messages
166
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

Well-known Member
Joined
Dec 3, 2018
Messages
9,758
Office Version
2007
Platform
Windows
Something like this:

Code:
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

Board Regular
Joined
Sep 19, 2017
Messages
166
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

Well-known Member
Joined
Dec 3, 2018
Messages
9,758
Office Version
2007
Platform
Windows
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

Board Regular
Joined
Sep 19, 2017
Messages
166
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

Well-known Member
Joined
Dec 3, 2018
Messages
9,758
Office Version
2007
Platform
Windows
The date to review is the columan H?

Which of the 2 macro should I adjust?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,758
Office Version
2007
Platform
Windows
Try this and tell me:

Code:
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
 

Forum statistics

Threads
1,086,116
Messages
5,387,921
Members
402,088
Latest member
poppa57

Some videos you may like

This Week's Hot Topics

Top