# VBA Send Reminder Email For Outlook Email From Excel



## unknownymous (Feb 13, 2019)

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)DateOwnerSubjectTypeEmailCCStatusx02/14/2019SeanReport 1Prelimsean@abc.comtaylor@abc.comSent02/14/2019TaylorReport 2Midtaylor@abc.comben@abc.comSent03/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.


----------



## DanteAmor (Feb 14, 2019)

I suppose your data is like this:



 ABCDEFG1DateOwnerSubjectTypeEmailCCStatus202/14/2019SeanReport 1Prelimsean@abc.comtaylor@abc.comPending302/14/2019TaylorReport 2Midtaylor@abc.comben@abc.comSent403/17/2019BenReport 3Finalben@abc.comsean@abc.comPending


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 (Feb 14, 2019)

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 (Feb 14, 2019)

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 (Feb 14, 2019)

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 (Feb 14, 2019)

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 (Feb 19, 2019)

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 (Feb 19, 2019)

The date to review is the columan H?

Which of the 2 macro should I adjust?


----------



## unknownymous (Feb 20, 2019)

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


----------



## DanteAmor (Feb 20, 2019)

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
```


----------



## unknownymous (Feb 13, 2019)

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)DateOwnerSubjectTypeEmailCCStatusx02/14/2019SeanReport 1Prelimsean@abc.comtaylor@abc.comSent02/14/2019TaylorReport 2Midtaylor@abc.comben@abc.comSent03/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.


----------



## unknownymous (Feb 20, 2019)

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


----------



## DanteAmor (Feb 20, 2019)

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


----------



## unknownymous (Mar 19, 2019)

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 (Mar 19, 2019)

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 (Mar 19, 2019)

This is brilliant. Thank you so much Dante!


----------



## DanteAmor (Mar 19, 2019)

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


----------



## FARAHSHABAN (Dec 19, 2022)

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,


----------

