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.
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.
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
Read More: Automatically Send Emails from Excel Based on Cell Content (2 Methods)
Similar Readings
- How to See Who Is in a Shared Excel File (With Quick Steps)
- Enable Share Workbook in Excel
- How to Share Excel File Online (2 Easy Methods)
- Share Excel File for Multiple Users
- How to Apply Macro to Send Email from Excel with Attachment
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.
- In the recipients’ email input box, select the Recipients column in the range B5:B7.
- Press Enter.
- Select the range C5:C7 in the final input box for the Content column.
- Finally, click OK.
- As a result, all of your emails will be sent as reminder emails before the due dates.
- 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.
- 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.
- 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
- How to Send Email If Conditions Met in Excel (3 Easy Methods)
- Send Automatic Email from Excel to Outlook (4 Methods)
- How to Send Email from Excel with Body Using a Macro (with Easy Steps)
- How to Automatically Send Email from Excel Based on Date
- Macro to Send Email from Excel with Body (3 Useful Cases)
- How to Send Multiple Emails from Excel Spreadsheet (2 Easy Methods)
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:
Modified Code:
Please, give us feedback, if any further query needed. The ExcelDemy team is always in your assistance.
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.
Hope this will work with your requirements.
Please, keep giving your feedbacks. The ExcelDemy team is always concerned about your queries.
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
# 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.
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.
Please, see the image below for a detailed understanding.
After running the code, you will see results with the attachment available.
Moreover, we have a dedicated article based on your requirement. Please, go through the article to learn in detail.
https://www.exceldemy.com/macro-to-send-email-from-excel-with-attachment/
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
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]
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.
2. Therefore, you will see the emails on display before sending them, with the selected different subjects.
Please feel free to ask more queries. Our ExcelDemy Team will reach you as soon as possible.
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.
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.
HOW TO AUTOMATICALLY SELECTDUE DATE COLUMN WITHOUT INPUTBOX AND RUN A CODE
Thank you Prachi Davade for your wonderful question. You can change the
to
This will do what you desire. If you have further queries, let us know.
Regards
Bishawajit, on behalf of ExcelDemy
Hi, how to add reminder time for the email? (e.g. want to send the email on January 13th, 2023 at 8.00 am)
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
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?
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
Hi, how to add CC and subject column. Please advice
You can use the following code to include the subject and CC columns.
The following image shows the output.

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
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 ?
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
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.
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
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.
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
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.
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.
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.
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
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.
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:
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
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.
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.
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.
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
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.
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
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.
Also, you can insert a data range by adding variables. To know more about this, see this comment.
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:
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