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

[odonnest]

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?

[pjwhitfield]

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.

[odonnest]

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

[pjwhitfield]

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).

[odonnest]

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.

[pjwhitfield]

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.

[odonnest]

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?