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