Expiry dates - to send automated emails

I have a lot of employees, with sensitive information that has expiry dates. to keep on top of all of this I would like to know if it is possible to send an automated email using the email addresses in the B column and while keeping myself CC'd in 60 days before it expires and 10 days prior as an urgent attention required. if possible I would like this to be able to be complete without the spread sheet being open.

Ideally the information in the email will have;

URGENT ATTENTION FOR (LINE MANAGER)
Employee - (surname) (first name) has got his (whatever is due for expiry from row 6) coming up for its expiry date

Any issues please contact your supervisor

I have attached a sample spread sheet with how I have it set up at the moment - conditional formatting to highlight in red expired and amber 60 days before problem is im not always in the office to keep on top of this information.

I use Excel 2010 and outlook.

Any help would be greatly appreciated.

Hello fellow Brightonian!

I use this at my work below. Some parts may need amending to your needs. This isn't possible without Excel open unless you have a personalworkook thats coded to autostart with huge amounts of arrays holding data. Anyhow, outlook VBA may be easier to work with for that if its vital.

Please Login or Register  to view this content.

Hello thanks for the quick reply fellow Brightonian!

Surely this cant be the amount of code I need I tried my own but it was getting awfully complicated and didn't work lol

Yes, sending the e-mail is relatively simple. The hardest bit will is data validation and calculations to find the which is @ -60 days

Set a few of the dates in your workbook to around April and it will pick them up and send an e-mail off (make sure you have your e-mail in the cells when testing it)

I've put some tests and it send emails. I've put some minor error traps in there for missing data but you may need more.

I recommend the John Walkenbach (think thats his name) Excel VBA programming book if you are getting started, its certainly helped me. As also the help received on this forum!

Heres something that should get you started, place it in a Module -

Please Login or Register  to view this content.

Thanks again for the quick reply! Gonna have a go tomorrow and see if a simpleton like me can get it working

No probs, the VBA lingo can be quite overwhelming at first but keep at it!

(Just a few notes I thought of -

You'll need to figure out a way to STOP the re-sending of information/ e-mails, maybe a test on interior.color of a cell - Its currently set to VbGreen if it's already sent . Also To trigger this sub on opening of the workbook, add the code below into 'ThisWorkbook'

Please Login or Register  to view this content.

)