Send Reminder Email Automatically from an Excel Worksheet Using VBA

In the banking or financial system, you might have to deal with a lot of clients. So, you have to deal with lots of information. That’s why you might need to remind your clients of the important deadlines. So, you have to organize lots of reminder emails and send them automatically. In this tutorial, we will show you how to send a reminder email automatically from an Excel worksheet using VBA codes.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


3 Steps to Send Reminder Email Automatically from an Excel Worksheet Using VBA

We’ve included a sample data set in the image below that includes the emails of various people, as well as the deadlines they must meet for various reasons. We’ll use VBA codes to send emails automatically to accomplish this. We’ll also make manual changes to the program to send the emails automatically.

Steps to Send Reminder Email Automatically from an Excel Worksheet Using VBA

Step 1: Create a Module in Excel Worksheet

  • Firstly, to open the VBA Macro, press  Alt + F11 .
  • Click on the Insert tab.
  • From the Insert tab, select the Module option.

Steps to Send Reminder Email Automatically from an Excel Worksheet Using VBA

Read More: Excel Macro to Send Email Automatically (3 Suitable Examples)


Step 2: Write VBA Codes to Send Reminder Email Automatically

  • To send reminder emails automatically, paste the following VBA codes.
Public Sub SendReminderMail()
'Declare the variables
    Dim XDueDate As Range
    Dim XRcptsEmail As Range
    Dim xMailContent As Range
    Dim xRngDn As Range
    Dim xCrtOut As Object
    Dim xValDateRng As String
    Dim xValSendRng As String
    Dim k As Long
    Dim xMailSections As Object
    Dim xFinalRw As Long
    Dim CrVbLf As String
    Dim xMsg As String
    Dim xSubEmail As String
    On Error Resume Next
    'To select the date column insert a input box
    Set XDueDate = Application.InputBox("Select the column for Deadline/Due Date date column:", "ExcelDemy", , , , , , 8)
    If XDueDate Is Nothing Then Exit Sub
    'Insert a input box for selecting the recipients
    Set XRcptsEmail = Application.InputBox("Choose the column for the email addresses of the recipients:", "ExcelDemy", , , , , , 8)
    If XRcptsEmail Is Nothing Then Exit Sub
    'To enter the text mail, insert a input box
    Set xMailContent = Application.InputBox("In your email, choose the column with the reminded text:", "ExcelDemy", , , , , , 8)
    If xMailContent Is Nothing Then Exit Sub
    'Count rows for the due dates
    xFinalRw = XDueDate.Rows.Count
    Set XDueDate = XDueDate(1)
    Set XRcptsEmail = XRcptsEmail(1)
    Set xMailContent = xMailContent(1)
    'Set command to open MS Outlook Application
    Set xCrtOut = CreateObject("Outlook.Application")
    
    'Apply For loop to conduct the operation in each row one by one
    For k = 1 To xFinalRw
        xValDateRng = ""
        xValDateRng = XDueDate.Offset(k - 1).Value
        'Apply If condition for the Due Date values
        If xValDateRng <> "" Then
        'Condition set to send mail if the difference between due dates and current date is greater than 1 and less than 7 days
        'Means 1 < X< 7, X = Due Date - Current Date
        If CDate(xValDateRng) - Date <= 7 And CDate(xValDateRng) - Date > 0 Then
            xValSendRng = XRcptsEmail.Offset(k - 1).Value
            'Create the subject, body and text contents with the required variables
            xSubEmail = xMailContent.Offset(k - 1).Value & " on " & xValDateRng
            CrVbLf = "<br><br>"
            xMsg = "<HTML><BODY>"
            xMsg = xMsg & "Dear " & xValSendRng & CrVbLf
            xMsg = xMsg & "Text : " & xMailContent.Offset(k - 1).Value & CrVbLf
            xMsg = xMsg & "</BODY></HTML>"
            'Create the email
            Set xMailSections = xCrtOut.CreateItem(0)
            'Define the position to place the Subject, Body and Recipients Address
            With xMailSections
                .Subject = xSubEmail
                .To = xValSendRng
                .HTMLBody = xMsg
                .Display
                
                .Send
            
            End With
            Set xMailSections = Nothing
        End If
    End If
    Next
    Set xCrtOut = Nothing
