VBA Send Reminder Emails Based on Cell Dates

[ScottWUK]

Hey Everyone,
I wonder if you can help me with some VBA for sending out reminder emails based on cell contents. I have a worksheet that looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Role[/TD]
[TD]Name[/TD]
[TD]Manager Name[/TD]
[TD]Manager Email[/TD]
[TD]Status[/TD]
[TD]Opp Date[/TD]
[TD]Recruitment Deadline[/TD]
[TD]1st Reminder Email Sent[/TD]
[TD]2nd Reminder Email Sent[/TD]
[/TR]
[TR]
[TD]Officer[/TD]
[TD]Bill[/TD]
[TD]Sarah[/TD]
[TD]sarah@sarah.com[/TD]
[TD]Closed[/TD]
[TD]01/02/2018[/TD]
[TD]20/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Assistant[/TD]
[TD]Bob[/TD]
[TD]Darren[/TD]
[TD]darren@darren.com[/TD]
[TD]Open[/TD]
[TD]04/05/2018[/TD]
[TD]06/04/2018[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I want to be able to send an email to the "manager email" if the opportunity date has passed (todays date) and an email hasnt been sent yet. I would then like to send a 2nd reminder email 14 days after the first email was sent. Could anyone help with some VBA for this? I have a set of code already but I just dont have the knowledge/experience to bend this to my will! Below is the code in case it gives you anything to work with

Sub SendEMail()

    Dim OutlookApp As Outlook.Application
    Dim MItem As Outlook.MailItem
    Dim cell As Range
    Dim Subj As String
    Dim EmailAddr As String
    Dim Recipient As String
    Dim Msg As String
    
    'Create Outlook object
    Set OutlookApp = New Outlook.Application
    
    'Loop through the rows
    For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeVisible)
        If cell.Value Like "?*@?*.?*" And _
        LCase(Cells(cell.row, "C").Value) = "active" Then
            EmailAddr = EmailAddr & ";" & cell.Value
        End If
    Next
    
    Msg = "Dear Task Manager,"
    Subj = ""
    
    'Create Mail Item and view before sending
    Set MItem = OutlookApp.CreateItem(olMailItem)
    With MItem
        .To = ""
        .BCC = EmailAddr
        .Subject = Subj
        .Body = Msg
        .Display
    End With


End Sub

Many thanks in advance!

 

[ScottWUK]

Would appreciate some help with this if anyone has any ideas please!


Thanks in advance