Automatically email me when a date is overdue

[kawa13]

How can I get Excel to send me an email if a date in a cell is overdue i.e. Cell Q1 contains today's date, Cell E1 has a date which is overdue. I'd like Excel to email me to inform me that automatically (I assume I have to have the spreadsheet open for this to work)

 

[ranman256]

paste this code into a module. Dont forget to add the OUTLOOK REFERENCE
'NOTE : YOU MUST HAVE THE OUTLOOK REFERENCE CHECKED IN VBE; Alt-F11, menu,tools, references, Microsoft Outlook XX Object library

Option Explicit
Sub CheckOverdueDate()
Dim vToday, vDue
Dim sTo As String, sSubj As String, sBody As String, sName As String

If Range("E1").Value = "" Then Exit Sub
vToday = Format(Date, "short date")
vDue = Format(Range("E1").Value, "short date")
If vToday > vDue Then
   sTo = "myEmail@Company.com"
   sName = Range("A1").Value
   sSubj = sName & " overdue"
   sBody = "this acct is overdue"
   
   Send1Email sTo, sSubj, sBody
   
   MsgBox "Overdue Email sent"
End If
End Sub
Private Function Send1Email(ByVal pvTo, ByVal pvSubj, ByVal pvBody, Optional ByVal pvFile) As Boolean
Dim oApp As Outlook.Application
Dim oMail As Outlook.MailItem
On Error GoTo ErrMail
'NOTE : YOU MUST HAVE THE OUTLOOK REFERENCE CHECKED IN VBE; Alt-F11, menu,tools, references, Microsoft Outlook XX Object library
Set oApp = GetApplication("Outlook.Application")  'it may be open already so use this
'Set oApp = CreateObject("Outlook.Application")  'not this
Set oMail = oApp.CreateItem(olMailItem)
With oMail
    .To = pvTo
    .Subject = pvSubj
    If Not IsMissing(pvFile) Then .Attachments.Add pvFile, olByValue, 1
   
    .HTMLBody = pvBody
    'If Not IsNull(pvBody) Then .Body = pvBody
   
    .Display True
    '.Send
End With
Send1Email = True
endit:
Set oMail = Nothing
Set oApp = Nothing
Exit Function
ErrMail:
MsgBox Err.Description, vbCritical, Err
Resume endit
End Function

execute the check when workbook opens

Private Sub Workbook_Open()
  CheckOverdueDate
End Sub