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

Odonnest, you didn't actually say what's wrong with the code you've posted. Doesn't it work? Why not—that is, what's it doing that you don't want it to?

Unless you actually add something to the workbook to flag which records have been included in an email, then test the flag state, there is no way your macro can differentiate between those records for which an email has been sent and those for which one hasn't. Of course, if the macro is run periodically (e.g. every 28 days), you could add logic to the code to exclude records for which the expiry date has passed - but that's still not going to resolve everything if the macro is run early and will create other problems if it's run late...

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Yes the code works fine, however I want it to distinguish new records from old records that have already been notified.

HI,

Can you attach a redacted copy of your file. This will help us to determine what would work best.

That's all very well but, as I have already said:
Unless you take either or those approaches, you won't be able to exclude anything.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi thanks file attached below

Attached Files

Copy of FLEET INFO 2014.xlsm (142.2 KB, 10 views)

Hi,

I modified your code see if this works.

Hi thanks seems to be working better now!!