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, 9).Value = mydate2

datetoday1 = Date
datetoday2 = datetoday1

Cells(x, 10).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

Download a sample file by clicking on the Excel icon:

7 thoughts on “Using Dates with Excel VBA to Automate Email Reminders

  1. ziv

    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

    Reply
  2. michael

    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

    Reply
  3. kern

    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

    Reply
  4. Amrit Kaur

    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.

    Hope you get what i am trying to make,… please help me… i am stucked.

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

    Regards,
    Amrit Kaur

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *