Email to one email address when a due date is close

Hello all: This is my first post as so far I have always been able to find what I need, but this time I have not been able to find something that works for me.

I have attached a test file that I have been working with. Basically it's a temporary employee tracking sheet. What I would like the sheet to do is to email out a notification to one set email address when a temporary employee is 2 weeks from their planned end date (If known). I am not sure how to do this. I would like for it to run in the background but I realize that is not possible when the file is not open. So I wish for this Macro/VBA to run every time the file is opened.

Thanks in advance

G.

John55 - Thanks so much for your prompt response. I have (with my limited VB code knowledge) modified the script a bit for my purposes. The attached updated files works perfectly when the Command Button is clicked. What I have been trying to do with this is have the script run when the files opens and not by a manual click of the command button.

Is there a fix for this?

Thanks,

G.

Temp_test_a.xlsm

John55, ahh I see what you did, you moved the code from a VB to a Macro???

So far it seems to work perfectly!!!!

Thanks so much!

Quick question, if the file is not opened every day is it possible that a email will not be sent? So I guess my question should be, if there is no text in the 'send' cell box and it is less than 10 days, will the macro sill send an email?

Easy way to do that (open file every day).. is to let Windows Task Scheduler take care of it for you..

http://social.technet.microsoft.com/...w7itprogeneral

Thanks APO - I have just set this task up and this should work great! Thanks all.