October 2, 2014

# Using Dates with Excel VBA to Automate Email Reminders

We have learnt how to send reminders using Excel with conditional formatting, for example.
If you look at the logic of the code you’ll find that the handling of the dates is the most important aspect. So let’s study how Excel VBA handles dates so that we can automate sending of such reminders and also understand the background. Many people wish to know how Excel and Excel VBA handle dates internally.

Let’s see how Excel handles dates normally in a worksheet.

• In Sheet2 we have two dates Date1 and Date2 in cells A2 and B2.
• Subtracting A2 from B2 directly gives us the difference in nmumber of days between the two dates.
• If you select both the cells A2 and B2, right click and select format cells, you can view the the Format Cells window.
• Both the cells reveal ‘date’ formatting.
• Now if you click on General, you can see how a number is displayed below the sample area.
• If you click OK, both the cells in A2 and B2 reveal how the dates can be expressed as numbers.
• If you now subtract the ‘datevalue’ in A2 from the ‘datevalue in B2, you again get 260 days.

You can perform the arithmetic operation in cell D2 to confirm the result of 260. If you had written ‘=DATEVALUE(“01/18/2014”)’ and ‘=DATEVALUE(“10/05/2014”) in cells E2 and F2 you would have also got the numerical values 41657 and 41917 in the respective cells.

1. Using VBA we can also convert the date values in column F or column 6 into numerical values. In a similar fashion we can convert the date TODAY or the system’s date into a numerical value. To achieve this we need two variables each, mydate1 and mydate2, for the dates in our worksheet.
2. We declare mydate1 as Date and mydate2 as LONG.
3. Next using a ‘for next’ loop we assign the value in a cell to mydate1.
4. In the following step we assign the mydate1 value to mydate2. The conversion of the data types from DATE to LONG happens automatically internally in VBA.
5. We do the same with TODAY or the system’s date, and get a numerical value for TODAY.
6. The next step is simple. To know the difference in days between the two dates we subtract the system’s date from the ‘date of payment’.
7. Now we can use simple logic with an IF statement. If the result of the subtraction is 3, 30 or 60 we can perform actions like formatting our cells and at the same time send an email reminder via Outlook, Gmail, Yahoo Mail or Hotmail.

View the training video below and then study the code:

Watch the above training video on YouTube.

Sub datesexcelvba()
Dim myApp As Outlook.Application, mymail As Outlook.MailItem
Dim mydate1 As Date
Dim mydate2 As Long
Dim datetoday1 As Date
Dim datetoday2 As Long

Dim x As Long
lastrow = Sheets(“Sheet1”).Cells(Rows.Count, 1).End(xlUp).Row
For x = 2 To lastrow

mydate1 = Cells(x, 6).Value
mydate2 = mydate1

Cells(x, 10).Value = mydate2

datetoday1 = Date
datetoday2 = datetoday1

Cells(x, 9).Value = datetoday2

If mydate2 – datetoday2 = 3 Then

Set myApp = New Outlook.Application
Set mymail = myApp.CreateItem(olMailItem)
mymail.To = Cells(x, 5).Value

With mymail
.Subject = “Payment Reminder”
.Body = “Your credit card payment is due.” & vbCrLf & “Kindly ignore if already paid.” & vbCrLf & “Dinesh Takyar”
.Display
‘.send
End With

Cells(x, 7) = “Yes”
Cells(x, 7).Interior.ColorIndex = 3
Cells(x, 7).Font.ColorIndex = 2
Cells(x, 7).Font.Bold = True
Cells(x, 8).Value = mydate2 – datetoday2
End If
Next
Set myApp = Nothing
Set mymail = Nothing
End Sub

## 15 thoughts on “Using Dates with Excel VBA to Automate Email Reminders”

1. Dhvanil says:

Dear Sir,

Need to resolve this error while running macro
user-defined type not defined

Thanks

1. Anjani Kumar Chauna says:

In the Visual Basic Editor, on the Tools menu, click References.
Click to select the Microsoft Outlook 15.0 Object Library check box, and then click OK.

2. ziv says:

Hi,

every time I open the excel file
I am getting “run-time error ’13’:
Type-Mismatch.

my line 13 shows: “mydate1 = Cells(x, 8).Value” which matches with –
“mydate1 = Cells(x, 6).Value”

What is the cause for this error ?

the full code:

Private Sub Workbook_Open()

Dim mydate1 As Date
Dim mydate2 As Long
Dim datetoday1 As Date
Dim datetoday2 As Long

Dim x As Long
lastrow = Sheets(“List”).Cells(Rows.Count, 1).End(xlUp).Row
For x = 6 To lastrow

mydate1 = Cells(x, 8).Value
mydate2 = mydate1

datetoday1 = Date
datetoday2 = datetoday1

If mydate2 – datetoday2 < 30 And Cells(x, 8).Value “” Then

Cells(x, 8).Interior.ColorIndex = 3
Cells(x, 8).Font.ColorIndex = 2
Cells(x, 8).Font.Bold = True

Else

Cells(x, 8).Interior.ColorIndex = 2
Cells(x, 8).Font.ColorIndex = 1
Cells(x, 8).Font.Bold = False

End If
Next
End Sub

3. michael says:

