Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-18-2014, 05:12 AM
odonnest odonnest is offline Formula Help Windows XP Formula Help Office 2007
Novice
Formula Help
 
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
  #2  
Old 12-20-2014, 07:52 PM
BobBridges's Avatar
BobBridges BobBridges is offline Formula Help Windows 7 64bit Formula Help Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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?
Reply With Quote
  #3  
Old 12-23-2014, 12:05 AM
macropod's Avatar
macropod macropod is online now Formula Help Windows 7 64bit Formula Help Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #4  
Old 01-08-2015, 07:45 AM
odonnest odonnest is offline Formula Help Windows XP Formula Help Office 2007
Novice
Formula Help
 
Join Date: Dec 2014
Posts: 4
odonnest is on a distinguished road
Default

Yes the code works fine, however I want it to distinguish new records from old records that have already been notified.
Reply With Quote
  #5  
Old 01-08-2015, 02:16 PM
charlesdh charlesdh is offline Formula Help Windows 7 32bit Formula Help Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

HI,

Can you attach a redacted copy of your file. This will help us to determine what would work best.
Reply With Quote
  #6  
Old 01-08-2015, 08:49 PM
macropod's Avatar
macropod macropod is online now Formula Help Windows 7 64bit Formula Help Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by odonnest View Post
Yes the code works fine, however I want it to distinguish new records from old records that have already been notified.
That's all very well but, as I have already said:
Quote:
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...
Unless you take either or those approaches, you won't be able to exclude anything.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 01-08-2015, 11:48 PM
odonnest odonnest is offline Formula Help Windows XP Formula Help Office 2007
Novice
Formula Help
 
Join Date: Dec 2014
Posts: 4
odonnest is on a distinguished road
Default

Quote:
Originally Posted by charlesdh View Post
HI,

Can you attach a redacted copy of your file. This will help us to determine what would work best.
Hi thanks file attached below
Attached Files
File Type: xlsm Copy of FLEET INFO 2014.xlsm (142.2 KB, 8 views)
Reply With Quote
  #8  
Old 01-09-2015, 12:00 PM
charlesdh charlesdh is offline Formula Help Windows 7 32bit Formula Help Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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
Reply With Quote
  #9  
Old 01-23-2015, 07:18 AM
odonnest odonnest is offline Formula Help Windows XP Formula Help Office 2007
Novice
Formula Help
 
Join Date: Dec 2014
Posts: 4
odonnest is on a distinguished road
Default

Hi thanks seems to be working better now!!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula Help Formula Excel Excel 1 08-14-2014 05:11 PM
Formula Help Formula Help withersd46260 Excel 2 04-30-2014 08:33 AM
Formula muhu Excel 1 04-14-2014 08:19 AM
Formula Help What formula should I use? TotalONE Excel 3 11-26-2013 01:30 PM
Formula Help Help with Formula Corca Excel 6 02-22-2010 09:40 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:01 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft