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.
- 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.
- We declare mydate1 as Date and mydate2 as LONG.
- Next using a ‘for next’ loop we assign the value in a cell to mydate1.
- 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.
- We do the same with TODAY or the system’s date, and get a numerical value for TODAY.
- 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’.
- 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.
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
.Subject = “Payment Reminder”
.Body = “Your credit card payment is due.” & vbCrLf & “Kindly ignore if already paid.” & vbCrLf & “Dinesh Takyar”
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
Set myApp = Nothing
Set mymail = Nothing
Download a sample file by clicking on the Excel icon: