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

Shayma

New Member
Joined
May 3, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,143
The following macro is automatically run each time the workbook is opened.

VBA Code:
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 :

Code:
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

New Member
Joined
May 3, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • Capture.PNG
    Capture.PNG
    11.3 KB · Views: 4
  • Capture1.PNG
    Capture1.PNG
    5.2 KB · Views: 4

Forum statistics

Threads
1,141,049
Messages
5,703,942
Members
421,321
Latest member
blusky4

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top