How to send email if due date has been met in Excel?
As below screenshot shown, if the due date in column C is less than or equal to 7 days (current date is 2017/9/13), then send an email reminder to the specified recipient in column A with specified content in column B. How to achieve it? This article will provide a VBA method to deal with it in details.
Send email if due date has been met with VBA code
Send email if due date has been met with VBA code

Please do as follows to send an email reminder if the due date has been met in Excel.
1. Press the Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, please click Insert > Module. Then copy and paste the below VBA code into the Module window.
VBA code: Send email if due date is closed in Excel
Public Sub CheckAndSendMail() 'Updated by Extendoffice 2018/11/22 Dim xRgDate As Range Dim xRgSend As Range Dim xRgText As Range Dim xRgDone As Range Dim xOutApp As Object Dim xMailItem As Object Dim xLastRow As Long Dim vbCrLf As String Dim xMailBody As String Dim xRgDateVal As String Dim xRgSendVal As String Dim xMailSubject As String Dim i As Long On Error Resume Next Set xRgDate = Application.InputBox("Please select the due date column:", "KuTools For Excel", , , , , , 8) If xRgDate Is Nothing Then Exit Sub Set xRgSend = Application.InputBox("Please select the recipients?email column:", "KuTools For Excel", , , , , , 8) If xRgSend Is Nothing Then Exit Sub Set xRgText = Application.InputBox("Select the column with reminded content in your email:", "KuTools For Excel", , , , , , 8) If xRgText Is Nothing Then Exit Sub xLastRow = xRgDate.Rows.count Set xRgDate = xRgDate(1) Set xRgSend = xRgSend(1) Set xRgText = xRgText(1) Set xOutApp = CreateObject("Outlook.Application") For i = 1 To xLastRow xRgDateVal = "" xRgDateVal = xRgDate.Offset(i - 1).Value If xRgDateVal <> "" Then If CDate(xRgDateVal) - Date <= 7 And CDate(xRgDateVal) - Date > 0 Then xRgSendVal = xRgSend.Offset(i - 1).Value xMailSubject = xRgText.Offset(i - 1).Value & " on " & xRgDateVal vbCrLf = "<br><br>" xMailBody = "<HTML><BODY>" xMailBody = xMailBody & "Dear " & xRgSendVal & vbCrLf xMailBody = xMailBody & "Text : " & xRgText.Offset(i - 1).Value & vbCrLf xMailBody = xMailBody & "</BODY></HTML>" Set xMailItem = xOutApp.CreateItem(0) With xMailItem .Subject = xMailSubject .To = xRgSendVal .HTMLBody = xMailBody .Display '.Send End With Set xMailItem = Nothing End If End If Next Set xOutApp = Nothing End Sub
Notes: The line If CDate(xRgDateVal) - Date <= 7 And CDate(xRgDateVal) - Date > 0 Then in the VBA code means that the due date must be greater than 1 day and less than or equal to 7 days. You can change it as you need.
3. Press the F5 key to run the code. In the first popping up Kutools for Excel dialog box, please select the due date column range and then click the OK button. See screenshot:
4. Then the second Kutools for Excel dialog box pops up, please select the corresponding column range which contains the recipients’ email addresses, and click the OK button. See screenshot:
5. In the last Kutools for Excel dialog box, select the content you want to display in the email body, and then click the OK button.
Then an email will be created automatically with the specified recipient, subject and body listed out if the due date in column C is less than or equal to 7 days. Please click the Send button to send the email.
Notes:
1. Each created email is corresponding to a due date. For example, if there are three due dates meet the criteria, three email messages will be created automatically.
2. This code will not be triggered if there are no dates meet the criteria.
3. The VBA code is only working when you use Outlook as your email program.
Related articles:
- How to automatically send email based on cell value in Excel?
- How to send an email through Outlook when workbook is saved in Excel?
- How to send email if a certain cell is modified in Excel?
- How to send email if button is clicked in Excel?
- How to send email reminder or notification if workbook is updated in Excel?
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
