Send Reminder Email Automatically from an Excel Worksheet Using VBA

The sample dataset below includes the emails of various people, as well as the deadlines they must meet for various reasons. We’ll use a VBA macro to automatically send emails from Excel to remind them, or to send the emails manually if required.

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

Step 1 – Create a Module in Excel Worksheet

  • To open the VBA Editor, press  Alt + F11 .
  • Click on the Insert tab.
  • Select the Module option.

A module window opens.

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

Read More: Excel Macro to Send Email Automatically


Step 2 – Write VBA Codes to Send Reminder Email Automatically

  • Copy the following code and paste it into the module window:
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: Excel Macro: Send Email to an Address in Cell


Step 3 – Run VBA Macro to Send Reminder Email Automatically from Excel Worksheet

  • After saving, press F5 to run the macro.

A macro dialog box opens.

  • In the Date input box, select the Deadline column containing the due dates.
  • Click OK.

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

Another input box opens.

  • Select the Recipients column in the range B5:B7.
  • Click OK.

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.
  • Click OK.

Sample Data

As a result, all of the reminder emails will be sent before the due dates.

Notes: 

  • We can also send the emails manually using the macro above. Simply place an apostrophe () before the (.send) command to disable automatic sending, as shown in the image below.

Sample Data

  • As a result, all completed drafts will be presented for review prior to sending them. After reviewing them, simply click the Send button on each individual email to be sent.

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 Email from Excel with Body Using a Macro


Download Practice Workbook


Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Bhubon Costa
Bhubon Costa

Bhubon Costa, B.Sc. in Naval Architecture & Marine Engineering from Bangladesh University of Engineering & Technology, has worked with the ExcelDemy since 2021. Currently, he has been working as a reviewer. Notably, he has written over 90 articles and led several VBA content development teams. He has a great passion for the fields of data analytics and data science. His areas of expertise include Excel VBA, Power Query, Pivot Table, Power BI, MySQL, PostgreSQL, machine learning, and Python... Read Full Bio

61 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.

      • Hai, I am getting “compile error” for control variable already in use. Highlighting “k=1 to xFinalRw” “apply for loop conduct the operation in each row one by one

        • Avatar photo
          Rubayed Razib Suprov Jun 15, 2023 at 1:04 PM

          Greetings Vinod,

          The code provided above is actually working quite well for multiple members of our team. The Error you experienced is local and I advise you to review the code and check whether you made any altercation to the code. Try to paste the code as given here same to the editor. Just change the attachment address according to your need.

          The specific error that you are experiencing might be the cause of using the same variable k in multiple places. Again, in our code, we did not use the variable in multiple places in the same code. Try to paste the same code that we provided or change the variable name to something else, like K1. Hope this helps.

          Thanks and Regards
          Rubayed Razib
          Team Exceldemy

    • 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 Bishawajit Chakraborty
      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 Bishawajit Chakraborty
      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?

        • Bishawajit Chakraborty
          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

      • Reply
        Nouran El Shazly May 2, 2023 at 4:49 AM

        Hi bhunon
        Hope you are great ,

        How I can add more than one cell content ? I mean for the content how I can include more than cell in the mail contents function?

        Also , how I can keep this reminder working although I closed the excel ?

        • Nouran El Shazly May 6, 2023 at 11:04 PM

          Dear
          I tried your previous solution for adding more the I content and it worked perfectly I added 18 variables.
          More question please can I set more than one reminder ?
          I mean to be reminded before the due date with 90 days and 50 days and 30 days for example?
          Thank you a lot

        • Bhubon Costa
          Bhubon Costa May 8, 2023 at 4:45 PM

          Dear Nouran,

          I am delighted to hear that your previous queries have been resolved. It seems like you have invested a lot of patience and effort, considering you had to work with 18 variables to accomplish your task!

          Regarding your further queries, you want to send reminder emails multiple times with different time intervals. For instance, you want to send the first mail three days prior to the deadline, the second mail five days prior to the deadline, and the final mail seven days before the deadline. To accomplish this, we have utilized the following dataset, and as of today, it is the 8th of May, 2023.

          Now, you have to modify the If section of the codes as in the image below.

          Here, is the full updated code.

          Public Sub SendReminderMail_Multiple()
          'Declare 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
                      Dim xDaysDiff As Integer
                      xDaysDiff = DateDiff("d", Date, CDate(xValDateRng))
                      If xDaysDiff = 3 Or xDaysDiff = 5 Or xDaysDiff = 7 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

          I hope that this response has resolved your current queries. If you would like to automate the program, please refer to our previous replies for detailed instructions. Additionally, please inform us if you have any further questions.

          Regards,
          ExcelDemy Team

        • Bhubon Costa
          Bhubon Costa May 3, 2023 at 4:16 PM

          Yes, you can still run the program when your Excel App is closed. You do not need to run the program every day manually. By doing so, whenever you open your PC your program will be executed automatically. Follow the steps below to do so.
          Step 1:

          1. From the Start Menu type Run to open the Windows Run dialog box and paste the following path:

          %AppData%\Microsoft\Windows\Start Menu\Programs\Startup

          2. Click OK.

          Step 2: Place your Excel File in the Startup folder.

          Step 3:

          1. Open your Excel file Macro Window.

          2. In the Project Explorer, double-click the ThisWorkbook object to open the code window for the workbook.

          3. In the code window, select Workbook from the drop-down list at the top.

          4. Then, select the Workbook_Open event from the drop-down list on the right.

          Step 4:

          1. Type or paste the VBA code that you want to run when the file is opened between the Private Sub Workbook_Open() and End Sub lines.

          2. Save the Excel file.

          Private Sub Workbook_Open()
              "Your Full VBA Codes"
          End Sub

          Now, whenever you open your PC, the VBA code you wrote will be automatically executed. Hope this will resolve your issue. Please let us know your further feedback, our ExcelDemy Team is always relentless to welcome your issues.

          Regards,
          ExcelDemy Team

        • Bhubon Costa
          Bhubon Costa May 2, 2023 at 4:23 PM

          Answer 1: Let’s say you want to add one more column for the mail content portion. So, we have assigned VBA codes with one more variable xMailContent2

          Copy the codes below and apply them to your Module.

          Public Sub SendReminderMail()
          'Declare the variables
              Dim XDueDate As Range
              Dim XRcptsEmail As Range
              Dim xMailContent As Range
              Dim xMailContent2 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 content 1, 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 text mail content 2, insert a input box
              Set xMailContent2 = Application.InputBox("In your email, choose the column with the reminded text:", "ExcelDemy", , , , , , 8)
              If xMailContent2 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 xMailContent2 = xMailContent2(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 & "Text : " & xMailContent2.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

          See the output as images below:

          Answer 2: Unfortunately, you cannot run the program without opening the Excel App. You may get this done by applying API. However, you can try the below process but it has some troubleshooting issues. Please let us know if you get solved your issue.

          1. Open Excel and create a new workbook or open an existing one.
          2. Press Alt + F11 to open the Visual Basic Editor.
          3. Write your VBA code in a module.
          4. Save the workbook as a macro-enabled workbook (*.xlsm) format.
          5. Close Excel.
          6. Open the Task Scheduler by typing “Task Scheduler” in the Start menu search bar and pressing Enter.
          7. Click on “Create Task” in the right-hand panel.
          8. In the “General” tab, give the task a name and a description.
          9. In the “Actions” tab, click “New” and select “Start a program” as the action.
          10. In the “Program/script” field, browse to the location where your Excel workbook is saved and select it.
          11. In the “Add arguments” field, enter the full path and filename of the workbook you want to run, enclosed in quotes.
          12. Click “OK” to close the “New Action” dialog box.
          13. In the “Triggers” tab, click “New” and select the frequency and timing for the task to run.
          14. Click “OK” to close the “New Trigger” dialog box.
          15. Click “OK” to create the task.

          On the other hand, you can automatically run when you open the Excel file, you do not need to manually run the code. Simply, apply the VBA codes inside the Private Sub event.

          Private Sub Workbook_Open()
              "Your Full VBA Codes"
          End Sub
      • 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

        • Bhubon Costa
          Bhubon Costa May 3, 2023 at 4:12 PM

          You do not need to run the program every day manually. You can automatically run the program whenever you open your PC. Follow the steps below to do so.
          Step 1:

          1. From the Start Menu type Run to open the Windows Run dialog box and paste the following path:

          %AppData%\Microsoft\Windows\Start Menu\Programs\Startup

          2. Click OK.

          Step 2: Place your Excel File in the Startup folder.

          Step 3:

          1. Open your Excel file Macro Window.

          2. In the Project Explorer, double-click the ThisWorkbook object to open the code window for the workbook.

          3. In the code window, select Workbook from the drop-down list at the top.

          4. Then, select the Workbook_Open event from the drop-down list on the right.

          Step 4:

          1. Type or paste the VBA code that you want to run when the file is opened between the Private Sub Workbook_Open() and End Sub lines.

          2. Save the Excel file.

          Private Sub Workbook_Open()
              "Your Full VBA Codes"
          End Sub

          Now, whenever you open your PC, the VBA code you wrote will be automatically executed. Hope this will resolve your issue. Please let us know your further feedback, our ExcelDemy Team is always relentless to welcome your issues.

          Regards,
          ExcelDemy Team

        • 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

  11. I have two questions –
    1. Is it possible to send recurring daily reminders? Say from 25th of the current month, until 10th of the next month?
    2. If the excel is saved in OneDrive, and opened via Web browser, will the macros still run?

    I will really, REALLY appreciate a quick response.

    • Hi PRATIBHU
      I hope you are doing well.

      This is your 2nd question and its answer.

      If the excel is saved in OneDrive, and opened via Web browser, will the macros still run?

      Answer: It depends on the type of macro and how it was created.
      If the macro is a VBA macro, it will not run in the web browser version of Excel. VBA macros can only run in the desktop version of Excel on a Windows or Mac computer.

      Coming to your first question, our team is working on the macro. We will reach you soon hopefully.

  12. dears,
    for content can we add more than one cell? say that cell with name of the task and cell contains the task itself ?

    also if i saved the excel on Team channel do i have to open it every day so the macros work ?

    • Answer 1: To add one or more columns for the mail content section, you must add variables. Let’s say you want to add one more column so we will add VBA codes with one more variable xMailContent2. So, your data set may look like the following image, one column with the task name, and another with the task details.
      So, apply the following VBA codes to perform your requirements.

      Public Sub SendReminderMail()
      'Declare the variables
          Dim XDueDate As Range
          Dim XRcptsEmail As Range
          Dim xMailContent As Range
          Dim xMailContent2 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 task Name, insert a input box
          Set xMailContent = Application.InputBox("In your email, choose the column with the Task Name:", "ExcelDemy", , , , , , 8)
          If xMailContent Is Nothing Then Exit Sub
          
          'To enter the task details, insert a input box
          Set xMailContent2 = Application.InputBox("In your email, choose the column with the Task Details:", "ExcelDemy", , , , , , 8)
          If xMailContent2 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 xMailContent2 = xMailContent2(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 & "Text : " & xMailContent2.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

      Answer 2: You cannot run a file automatically from a Team Chanel server. However, you can automatically run the program whenever you open your PC. Follow the steps below to do so.
      Step 1:

      1. From the Start Menu type Run to open the Windows Run dialog box and paste the following path:

      %AppData%\Microsoft\Windows\Start Menu\Programs\Startup

      2. Click OK.

      Step 2: Place your Excel File in the Startup folder.

      Step 3:

      1. Open your Excel file Macro Window.

      2. In the Project Explorer, double-click the ThisWorkbook object to open the code window for the workbook.

      3. In the code window, select Workbook from the drop-down list at the top.

      4. Then, select the Workbook_Open event from the drop-down list on the right.

      Step 4:

      1. Type or paste the VBA code that you want to run when the file is opened between the Private Sub Workbook_Open() and End Sub lines.

      2. Save the Excel file.

      Private Sub Workbook_Open()
          "Your Full VBA Codes"
      End Sub

      Now, whenever you open your PC, the VBA code you wrote will be automatically executed. Hope this will resolve your issue. Please let us know your further feedback, our ExcelDemy Team is always relentless to welcome your issues.

      Regards,
      ExcelDemy Team

  13. dears
    Thanks a lot for your support my previous inquiries was resolved ,
    i added 18 variables !!!

    more Qs can we add multiple reminder for the same due date ? i mean 7 days before and 3 days before for example ?

    BR
    Nouran

    • Dear Nouran,

      I am delighted to hear that your previous queries have been resolved. It seems like you have invested a lot of patience and effort, considering you had to work with 18 variables to accomplish your task!

      Regarding your further queries, you want to send reminder emails multiple times with different time intervals. For instance, you want to send the first mail three days prior to the deadline, the second mail five days prior to the deadline, and the final mail seven days before the deadline. To accomplish this, we have utilized the following dataset, and as of today, it is the 8th of May, 2023.

      Now, you have to modify the If section of the codes as in the image below.

      Here, is the full updated code.

      Public Sub SendReminderMail_Multiple()
      'Declare 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
                  Dim xDaysDiff As Integer
                  xDaysDiff = DateDiff("d", Date, CDate(xValDateRng))
                  If xDaysDiff = 3 Or xDaysDiff = 5 Or xDaysDiff = 7 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

      I hope that this response has resolved your current queries. If you would like to automate the program, please refer to our previous replies for detailed instructions. Additionally, please inform us if you have any further questions.

      Regards,
      ExcelDemy Team

  14. is it possible to have the text insert a data range and not just 1 cell of information?

    • Dear Mike,

      I understand you wish to insert a range to have the text as input. Here is the modified code to do so.

      
      Public Sub SendReminderMail()
          'Declare the variables
          Dim XDueDates As Range
          Dim XRcptsEmails As Range
          Dim xMailContents As Range
          Dim xMailContent2s As Range
      
          Dim xDueDate As Range
          Dim xRcptEmail As Range
          Dim xMailContent As Range
          Dim xMailContent2 As Range
          
          Dim xRngDn As Range
          Dim xCrtOut As Object
          Dim xValDateRng As Variant
          Dim xValSendRng As Variant
          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 an input box
          Set XDueDates = Application.InputBox("Select the range for Deadline/Due Date column:", "ExcelDemy", , , , , , 8)
          If XDueDates Is Nothing Then Exit Sub
          
          'Insert an input box for selecting the recipients
          Set XRcptsEmails = Application.InputBox("Choose the range for the email addresses of the recipients:", "ExcelDemy", , , , , , 8)
          If XRcptsEmails Is Nothing Then Exit Sub
          
          'To enter the task Name, insert an input box
          Set xMailContents = Application.InputBox("In your email, choose the range with the Task Name:", "ExcelDemy", , , , , , 8)
          If xMailContents Is Nothing Then Exit Sub
          
          'To enter the task details, insert an input box
          Set xMailContent2s = Application.InputBox("In your email, choose the range with the Task Details:", "ExcelDemy", , , , , , 8)
          If xMailContent2s Is Nothing Then Exit Sub
          
          'Count rows for the due dates
          xFinalRw = XDueDates.Rows.Count
          
          '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 = XDueDates.Cells(k).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  0 Then
                      xValSendRng = XRcptsEmails.Cells(k).Value
                      'Create the subject, body and text contents with the required variables
                      xSubEmail = xMailContents.Cells(k).Value & " on " & xValDateRng
                      CrVbLf = ""
                      xMsg = ""
                      xMsg = xMsg & "Dear " & xValSendRng & CrVbLf
                      xMsg = xMsg & "Text: " & xMailContents.Cells(k).Value & CrVbLf
                      xMsg = xMsg & "Text: " & xMailContent2s.Cells(k).Value & CrVbLf
                      xMsg = xMsg & ""
                      '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 k
          Set xCrtOut = Nothing
      End Sub
      

      Also, you can insert a data range by adding variables. To know more about this, see this comment.

  15. i was able to generate email earlier but now when i run macro it does not open outlook email with all information anymore. i checked references as well and i have checked outlook so it should be working so not sure why it is not opening outlook email. can someone help?

    • Hello ANA, Thank you for reaching out to us. We understand that you were previously able to generate emails successfully, but now when running the macro, Outlook does not open with all the information as before.
      To identify the problem, it’s challenging to determine the exact cause from here. However, one possibility could be that your deadlines may not have been updated correctly, causing the following lines of code to not execute as expected:

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

      As you can see, emails will only be sent when the deadline is 7 days or less from the current date. If your deadlines fall within this range and you’re still experiencing issues, we recommend sharing your file with us through the Exceldemy Forum. This will allow us to directly analyze the problem and provide appropriate solutions. Thank you for your cooperation.
      Regards
      Aniruddah

  16. why when I try to run the code, the outlook application doesn’t pop out?

    • Hi AMY,
      Most probably you haven’t disable the “.Send” statement in the VBA code. Disable the “.Send” statement by putting an apostrophe before it.
      disable .Send statement

      Regards
      Rafiul Hasan
      ExcelDemy

  17. Hi, how do I make the code work for workbook with multiple worksheets? Thanks.

    • Hi,
      You can use the following code. After sending mail for the first sheet, it will show message box for next sheet. Insert your ranges carefully for different sheets in the appeared input message box.

      Public Sub SendReminderMailForAllSheets()
          Dim ws As Worksheet
          For Each ws In ThisWorkbook.Sheets
              ' Call the existing SendReminderMail subroutine for each sheet
              SendReminderMail ws
          Next ws
      End Sub
      
      Public Sub SendReminderMail(ws As Worksheet)
          '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 an input box
          Set XDueDate = Application.InputBox("Select the column for Deadline/Due Date date column in " & ws.Name & ":", "ExcelDemy", , , , , , 8)
          If XDueDate Is Nothing Then Exit Sub
          'Insert an input box for selecting the recipients
          Set XRcptsEmail = Application.InputBox("Choose the column for the email addresses of the recipients in " & ws.Name & ":", "ExcelDemy", , , , , , 8)
          If XRcptsEmail Is Nothing Then Exit Sub
          'To enter the text mail, insert an input box
          Set xMailContent = Application.InputBox("In your email, choose the column with the reminded text in " & ws.Name & ":", "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 the 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 ' Uncomment this line if you want to send emails automatically without displaying
                      End With
                      Set xMailSections = Nothing
                  End If
              End If
          Next k
          Set xCrtOut = Nothing
      End Sub
      

      Regards
      Rafiul Hasan
      ExcelDemy

  18. how do i make code
    1) to send mail by just selecting a row (specifically email id column in that row) so that information from different columns in that row could be sent .
    2) also how to enable multiple selection of rows.

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Apr 3, 2024 at 2:18 PM

      Hello Vyshnav V S

      Thanks for visiting our blog and sharing an exciting problem. I have developed an Excel VBA Sub-procedure that meets both requirements.

      To send email using VBA, you can use the Send property. When sending an email, the Microsoft Outlook dialog box will appear. Lastly, you must click on Allow to send email.

      SOLUTION Overview:

      1. Enabling Multi-row Selection:
      2. Send Emails by Selecting a Row (specifically the email ID column in that row):

      NOTE: Additionally, the sub-procedure will validate your selection. If you select another column instead of the ID column (assuming it is column B), the message will pop up saying to choose the intended column.

      Excel VBA Sub-procedure:

      Sub SendEmailUsingSelectedRows()
      
          Dim selectedRange As Range
          Dim outlookApp As Object
          Dim outlookMail As Object
          Dim row As Range
          
          If Selection.Column <> 2 Then
              MsgBox "Please select a range of cells in the Email ID column (Column B).", vbCritical
              Exit Sub
          End If
          
          For Each row In Selection.Rows
      
              If Not IsEmpty(row.Cells(1, 1).Value) Then
      
                  Set outlookApp = CreateObject("Outlook.Application")
                  Set outlookMail = outlookApp.CreateItem(0)
      
                  outlookMail.To = row.Cells(1, 1).Value
                  outlookMail.Subject = "Your ExcelDemy Registration Information"
      
                  outlookMail.Body = "Greetings," & vbCrLf & vbCrLf & _
                                      row.Cells(1, 2).Value & vbCrLf & vbCrLf & _
                                      "Deadline: " & row.Cells(1, 3).Value & vbCrLf & vbCrLf & _
                                      "ExcelDemy Team"
                  On Error Resume Next
                  outlookMail.Display
                  outlookMail.Send
      
                  Set outlookMail = Nothing
                  Set outlookApp = Nothing
              
              End If
          Next row
      
      End Sub

      Hopefully, the sub-procedure will meet your expectations. I have attached the solution workbook as well. Good luck.

      DOWNLOAD SOLUTION WORKBOOK

      Regards
      Lutfor Rahman Shimanto
      Excel & VBA Developer
      ExcelDemy

  19. How do i make a code for multi selection ( like by selecting one row , mail should be sent to recipient with data fetched from needed columns in that same row )

    Hoping for a fast reply

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Apr 3, 2024 at 5:19 PM

      Dear Vyshnav V S

      Thanks for clarifying your requirements further. I have modified the previously given code to fulfil your goal. Now, you can select any column containing email IDs, and all the other required information will be retrieved from columns C and D.

      SOLUTION Overview:

      Excel VBA Sub-procedure:

      Sub SendEmailUsingSelectedRows()
      
          Dim selectedRange As Range
          Dim outlookApp As Object
          Dim outlookMail As Object
          Dim row As Range
          
          For Each row In Selection.Rows
              
              If Not IsEmpty(row.Cells(1, 1).Value) Then
      
                  Set outlookApp = CreateObject("Outlook.Application")
                  Set outlookMail = outlookApp.CreateItem(0)
      
                  outlookMail.To = row.Cells(1, 1).Value
                  outlookMail.Subject = "Your ExcelDemy Registration Information"
      
                  outlookMail.Body = "Greetings," & vbCrLf & vbCrLf & _
                                      Range("D" & row.row).Value & vbCrLf & vbCrLf & _
                                      "Deadline: " & Range("E" & row.row).Value & vbCrLf & vbCrLf & _
                                      "ExcelDemy Team"
                  On Error Resume Next
                  outlookMail.Display
                  outlookMail.Send
      
                  Set outlookMail = Nothing
                  Set outlookApp = Nothing
              
              End If
          Next row
      
      End Sub

      Make changes to the code if necessary. I hope the code will fulfil your goal; good luck.

      Regards
      Lutfor Rahman Shimanto
      Excel & VBA Developer
      ExcelDemy

  20. How do i make a remainder mail as above with different column(start date , planned due date , task id , mail content , recipient mail id , reviewer ,etc).Here i just need to run the code so that mail need to be sent to each recipients mail id who have passed the due date.(it should be automatically done without manual selection of each column for sending mail to one person itself)

    Note : mail need to be sent to all recipient who have passed the due date in planned due date column.

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Apr 4, 2024 at 11:38 AM

      Dear Vyshnav V S

      As requested, I have improved the existing sub-procedure (previously provided) to fulfil your new requirements.

      SOLUTION Overview:

      NOTE: If the Microsoft Outlook dialog box appears, click Allow to send an email.

      Improved Excel VBA Sub-procedure:

      Sub SendReminderEmails()
      
          Dim outlookApp As Object
          Dim outlookMail As Object
          Dim ws As Worksheet
          Dim lastRow As Long
          Dim i As Long
          
          Set ws = ThisWorkbook.Sheets("DATA")
          
          lastRow = ws.Cells(ws.Rows.Count, "F").End(xlUp).row
          
          For i = 5 To lastRow
              If ws.Cells(i, "C").Value < Date Then
                  Set outlookApp = CreateObject("Outlook.Application")
                  Set outlookMail = outlookApp.CreateItem(0)
                  
                  outlookMail.To = ws.Cells(i, "F").Value
                  outlookMail.Subject = "Reminder: " & ws.Cells(i, "E").Value
                  
                  outlookMail.Body = "Dear " & vbCrLf & vbCrLf & _
                                     "This is a reminder that Task ID " & ws.Cells(i, "D").Value & " is overdue." & vbCrLf & vbCrLf & _
                                      ws.Cells(i, "E").Value & "," & vbCrLf & _
                                     "Start Date: " & ws.Cells(i, "B").Value & vbCrLf & _
                                     "Planned Due Date: " & Format(ws.Cells(i, "C").Value, "DD-MMM-YYYY") & vbCrLf & _
                                     "Mail Content: " & ws.Cells(i, "F").Value & vbCrLf & _
                                     "Reviewer: " & ws.Cells(i, "G").Value & vbCrLf & vbCrLf & _
                                     "Please take necessary action." & vbCrLf & vbCrLf & _
                                     "Regards," & vbCrLf & _
                                     "Vyshnav V S"
                  
                  outlookMail.Display
                  
                  On Error Resume Next
                  outlookMail.Send
                  
                  Set outlookMail = Nothing
                  Set outlookApp = Nothing
              End If
          Next i
      
      End Sub

      I hope the sub-procedure will reach your goal. I have attached the solution workbook as well; good luck.

      DOWNLOAD SOLUTION WORKBOOK

      Regards
      Lutfor Rahman Shimanto
      Excel & VBA Developer
      ExcelDemy

      • i need a vba code such that hyperlink contain alm link plus task id . but in the hyperlink that is displayed , there should be only task id ( but this task id has same hyperlink address as of earlier).task id is taken from excel.alm link is assumed.this should be also included in the above code

        hyperlink : almlink + task id
        But in the hyperlink test to display : task id

        • Lutfor Rahman Shimanto
          Lutfor Rahman Shimanto Apr 18, 2024 at 10:14 AM

          Dear Vyshnav V S

          It is good to see you again. Thanks for sharing further requirements. You want to improve the existing VBA code to add a hyperlink in the email content. The hyperlink should display only the task ID, but when clicked, it will direct to the ALM link along with the task ID.

          I have improved the previously given code to fulfil your goal. When sending an email, ensure that you uncomment the line that contains the “.Send” property. Also, change the existing base AML link with the intended one.

          SOLUTION Overview:

          Improved Excel VBA Sub-procedure:

          Sub SendReminderEmails()
          
              Dim outlookApp As Object
              Dim outlookMail As Object
              Dim ws As Worksheet
              Dim lastRow As Long
              Dim i As Long
              Dim almBaseLink As String
          
              almBaseLink = "http://alm.example.com/task?id="
          
              Set ws = ThisWorkbook.Sheets("DATA")
          
              lastRow = ws.Cells(ws.Rows.Count, "F").End(xlUp).row
          
              For i = 5 To lastRow
                  If ws.Cells(i, "C").Value < Date Then
                      Set outlookApp = CreateObject("Outlook.Application")
                      Set outlookMail = outlookApp.CreateItem(0)
          
                      outlookMail.To = ws.Cells(i, "F").Value
                      outlookMail.Subject = "Reminder: " & ws.Cells(i, "E").Value
          
                      Dim taskID As String
                      taskID = ws.Cells(i, "D").Value
                      Dim taskALMLink As String
                      taskALMLink = almBaseLink & taskID
          
                      outlookMail.Body = "Dear " & vbCrLf & vbCrLf & _
                                          "This is a reminder that Task ID " & ws.Cells(i, "D").Value & " is overdue." & vbCrLf & vbCrLf & _
                                          ws.Cells(i, "E").Value & "," & vbCrLf & _
                                          "Start Date: " & ws.Cells(i, "B").Value & vbCrLf & _
                                          "Planned Due Date: " & Format(ws.Cells(i, "C").Value, "DD-MMM-YYYY") & vbCrLf & _
                                          "Mail Content: " & ws.Cells(i, "F").Value & vbCrLf & _
                                          "Reviewer: " & ws.Cells(i, "G").Value & vbCrLf & vbCrLf & _
                                          "Please take necessary action." & vbCrLf & vbCrLf & _
                                          "Regards," & vbCrLf & _
                                          "Vyshnav V S" & vbCrLf & vbCrLf & _
                                          "Click here to view the task details: " & _
                                          "<a href=""" & taskALMLink & """>" & taskID & "</a>"
          
                      outlookMail.HTMLBody = outlookMail.Body
                      outlookMail.Display
          
                      On Error Resume Next
                      'outlookMail.Send
          
                      Set outlookMail = Nothing
                      Set outlookApp = Nothing
                  End If
              Next i
          
          End Sub

          I hope the improved sub-procedure will reach your goal. I have attached the solution workbook; good luck.

          DOWNLOAD SOLUTION WORKBOOK

          Regards
          Lutfor Rahman Shimanto
          Excel & VBA Developer
          ExcelDemy

  21. How to add Request for Delivery Receipt and Read Receipt in Outlook into the above remainder mail.

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Apr 8, 2024 at 4:16 PM

      Dear Vyshnav V S

      It is great to see you again. Thanks for reaching out and sharing another exciting problem.

      To add a Request for Delivery Receipt and a Read Receipt in the Outlook Application, we must use the ReadReceiptRequested and OriginatorDeliveryReportRequested properties. Do not worry! Based on your new requirements, I have enhanced the existing sub-procedure (I provided earlier).

      SOLUTION Overview:

      Excel VBA SUb-procedure:

      Sub SendReminderEmails()
      
          Dim outlookApp As Object
          Dim outlookMail As Object
          Dim ws As Worksheet
          Dim lastRow As Long
          Dim i As Long
      
          Set ws = ThisWorkbook.Sheets("DATA")
      
          lastRow = ws.Cells(ws.Rows.Count, "F").End(xlUp).row
      
          For i = 5 To lastRow
              If ws.Cells(i, "C").Value < Date Then
                  Set outlookApp = CreateObject("Outlook.Application")
                  Set outlookMail = outlookApp.CreateItem(0)
      
                  outlookMail.To = ws.Cells(i, "F").Value
                  outlookMail.Subject = "Reminder: " & ws.Cells(i, "E").Value
      
                  outlookMail.Body = "Dear " & vbCrLf & vbCrLf & _
                                     "This is a reminder that Task ID " & ws.Cells(i, "D").Value & " is overdue." & vbCrLf & vbCrLf & _
                                     ws.Cells(i, "E").Value & "," & vbCrLf & _
                                     "Start Date: " & ws.Cells(i, "B").Value & vbCrLf & _
                                     "Planned Due Date: " & Format(ws.Cells(i, "C").Value, "DD-MMM-YYYY") & vbCrLf & _
                                     "Mail Content: " & ws.Cells(i, "F").Value & vbCrLf & _
                                     "Reviewer: " & ws.Cells(i, "G").Value & vbCrLf & vbCrLf & _
                                     "Please take necessary action." & vbCrLf & vbCrLf & _
                                     "Regards," & vbCrLf & _
                                     "Vyshnav V S"
                  
                  
                  outlookMail.ReadReceiptRequested = True
                  outlookMail.OriginatorDeliveryReportRequested = True
      
                  outlookMail.Display
      
                  On Error Resume Next
                  outlookMail.Send
      
                  Set outlookMail = Nothing
                  Set outlookApp = Nothing
              End If
          
          Next i
          
      End Sub

      I hope you have found the solution you were looking for. The solution workbook is attached; good luck.

      DOWNLOAD SOLUTION WORKBOOK

      Regards
      Lutfor Rahman Shimanto
      Excel & VBA Developer
      ExcelDemy

  22. How to add user interface instead of running the excel macro in this above remainder mail

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Apr 17, 2024 at 11:01 AM

      Hello Vyshnav V S

      It is good to see you again. When sending a reminder email, you want to add a user interface instead of running the Excel VBA Sub-procedure. To achieve this goal, you must use an Excel UserForm. I have designed a user interface in an Excel File.

      SOLUTION Overview:

      I hope the solution will help you reach your goal. I have also attached the solution workbook; good luck.

      DOWNLOAD SOLUTION WORKBOOK

      Regards
      Lutfor Rahman Shimanto
      Excel & VBA Developer
      ExcelDemy

  23. how to add a code to access ALM/work items/search work items / task id / task ticket from the task id details taken from the excel sheet and copy and paste the link details of that task ticket in mail.

    hope for fast reply

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Apr 16, 2024 at 3:14 PM

      Dear Vyshnav V S

      It is good to see you again. You want to upgrade the previously given VBA code to include a feature that retrieves task details from an ALM system or a similar work item system.

      To do so, connect to the ALM or work item system to fetch task details within the VBA. Later, you can develop a function to find the task details based on the task ID from the Excel sheet and generate a link to the task’s details. The implementation will depend on the system used and its access methods (like APIs).

      Hopefully, the idea will help you; good luck.

      Regards
      Lutfor Rahman Shimanto
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo