Thread: [Solved] Formula Help
View Single Post
 
Old 12-18-2014, 05:12 AM
odonnest odonnest is offline Windows XP Office 2007
Novice
 
Join Date: Dec 2014
Posts: 4
odonnest is on a distinguished road
Default Formula Help

I have inserted a macro into a spreadsheet to read through a list of vehicles and their tax expiry dates and send me an email to notify me when one is due within 28 days. However I need to put something extra in so the formula does not resend cells that have already been notified, the coding I have I am pasting below, can anyone please help?

Sub Fuels()
Worksheets("FUEL CARDS").Activate
For xRow = 6 To ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
expiry_Date = Cells(xRow, 3).Value
If ((expiry_Date - 28 <= Date) And (Len(Cells(xRow, 4).Value < 4))) Then
Cells(xRow, 4).Value = Date
strVehicles = strVehicles & Cells(xRow, 1).Value & ", "
End If
Next
strEmailAddress = "StephenODonnell@tfl.gov.uk"
strSubject = "Vehicle Fuel Cards expiring soon"
email_body = "Hi all," & vbNewLine & vbNewLine & "the following vehicles Fuel Cards expires within next 28 days." & vbNewLine & vbNewLine & _
strVehicles
Call SendeMail(strEmailAddress, "", "", strSubject, email_body)
End Sub
Reply With Quote