End Sub

Steps to Send Reminder Email Automatically from an Excel Worksheet Using VBA

Read More: Automatically Send Emails from Excel Based on Cell Content (2 Methods)


Similar Readings


Step 3: Run VBA Program to Send Reminder Email Automatically from Excel Worksheet

  • After saving, press F5 to run the program.
  • In the Date input box, select the Deadline column with the due dates.
  • Then, click OK.

Steps to Send Reminder Email Automatically from an Excel Worksheet Using VBA

  • In the recipients’ email input box, select the Recipients column in the range B5:B7.
  • Press Enter.

Steps to Send Reminder Email Automatically from an Excel Worksheet Using VBA

  • Select the range C5:C7 in the final input box for the Content column.
  • Finally, click OK.

Sample Data

  • As a result, all of your emails will be sent as reminder emails before the due dates.
 Notes: 
  • You can also choose to manually send the emails using the automated program described above. Simply place an apostrophe (‘) before the (.send) command to disable it, as shown in the image below.

Sample Data

  • As a result, you will receive all completed drafts prior to sending them. Then, by simply clicking the Send button, select whom you want to send emails to.

Sample Data

  • Emails may not be sent immediately if there is insufficient storage to receive new data or if the per-day message sending limit has been reached.
  • Microsoft Outlook must be used as the default email application.

Read More: How to Send Excel File to Email Automatically (3 Suitable Methods)


Conclusion

I hope this article has given you a tutorial about how to send email reminders automatically from an Excel worksheet using VBA. All of these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. We’re motivated to keep making tutorials like this because of your valuable support.

Please contact us if you have any questions. Also, feel free to leave comments in the section below.

We, the Exceldemy Team, are always responsive to your queries.

Stay with us and keep learning.


Related Articles

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

25 Comments
  1. Hi, after following this process, I received automated Email, but all email are getting delivered together which should be deliver one day before the date given in deadline column. means mail are not getting delivered as per dates.
    please help with solution.

    • Greetings Nishant,
      To get emails one day prior the deadline date, try the following codes below.

      We just edited the code in the If condition by replacing 1 with 0.

      Previous code:

      If CDate(xValDateRng) - Date <= 7 And CDate(xValDateRng) - Date > 0 Then
                  xValSendRng = XRcptsEmail.Offset(k - 1).Value

      Modified Code:

      If CDate(xValDateRng) - Date <= 7 And CDate(xValDateRng) - Date > 1 Then
                  xValSendRng = XRcptsEmail.Offset(k - 1).Value

      Please, give us feedback, if any further query needed. The ExcelDemy team is always in your assistance.

  2. How to add on another column instead of only, due date, email address and text column. Please advise

    • Greeting AIN,
      Let’s say you want to add another column containing the CC email addresses. You can just copy the codes from here. Moreover, if you want to get the Excel file containing the codes, just give your email address.

      Public Sub SendReminderMail()
      'Declare the variables
          Dim XDueDate As Range
          Dim XRcptsEmail As Range
          Dim xMailContent As Range
          Dim xRngDn As Range
          Dim xCrtOut As Object
          Dim xValDateRng As String
          Dim xValSendRng As String
          Dim k As Long
          Dim xMailSections As Object
          Dim xFinalRw As Long
          Dim CrVbLf As String
          Dim xMsg As String
          Dim xSubEmail As String
      'Declare variables for additional column
          Dim xCCEmail As String
          Dim xCCContent As Range
          
          On Error Resume Next
          
          'To select the date column insert a input box
          Set XDueDate = Application.InputBox("Select the column for Deadline/Due Date date column:", "ExcelDemy", , , , , , 8)
          If XDueDate Is Nothing Then Exit Sub
          
          'Insert a input box for selecting the recipients
          Set XRcptsEmail = Application.InputBox("Choose the column for the email addresses of the recipients:", "ExcelDemy", , , , , , 8)
          If XRcptsEmail Is Nothing Then Exit Sub
          
          'To enter the text mail, insert a input box
          Set xMailContent = Application.InputBox("In your email, choose the column with the reminded text:", "ExcelDemy", , , , , , 8)
          If xMailContent Is Nothing Then Exit Sub
          
          'To enter the CC mails in another column, insert a input box
          Set xCCContent = Application.InputBox("In your email, choose the column with the CC addresses:", "ExcelDemy", , , , , , 8)
          If xCCContent Is Nothing Then Exit Sub
          
          
          
          'Count rows for the due dates
          xFinalRw = XDueDate.Rows.Count
          Set XDueDate = XDueDate(1)
          Set XRcptsEmail = XRcptsEmail(1)
          Set xMailContent = xMailContent(1)
          Set xCCContent = xCCContent(1)
          
          'Set command to open MS Outlook Application
          Set xCrtOut = CreateObject("Outlook.Application")
          
          'Apply For loop to conduct the operation in each row one by one
          For k = 1 To xFinalRw
              xValDateRng = ""
              xValDateRng = XDueDate.Offset(k - 1).Value
              
              'Apply If condition for the Due Date values
              If xValDateRng <> "" Then
              
              'Condition set to send mail if the difference between due dates and current date is greater than 1 and less than 7 days
              'Means 1 < X< 7, X = Due Date - Current Date
              If CDate(xValDateRng) - Date <= 7 And CDate(xValDateRng) - Date > 0 Then
                  xValSendRng = XRcptsEmail.Offset(k - 1).Value
                  
                  'Create the cc emails with the required variables
                  xCCEmail = xCCContent.Offset(k - 1).Value
                  
                  'Create the subject, body and text contents with the required variables
                  xSubEmail = xMailContent.Offset(k - 1).Value & " on " & xValDateRng
                  
                  CrVbLf = "<br><br>"
                  xMsg = "<HTML><BODY>"
                  xMsg = xMsg & "Dear " & xValSendRng & CrVbLf
                  xMsg = xMsg & "Text : " & xMailContent.Offset(k - 1).Value & CrVbLf
                  xMsg = xMsg & "</BODY></HTML>"
                  
                  'Create the email
                  Set xMailSections = xCrtOut.CreateItem(0)
                  
                  'Define the position to place the Subject, Body and Recipients Address
                  With xMailSections
                      .Subject = xSubEmail
                      .To = xValSendRng
                      .CC = xCCEmail
                      .HTMLBody = xMsg
                      .Display
                      
                      '.Send
                  
                  End With
                  Set xMailSections = Nothing
              End If
          End If
          Next
          Set xCrtOut = Nothing
      End Sub
      
      

      Hope this will work with your requirements.
      Please, keep giving your feedbacks. The ExcelDemy team is always concerned about your queries.

  3. Hello,

    How long before the due date does the email get sent to the email address?

    • Hi Taylor,

      # The macro contains conditions against the email sending within a Week prior to the Deadline/Due Date using the following line

      If CDate(xValDateRng) - Date <= 7 And CDate(xValDateRng) - Date > 0 Then
      xValSendRng = XRcptsEmail.Offset(k - 1).Value

      # So, the email will get sent within- 1 day <= Deadline/Due Date <= 7 day or 1 week range prior to the Deadline/Due Date.

      Hope, you find your answer, let us know if further explanation is needed. Our dedicated Softeko Team is always there to help.

  4. Hi
    If i want to add an attachment, can u please help?
    Regards

    • Greetings Lalit,
      You just have to add two lines in between the xMailSections portion:
      Steps:
      1. Add the file path of your attachment with the attachment name.
      2. Apply add attachment command to add the attachment.

      Attached_File = "C:\Users\USER\Downloads\Send_Email_Reminder.xlsm"
      .Attachments.Add Attached_File

      Please, see the image below for a detailed understanding.

      1

      After running the code, you will see results with the attachment available.

      2

      Moreover, we have a dedicated article based on your requirement. Please, go through the article to learn in detail.

      Hope, this will meet your requirement. Please, give us feedback if any furthur query needed. The ExcelDemy team is always in your assistance.

    • Hello, Lalit Kansal! You can send us your attachment to this email: [email protected]

  5. Hello

    Thanks for resolving the query.

    Can u help in the code if we want to add the different subject rather than text and due date copied in the mail

    Thanks and Regards

    • Greetings Lalit,
      Pleased to hear that your previous query has been resolved.

      We have created an Excel file for you to add different subjects. Just download the file and run the code of Module 1.

      https://www.exceldemy.com/wp-content/uploads/2022/08/Send_Email_Reminder_Lalit.xlsm

      1. You will get an input box to select the subject column as shown in the image below.

      1

      2. Therefore, you will see the emails on display before sending them, with the selected different subjects.

      2

      Please feel free to ask more queries. Our ExcelDemy Team will reach you as soon as possible.

  6. Hello,

    Do we need to run the module every time i update the input (For eg: deadline)?
    How to autoupdate that?

    Br,
    Claudiu

    • Hello Claudiu, thank you for your question. The following steps will execute the VBA code whenever you change the dates.

      Press ALT+F11 to bring up the VBA window. Then, right click on “Sheet1” and select View Code.

      Then, type the following VBA code. This code will call the SendReminderMail Sub whenever, a value changes in the cell range D5:D7.

      Private Sub Worksheet_Change(ByVal xTarget As Range)
      If Not Intersect(xTarget, Range("D5:D7")) Is Nothing Then
      Call SendReminderMail
      End If
      End Sub

      After that, we’ve set the cell ranges instead of using the input box. For example, the code for xDueDate is changed to:

      Set XDueDate = Range("D5:D7")

      Then, when you change the date, it will automatically execute the macro.

      However, if this doesn’t solve your problem, you can mail us your Excel file with detailed instructions to: [email protected], and we’ll try to solve it as soon as possible.

  7. HOW TO AUTOMATICALLY SELECTDUE DATE COLUMN WITHOUT INPUTBOX AND RUN A CODE

    • Reply Avatar photo
      Bishawajit Chakraborty Dec 12, 2022 at 2:34 PM

      Thank you Prachi Davade for your wonderful question. You can change the

      Set XDueDate = Application.InputBox("Select the column for Deadline/Due Date date column:", "ExcelDemy", , , , , , 8)

      to

      Set XDueDate = Range("D5:D7")

      This will do what you desire. If you have further queries, let us know.

      Regards

      Bishawajit, on behalf of ExcelDemy

  8. Hi, how to add reminder time for the email? (e.g. want to send the email on January 13th, 2023 at 8.00 am)

    • Reply Avatar photo
      Bishawajit Chakraborty Jan 17, 2023 at 4:13 PM

      Thank you, SALWA for your wonderful question. 

      First off, you cannot change the time for a scheduled email; however, you can add the remaining date in the email using the VBA code. When you apply the VBA code, the emails will pop up for sending. And on the Outlook email section, you can customize emails with scheduled time

      Public Sub SendEmail02()
          Dim Date_Range As Range
          Dim Mail_Recipient As Range
          Dim Email_Text As Range
          Dim Outlook_App_Create As Object
          Dim Mail_Item As Object
          Dim Last_Row As Long
          Dim VB_CR_LF, Email_Body, Date_Range_Value, Send_Value, Subject As String
          Dim i As Long
          On Error Resume Next
          Set Date_Range = Application.InputBox("Please choose the date range:", "Message Box", , , , , , 8)
          If Date_Range Is Nothing Then Exit Sub
          Set Mail_Recipient = Application.InputBox("Please select the Email addresses:", "Message Box", , , , , , 8)
          If Mail_Recipient Is Nothing Then Exit Sub
          Set Email_Text = Application.InputBox("Select the Email Text:", "Message Box", , , , , , 8)
          If Email_Text Is Nothing Then Exit Sub
          Last_Row = Date_Range.Rows.Count
          Set Date_Range = Date_Range(1)
          Set Mail_Recipient = Mail_Recipient(1)
          Set Email_Text = Email_Text(1)
          Set Outlook_App_Create = CreateObject("Outlook.Application")
          For i = 1 To Last_Row
              Date_Range_Value = ""
              Date_Range_Value = Date_Range.Offset(i - 1).Value
              If Date_Range_Value <> "" Then
              If CDate(Date_Range_Value) - Date <= 7 And CDate(Date_Range_Value) - Date > 0 Then
                  Send_Value = Mail_Recipient.Offset(i - 1).Value
                  Subject = Email_Text.Offset(i - 1).Value & " on " & Date_Range_Value
                  VB_CR_LF = "<br><br>"
                  Email_Body = "<HTML><BODY>"
                  Email_Body = Email_Body & "Dear " & Send_Value & VB_CR_LF
                  Email_Body = Email_Body & "Text : " & Email_Text.Offset(i - 1).Value & VB_CR_LF
                  Email_Body = Email_Body & "</BODY></HTML>"
                  Set Mail_Item = Outlook_App_Create.CreateItem(0)
                  With Mail_Item
                      .Subject = Subject
                      .To = Send_Value
                      .HTMLBody = Email_Body
                      .Display
                  End With
                  Set Mail_Item = Nothing
              End If
          End If
          Next
          Set Outlook_App_Create = Nothing
      End Sub

      Then, using your Outlook account, you can set it up for a scheduled email. I hope this may solve your issue. 

      Bishawajit, on behalf of ExcelDemy

      • Hi I’d like to find out if i can change the name of the person I am sending it to, so instead of it being the email address showing it’s there name that I would define ? How can I have this happen?

        • Avatar photo
          Bishawajit Chakraborty Mar 9, 2023 at 11:55 AM

          Thank you, CHAD for your wonderful question.

          Firstly, you cannot change the recipient’s name after emailing. When you apply the VBA code, the emails will pop up for sending. And on the Outlook email section, you can customize emails. So, you have to put the email address first in the recipient’s column.

          This will do what you desire. If you have further queries, let us know.

          Regards

          Bishawajit, on behalf of ExcelDemy

  9. Hi, how to add CC and subject column. Please advice

    • You can use the following code to include the subject and CC columns.

      Public Sub SendReminderMail()
      'Declare the variables
          Dim XDueDate As Range
          Dim XRcptsEmail As Range
          Dim xMailContent As Range
          Dim xRngDn As Range
          Dim xCrtOut As Object
          Dim xValDateRng As String
          Dim xValSendRng As String
          Dim k As Long
          Dim xMailSections As Object
          Dim xFinalRw As Long
          Dim CrVbLf As String
          Dim xMsg As String
          Dim xSubEmail As Range
          Dim xCCMail As Range
          On Error Resume Next
          'To select the date column insert a input box
          Set XDueDate = Application.InputBox("Select the column for Deadline/Due Date date column:", "ExcelDemy", , , , , , 8)
          If XDueDate Is Nothing Then Exit Sub
          'Insert a input box for selecting the recipients
          Set XRcptsEmail = Application.InputBox("Choose the column for the email addresses of the recipients:", "ExcelDemy", , , , , , 8)
          If XRcptsEmail Is Nothing Then Exit Sub
          'To enter the text mail, insert a input box
          Set xMailContent = Application.InputBox("In your email, choose the column with the reminded text:", "ExcelDemy", , , , , , 8)
          If xMailContent Is Nothing Then Exit Sub
          'To enter the subject mail, insert a input box
          Set xSubEmail = Application.InputBox("In your email, choose the column with the subject text:", "ExcelDemy", , , , , , 8)
          If xSubEmail Is Nothing Then Exit Sub
          'To enter the CC mail, insert a input box
          Set xCCMail = Application.InputBox("In your email, choose the column with the CC column:", "ExcelDemy", , , , , , 8)
          If xCCMail Is Nothing Then Exit Sub
          'Count rows for the due dates
          xFinalRw = XDueDate.Rows.Count
          Set XDueDate = XDueDate(1)
          Set XRcptsEmail = XRcptsEmail(1)
          Set xMailContent = xMailContent(1)
          Set xSubEmail = xSubEmail(1)
          Set xCCMail = xCCMail(1)
          'Set command to open MS Outlook Application
          Set xCrtOut = CreateObject("Outlook.Application")
          'Apply For loop to conduct the operation in each row one by one
          For k = 1 To xFinalRw
              xValDateRng = ""
              xValDateRng = XDueDate.Offset(k - 1).Value
              'Apply If condition for the Due Date values
              If xValDateRng <> "" Then
              'Condition set to send mail if the difference between due dates and current date is greater than 1 and less than 7 days
              'Means 1 < X< 7, X = Due Date - Current Date
              If CDate(xValDateRng) - Date <= 7 And CDate(xValDateRng) - Date > 0 Then
                  xValSendRng = XRcptsEmail.Offset(k - 1).Value
                  'Create the subject, body and text contents with the required variables
                  xSubEmail = xSubEmail.Offset(k - 1).Value
                  xCCMail = xCCMail.Offset(k - 1).Value
                  CrVbLf = "<br><br>"
                  xMsg = "<HTML><BODY>"
                  xMsg = xMsg & "Dear " & xValSendRng & CrVbLf
                  xMsg = xMsg & "Text : " & xMailContent.Offset(k - 1).Value & CrVbLf
                  xMsg = xMsg & "</BODY></HTML>"
                  'Create the email
                  Set xMailSections = xCrtOut.CreateItem(0)
                  'Define the position to place the Subject, Body and Recipients Address
                  With xMailSections
                      .Subject = xSubEmail
                      .CC = xCCMail
                      .To = xValSendRng
                      .HTMLBody = xMsg
                      .Display
                      '.Send
                  End With
                  Set xMailSections = Nothing
              End If
          End If
          Next
          Set xCrtOut = Nothing
      End Sub
      

      The following image shows the output.

  10. Hi, i wanna asked about this program. Is this program just running one time when we run this program or this program will always run and when it reach the due date then it will still automatically send reminder to email. Would like to have your confirmation

    Thank you

    • Greeting Yuki,
      Thank you for your queries,

      This program will have to run just one time and when it will reach the due date it will send reminder emails automatically.

      But you can also customize it to send it manually. Please go through the “Notes” section in Step 3 of the article.

      You can stop sending emails automatically just by disabling the .Send command from the full code. It will pops up for a confirmation before sending. Moreover, you can customize the recipients names. That’s the elegance of our objective!

      However, if this doesn’t solve your queries, you can mail us your Excel file with detailed instructions to: [email protected], and we’ll try to solve it as soon as possible.

      Regards,
      ExcelDemy Team

      • Hai thank for the explanation. So basically for this program we need to run manually every day right.
        But i would like to know whether there is a program that can always running automatically when the due date already reached.Thank you

        • Yeah, YUKI, you are right. Your workbook need to be opened all the time to continue running this program. But, Problem arises when you turn off your pc the program will not run. So, you need to reopen the Excel file and run the program on daily basis.

          However, you can just open the Excel App. from your PC. Then it will run automatically. To do so, please insert your whole code in between Private Sub as below:

          Private Sub auto_open()
          "Your Codes"
          End Sub

          So, whenever you open the Excel App. the program will continue to run.

          On the other hand, if you want to run the program just after whenever you open PC. We need some time to make it for you. You may share your Excel file with us in [email protected]. Our dedicated ExcelDemy Team is trying to resolve your issue.

          Regards,
          ExcelDemy Team

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo