I borrowed this code from someone who was doing something similar to me; Looking at a date column in Excel and if it is a certain date (currently it's set at today's date) then it sets the word YES in the column to the right of it and in the script if the word YES is detected it fires off an email alert. I would like help modifying this script (I've already tweaked it some) so that it checks this column in Excel for a date that is 4 years after its purchase date. I have one column that shows the purchase date and the column directly to the right of that shows the purchase date + 4 years, call it the replacement date. If the replacement date is => 4 years then I want the script to kick off the alert. What do I need to change?
Dim wsIndex, bodyText
Set objExcel = CreateObject("Excel.Application")
Set objOutlook = CreateObject("Outlook.Application")
objExcel.DisplayAlerts = False
Set objWB = objExcel.Activeworkbook
bodyText = "The following servers are nearing their 4 year replacement:" & vbNewLine & vbNewLine
For wsIndex = 1 To 2
Set objWS = objWB.Worksheets(wsIndex)
For Each vCell In objWS.Range("K2:K" & objWS.Cells(objWS.Rows.Count, "K").End(-4162).Row).Cells
If FormatDateTime(vCell) = FormatDateTime(Date) Then
If vCell.Offset(0, 1).Value <> "YES" Then
bodyText = bodyText & vCell.offset(0, -3).Value & " - " & vCell.offset(0, -2).Value & vbNewLine
vCell.Offset(0, 1).Value = "YES"
Set objMail = objOutlook.CreateItem(olMailItem)
objMail.To = "email@example.com"
objMail.Subject = "Servers expiring soon"
objMail.Body = bodyText
Set objExcel = Nothing
Set objWB = Nothing
Set objWS = Nothing
Set objMail = Nothing
Set objOutlook = Nothing