How to send email if due date has been met in Excel?
As shown in the screenshot below, if the due date in column C is less than or equal to 7 days (for example, the current date is 2017/9/13), an email is sent to the specified recipient in column A and the specified content in column B is dispalyed in the body of the email. How could you do to achieve it? This article provides a VBA code to help you accomplish this task.
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.
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.
- 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?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
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!