Need help with automatic email generation flagging excel due dates

[MGrunza]

Hello, I only have basic knowledge of excel capabilities. I am not familiar with any programming or code usage. I have a spread sheet set up for peices of test equipment, their coresponding serial numbers, and calibration due dates. I would like to send a notification to my email one day prior to the calibration due dates. Can someone please instruct me how this can be accomplished?

Thank you!

[Fettertiger]

Hi MGrunza,

since you said you are not familar with excel programing, I thought it would be easier for you if I not just provide you with the program code, but include it in a workbook instead. The worksheet consists of three tables, the main one being the Calibration Dates table. Here you can maintain the data as mentioned in your question. I have added one more column to check if an email was already sent. The macro will try to sent an email every time it is running until the value is marked with an "X" (which will be automatically maintained).
You can trigger the macro with a click on the button on this table.

The second table is called settings. Here you can maintain default values, like email address, subject and message. It uses some specific "placeholders" marked with <> . The macro will replace those placeholders by proper values e.g <DueDate> would be replaced by May 14th 2012.

The third table is used to maintain public holidays. Rather than triggering the email one day before due date, it will send it one workday before. This is done to avoid sending you a reminder on a sunday - which could result in an overdue calibration. This logic will not only take into account weekends, but will also check if there is an entry in the public holiday list. Unfortunately I could not find a holiday list for Pennsylvania (well I did but I was not sure which ones would be relevant for you), therefore I have just added the "common" US holidays. Please add additional holidays at your own convinience.

I hope this is what you needed.

Greetings from Germany

Theo
____________
Please mark your threads as closed as soon as your issue is solved. Thank those who helped you by clicking the star to the left of your helpers post!