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:

Role	Name	Manager Name	Manager Email	Status	Opp Date	Recruitment Deadline	1st Reminder Email Sent	2nd Reminder Email Sent
Officer	Bill	Sarah	sarah@sarah.com	Closed	01/02/2018	20/01/2018
Assistant	Bob	Darren	darren@darren.com	Open	04/05/2018	06/04/2018

<tbody>
</tbody>

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