Thread: How to trigger excel to send an email on a date instead of a number value?

I'm trying to set up an excel database to automatically send out reminder emails when due dates for my employees' recertifactions are 2 weeks away. Actually sending an email seems to work prefectly with the code I am using so I didn't list that portion, but I do not know how to change the trigger for sending an email from numeric (currently is target cell>200) to instead equaling a date (specifically, trying to get it to send when cells equal current date+14). I even think with the code below that the trigger could just be changed to when target cell>current date+14 so not as much would have to be changed.

Thanks in advance.

Option Explicit

Private Sub Worksheet_Calculate()
    Dim FormulaRange As Range
    Dim NotSentMsg As String
    Dim MyMsg As String
    Dim SentMsg As String
    Dim MyLimit As Double

    NotSentMsg = "Not Sent"
    SentMsg = "Sent"

    'Above the MyLimit value it will run the macro
    MyLimit = 200

    'Set the range with the Formula that you want to check
    Set FormulaRange = Me.Range("B8")

    On Error GoTo EndMacro:
    For Each FormulaCell In FormulaRange.Cells
        With FormulaCell
            If IsNumeric(.Value) = False Then
                MyMsg = "Not numeric"
            Else
                If .Value > MyLimit Then
                    MyMsg = SentMsg
                    If .Offset(0, 1).Value = NotSentMsg Then
                        Call Mail_with_outlook1
                    End If
                Else
                    MyMsg = NotSentMsg
                End If
            End If
            Application.EnableEvents = False
            .Offset(0, 1).Value = MyMsg
            Application.EnableEvents = True
        End With
    Next FormulaCell

ExitMacro:
    Exit Sub

EndMacro:
    Application.EnableEvents = True

    MsgBox "Some Error occurred." _
         & vbLf & Err.Number _
         & vbLf & Err.Description

End Sub

Not tested but maybe:

Private Sub Worksheet_Calculate()
Dim FormulaRange As Range
Dim NotSentMsg As String
Dim MyMsg As String
Dim SentMsg As String
Dim MyLimit As Date

NotSentMsg = "Not Sent"
SentMsg = "Sent"

'Above the MyLimit value it will run the macro
MyLimit = Date + 14

'Set the range with the Formula that you want to check
Set FormulaRange = Me.Range("B8")

On Error GoTo EndMacro:
For Each FormulaCell In FormulaRange.Cells
With FormulaCell
If IsNumeric(.Value) = False Then
MyMsg = "Not numeric"
Else
If .Value = MyLimit Then
MyMsg = SentMsg
If .Offset(0, 1).Value = NotSentMsg Then
Call Mail_with_outlook1
End If
Else
MyMsg = NotSentMsg
End If
End If
Application.EnableEvents = False
.Offset(0, 1).Value = MyMsg
Application.EnableEvents = True
End With
Next FormulaCell

ExitMacro:
Exit Sub

Please use code tags when you post code to the forum.

Dom

Hello b_hill06,

Welcome to the Forum!

To make your posts easier to read, copy, and edit please wrap your code. I did it for you this time. Here is how you can do it next time.

How to wrap your Code using the # icon

1. Select all your code using the mouse.
2. Click on the # icon on the toolbar in the Message window. This will automatically wrap the text you selected with the proper Code tags to create a Code Window in your post.

To manually wrap your code, use these Bulletin Board Code Tags

[code] Before of the first line,

[/code] After the last line.

Yeah, IsDate should be used in VBA to test for a date. You said "specifically, trying to get it to send when cells equal current date+14)" which is why I used = but if you want it to pick up before or after then you would need to use either < or >.

Along these lines I guess but adjust to suit:

Private Sub Worksheet_Calculate()

    Dim FormulaRange As Range
    Dim FormulaCell As Range
    Dim NotSentMsg As String
    Dim MyMsg As String
    Dim SentMsg As String
    Dim MyLimit As Date

    NotSentMsg = "Not Sent"
    SentMsg = "Sent"

'Above the MyLimit value it will run the macro
    MyLimit = Date + 14

'Set the range with the Formula that you want to check
    Set FormulaRange = Me.Range("B8")

    On Error GoTo EndMacro:

    For Each FormulaCell In FormulaRange.Cells
        With FormulaCell
            If IsDate(.Value) = False Then
                MyMsg = "Not date"
            Else
                If .Value = MyLimit Then
                    MyMsg = SentMsg
                    If .Offset(0, 1).Value = NotSentMsg Then
                        Call Mail_with_outlook1
                    End If
                Else
                    MyMsg = NotSentMsg
                End If
            End If
            Application.EnableEvents = False
            .Offset(0, 1).Value = MyMsg
            Application.EnableEvents = True
        End With
    Next FormulaCell
    
EndMacro:
    
End Sub

Dom