Send automatically an Email if the due date in the excel sheet is about to be exceeded

[Shayma]

Hello all,

I have an excel sheet which is protected and has a VBA code to indicate the date of the cell entry and that cell is one time entry. It is a sheet including tasks of a project to be followed and the employee should only enter the due date he believes he can finish the task and if he finishes the task he choses from the drop down list that the task is completed (cell entry that is related to VBA code to show the date he said the task is done). Now I need help please, if the employee did not choose that the task is completed and the due date is exceeded, I want an email to be sent automatically to me to review with the employee why he did not finish the task. I have around 40 employees where each one of them will be having many projects and the idea is to track who is causing the delay in the project.
The first column includes the task the second column is the drop down list choice (Completed), the third column is the automatic date (due to VBA code) showing what date he said it is complete, the forth column is the due date column which will be entered by the employee and and the fifth column also applies the VBA code to indicate which date he set the due date.

The VBA code that I am using is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim xRg As Range
    
    On Error Resume Next
    
    Set xRg = Intersect(Range("B2:I15"), Target)

    If xRg Is Nothing Then Exit Sub
    
    Target.Worksheet.Unprotect Password:="123"
    
    Application.EnableEvents = False
    With Target(1, 2)
        .Value = Date
        .EntireColumn.AutoFit
    End With
    Application.EnableEvents = True
    
    If xRg.Value <> mStr Then xRg.Locked = True
    
    Target.Worksheet.Protect Password:="123"
    
End Sub

 

[Logit]

The following macro is automatically run each time the workbook is opened.

Option Explicit

Sub eMail()
Dim toDate As Date
Dim SentDate As String
Dim OutApp
Dim OutMail

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
End With

toDate = Range("B2").Value
SentDate = Range("B3").Value
  If toDate = Date And SentDate = "" Then
 
     Set OutApp = CreateObject("Outlook.Application")
     Set OutMail = OutApp.CreateItem(0)

        
        On Error Resume Next
        With OutMail
        .To = "youremailaddresshere@yahoo.com"
        .CC = ""
        .BCC = ""
        .Subject = "Employee X Exceeded Project Time Limit"
        .Body = "Employee X has failed to meet project deadline."
        .Display   ' ********* Creates draft emails. Comment this out when you are ready
        '.Send     '********** UN-comment this when you  are ready to go live
        End With
 
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
 Range("B3").Value = "Mail Sent " & Date + Time 'Marks the project complete - will not send duplicate emails
End If


ActiveWorkbook.Save

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
End With
End Sub

The auto run function is included in the next macro which is located in the ThisWorkbook module :

Private Sub Workbook_Open()
    eMail
End Sub

The example workbook may be downloaded from : WORKS Email on Date.xlsm

I recommend including password protecting cells B2 and B3 after a date has been entered. That will prevent anyone from changing the dates in those locations.

 

[Shayma]

Hello Logit,

First of all, thank you so much for your reply and help. Please can you check the columns below because I got confused about what to change in the code you gave me. As you can see, the Due Date will be entered first in column D and the date they set the due date will be shown in column E automatically, column B will be the update and automatically the action taken date will appear in column C. As you can see it is a range of rows not a single cell. The rows of due date starts from D3 to D15, and will be compared with column C in case they did not take the action, or they took the action after the due date.

As in the second picture, the first VBA code will be written in module 1 and the second code will be written in thisWork book correct? The example workbook you shared with me unfortunately did not open.