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

Excel Productivity Tools

Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial

Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial

Send email if due date has been met with VBA code

Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!

Save 50% of your time, and reduce thousands of mouse clicks for you every day!

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.

Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 80% Excel problems.

Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

Increases your productivity by 50% when viewing and editing multiple documents.
Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
Open and create documents in new tabs of same window, rather than in new windows.
Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!

You are guest ( Sign Up? )

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
Alex · 2 days ago
Buenos días! ¿que modificación tendría que realizar para dejar seleccionadas las celdas con la información de fecha, texto y correo y no tener que seleccionarlas cada vez que se activa la macro?

también me gustaría saber como introducir un CC, es decir, poder poner a otra persona en copia del correo. Gracias!
- Reply
- Share
To post as a guest, your comment is unpublished.
SerMFe · 15 days ago
Hi,

I have a question,

If I want to select a prederterminate range of cells, how could i modify the code in this part:

xLastRow = xRgDate.Rows.Count
Set xRgDate = xRgDate(1)

Toset automaticatly the cells?

Thank you :)
- Reply
- Share
To post as a guest, your comment is unpublished.
LaThalia Y Echols · 1 months ago
I am new to VBA. Is there a way to automatically run this program whenever the file is opened?
- Reply
- Share
- To post as a guest, your comment is unpublished.
  SerMFe · 15 days ago
  
  Yes, with
  
  Private Sub Workbook_Open()
  *Enter code here*
  End Sub
  
  Reply
  
  Share
To post as a guest, your comment is unpublished.
L Echols · 1 months ago
Hello, I am new to VBA. Is there a way to have this program automatically run whenever the file is opened?
- Reply
- Share
- To post as a guest, your comment is unpublished.
  crystal · 28 days ago
  
  Hi L Echols,
  For automatically running the code when the file is opened, please double click to open the ThisWorkbook code window (locates in the left side of the Microsoft Visual Basic for Applications window) , select Workbook from the first drop-down list, and then copy the above VBA code (except the first and the last line) into the code window and paste between the given two lines. See the below attached screenshot:
  
  1.png
  
  Reply
  
  Share
  
  To post as a guest, your comment is unpublished.
  
  Daniel Lee · 15 days ago
  
  This is awesome. I was wondering if there is a way to run the code without having to select the KuTools values every time? For clarification, I inputted this code and now whenever I open the workbook I still have to highlight the same columns. Is there a way to input the code to run a check on the same columns every time - as long as no edits to workbook have been made - to run the check and formulate an email based off said check? Thank you in advance.
  
  Reply
  
  Share
  
  To post as a guest, your comment is unpublished.
  
  Danny · 15 days ago
  
  This is awesome. I was wondering if there is a way to run the code without having to select the KuTools values every time? For clarification, I inputted this code and now whenever I open the workbook I still have to highlight the same columns. Is there a way to input the code to run a check on the same columns every time - as long as no edits to workbook have been made - to run the check and formulate an email based off said check? Thank you in advance.
  
  Reply
  
  Share
  
  To post as a guest, your comment is unpublished.
  
  crystal · 7 days ago
  
  Hi Danny,
  Please try the below code and change the ranges as you need.
  
  Public Sub CheckAndSendMail()
  'Updated by Extendoffice 2019/5/17
  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 = Range("C2: C4")
  If xRgDate Is Nothing Then Exit Sub
  Set xRgSend = Range("A2: A4")
  If xRgSend Is Nothing Then Exit Sub
  Set xRgText = Range("B2:B4")
  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 = "
  
  "
  xMailBody = ""
  xMailBody = xMailBody & "Dear " & xRgSendVal & vbCrLf
  xMailBody = xMailBody & "Text : " & xRgText.Offset(i - 1).Value & vbCrLf
  xMailBody = xMailBody & ""
  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
  
  Reply
  
  Share
To post as a guest, your comment is unpublished.
Dinesh B S · 1 months ago
in the body i want the column a,b,c value in a table format, like the expired date once reached in the body column i want to print a,b,c cells details in table format
- Reply
- Share
- To post as a guest, your comment is unpublished.
  crystal · 28 days ago
  
  Hi Dinesh B S,
  Sorry can't help you with that. Welcome to post any question in our forum: https://www.extendoffice.com/forum.html.
  
  Reply
  
  Share

Login to your account

Password Reset

Reminder