How can I get excel to automatically email 5 email addresses when an expiry date is due

I am working on a database that contains bus data, due dates for Mot's, Tax dates and disabled badges, I am trying to find out how I can get the spreadsheet to email 5 colleagues when the expiry date is due in 28 days? Can anyonel help please?

You'll need a reference to the Outlook Object Library (VBA Editor > tools > References)

add the following in a module:

Please Login or Register  to view this content.

Then in your Workbook_Open event, add some code to compare the expiry dates (- 28 days) against todays date ( Now() ) and if less than today, pass the variables to the Function to send the email.

Thanks unfortunately I am a touch niave regarding VB can you make this any simpler please?

without seeing your workbook and how the data is held etc its not possible to create the exact code you require. Below however is a very very basic sample code to do the same thing,

In this case, column A has the vehicle registrations, B has the expiry dates and C is used to note when the emails have been sent (otherwise you'll continue to get mails every time).

Hi thanks again have tried unsuccessfully maybe you can help more if you can see the workbook, I have attached a copy. Would be very graterful for any help you could give.

Ive added the code for the MOT button, the logic and everything is the same just change references and columns where necessary when duplicating the code for other buttons.

Hi thanks your help has ben invaluable!! One final thing, is there a line of code that will automatically send the email upon running the macro?