In your video you provide code to the foregoing to automatically send an Outlook email when the trigger date–in your case the 3rd day prior to a due date–occurs. However, from the code you provide, it appears that an email will be sent each time the workbook/worksheet is opened on the trigger date. Is there some vba code you could suggest to insert that will prevent re-sending email after it’s sent the first time the workbook is opened on the trigger date? Thanks

4. Jared Morton says:

I am getting a ” User-defined Type not defined” error message for the sub. how do I fix this?

5. kern says:

I have the following code and have Microsoft Outlook Library activated under Reference tab as well but email not sent.

Sub datesexcelvba()

Dim myApp As Outlook.Application, mymail As Outlook.MailItem

Dim mydate1 As Date
Dim mydate2 As Long
Dim datetoday1 As Date
Dim datetoday2 As Long

Dim x As Long
Lastrow = Sheets(“Sheet1”).Cells(Rows.Count, 1).End(xlUp).Row
For x = 2 To Lastrow

mydate1 = Cells(x, 6).Value
mydate2 = mydate1

Cells(x, 9).Value = mydate2

datetoday1 = Date
datetoday2 = datetoday1

Cells(x, 10).Value = datetoday2

If mydate2 – datetoday2 = 30 Then

Set myApp = New Outlook.Application
Set mymail = myApp.CreateItem(olMailItem)
mymail.To = Cells(x, 5).Value

With mymail
.Subject = “Permit & license renewal reminder”
.Body = “This is a reminder for you to renew the permit or licence” & vbCrLf & “Kindly ignore if renewed” & vbCrLf & “KS”
.Display
‘.send
End With

Cells(x, 7) = “Yes”
Cells(x, 7).Interior.ColorIndex = 3
Cells(x, 7).Font.ColorIndex = 2
Cells(x, 7).Font.Bold = True
Cells(x, 8).Value = mydate2 – datetoday2
End If
Next
Set myApp = Nothing
Set mymail = Nothing
End Sub

1. jose pablo says:

Muy bueno

6. Amrit Kaur says:

Hi Sir,

I love all your videos, very clear with explanation and voice. thank you so much.

i have already created one user form for data entry for my Tele calling team.
which my entire team using really well.
i have one workbook named Tele Calling Data and it has 3 sheets name “Inbound” “Outbound” & “Follow UP”
each sheet has headers from (“A:K”) : date, Telecaller Name,Customer Name,Contact Number,Emaiil id, Location, Source, status,requirement,remarks & feedback

in my current userform named telecalling data form i have three pages (as it is multi page form) i have created textboxes in the same series as i have in excel, all of them are text boxes accept status requirements & remarks

and my entire report is based on status…
the Status drop box has options :- hot, warm, cold, visit done & NA

now i am trying to make another user form called daily report

in which combo box Date should give the option of dates which are already entered in 3 excel sheets

below the combobox date i have hot inbound, cold inbound, warm inbound, visit done inbound & NA inbound

below that is another row of labels & textboxes
hot outbound , cold outbound , warm outbound , visit done outbound & NA outbound

below that is another row of labels & textboxes
hot FollowUP, cold FollowUP, warm FollowUP, visit done FollowUP & NA FollowUP

And at last i have command button called get dat.

Now if i select the date in combo box and click on get data i shud get details as below:

hot inbound : total number of “hot” in inbound sheet
and so on
for warm inbound, visit done inbound, cold inbound & NA inbound

same for Outbound & follow up.

please reply back if you are unable to get what i have asked ( as it is confusing with the long story :):):) )

Regards,
Amrit Kaur

7. sam says:

hi sir,

if mydate1 cell is blank then next cell, what is the code ?

Date of payment Reminders
20/03/2017
21/03/2017

17/03/2017
12/03/2017
15/03/2017
20/03/2017

8. Amit Kumar says:

i have tried this in the worksheet but it is showing below error

“Compile Error: User-defined type not defined”

Sub datesexcelvba()
Dim myApp As Outlook.Application, mymail As Outlook.MailItem
Dim mydate1 As Date
Dim mydate2 As Long
Dim mydatetoday1 As Date
Dim mydatetoday2 As Long

Dim x As Long
lastrow = Sheets(“sheet1”).Cells(Rows.Count, 1).End(xlUp).Row
For x = 2 To lastrow

mydate1 = Cells(x, 6).Value
mydate2 = mydate1

Cells(x, 10).Value = mydate2

datetoday1 = Date
datetoday2 = datetoday1

Cells(x, 9).Value = datetoday2

If mydate2 – datetoday2 = 3 Then
Set myApp = Outlook2016.Application
Set mymail = myApp.CreateItem(olMailItem)
mymail.To = Cells(x, 5).Value

With mymail
.Subject = “Payment Reminder”
.Body = “Your credit card payment is due.” & vbCrLf & “Kindly ignore if already paid.” & vbCrLf & “Dinesh Takyar”
.Display
‘.send
End With

Cells(x, 7) = “Yes”
Cells(x, 7).Interior.ColorIndex = 3
Cells(x, 7).Font.ColorIndex = 2
Cells(x, 7).Font.Bold = True
Cells(x, 8).Value = mydate2 – datetoday2

End If
Next
Set myApp = Nothing
Set mymail = Nothing
End Sub

9. Niaz says:

Hi ,

This VBA is running fine for me, but i want to have this run automatically without opening the excel , is this possible ?

1. Anand says:

Bro r u getting solution for that

10. chili says:

hi, its way easier if you post the whole excel file… 🙂