VBA - customised email alerts on changes to individual rows in worksheet

Good morning, all!

I'm just wondering if you could all help me solve a little work-related problem.

I work as a remote service engineer. The central planning is done on a crappy excel-based Gantt-type arrangement workbook, not a proper chart. This is kept on the company network. My fellow engineers and I travel to places like oil rigs where the networks tend to block the VPN we would use to access the company drives. And when the planners make changes to your schedule, they tend to keep it to themselves!

So, I've proposed we implement something in VBA to ping an automated email alert to the engineers when their workload is changed, i.e. anything on their row in the sheet is altered.

I've come up with the attached sheet with code for test purposes. It does vaguely do what's intended, but with the following shortcomings:

1. It's currently only looking for changes to one row (engineer), not monitoring the entire sheet/roster. There's only two "engineers" in there just now, for test.
2. It literally creates one email per change. So, if someone in planning put in a job number, changed the fill colour, and put a border around the job duration, three emails would be generated. It would be good if when they were finished the changes and saved the worksheet, the alert could be generated then.

Please have a look and see what you can do with it. Would be very much appreciate if I can drag the planners almost into the 21st century until we start to use proper planning / projects software!

Thanks in advance,

Roy K.

Edit - I did have my own email addresses listed in the sheet, figured it might make sense to remove them.

Try this, untested:

Please Login or Register  to view this content.

And FYI, even if you deleted your email address, it still can be found in the code

Green is For+Next, Red is With+End with

Please Login or Register  to view this content.