VBA Send Reminder Email For Outlook Email From Excel

unknownymous

Board Regular
Joined
Sep 19, 2017
Messages
249
Office Version
  1. 2016
Platform
  1. Windows
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:

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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:
Upvote 0
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. :)
 
Upvote 0
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
 
Upvote 0
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. :)
 
Upvote 0
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.
 
Upvote 0
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. :)
 
Upvote 0
The date to review is the columan H?

Which of the 2 macro should I adjust?
 
Upvote 0
The first macro. Yes, H1 is where the current date is located. Thank you.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top