#1
|
|||
|
|||
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 |
#2
|
||||
|
||||
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?
|
#3
|
||||
|
||||
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] |
#4
|
|||
|
|||
Yes the code works fine, however I want it to distinguish new records from old records that have already been notified.
|
#5
|
|||
|
|||
HI,
Can you attach a redacted copy of your file. This will help us to determine what would work best. |
#6
|
||||
|
||||
Quote:
Quote:
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
Hi thanks file attached below
|
#8
|
|||
|
|||
Hi,
I modified your code see if this works. Code:
Sub MOT() Worksheets("MOT's DUE").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 Cells(xRow, 4).Text = "" Then '(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; Declan.Russell@tfl.gov.uk; Rino.Marcangelo@tfl.gov.uk; Salina.Sandhu@tfl.gov.uk; Sandie.Charlton@tfl.gov.uk" strSubject = "Vehicle MOT expiring soon" email_body = "Hi all," & vbNewLine & vbNewLine & "the following vehicles MOT expires within next 28 days." & vbNewLine & vbNewLine & _ strVehicles Call SendeMail(strEmailAddress, "", "", strSubject, email_body) End Sub |
#9
|
|||
|
|||
Hi thanks seems to be working better now!!
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Formula | Excel | Excel | 1 | 08-14-2014 05:11 PM |
Formula Help | withersd46260 | Excel | 2 | 04-30-2014 08:33 AM |
Formula | muhu | Excel | 1 | 04-14-2014 08:19 AM |
What formula should I use? | TotalONE | Excel | 3 | 11-26-2013 01:30 PM |
Help with Formula | Corca | Excel | 6 | 02-22-2010 09:40 PM |