Wow... okay, you're building an entire system here. I'm going to start by telling you that, in order to avoid overwhelming the experts here with the amount of work, you should be breaking this down into bite sized pieces.
I assume that the calendar file is stored on the network somewhere, and that everyone has access to it? (Probably most importantly the managers?)
Given that, I'm assuming that you want to:
- Have the user complete the Absence request form
- They fill in the name, start and end dates, type, message, etc..
- They click "Send to Outlook"
- Clicking "Send to Outlook":
- The calendar is marked with the "T" (are you sure you don't want a T-Full, T-Half, T-...?)
- An email is sent to the manager telling them to review it
When the manager gets the email:
- They open the file and review the request
- Are you going to build a form to review all "Tentative" vacation for that manager?
- They will have the option to "Approve" or "Decline"
- If they click "Approve":
- The worksheet gets updated to show the correct absence type. (Would probably be easier with a T-type as listed above)
- And Outlook appointment is created to mark the approved leave in the user's calendar? Manager's calendar? Both?
- If they click "Decline":
- The tentative items are removed from the calendar
- And email is sent to the user telling them it's been declined and why
That's a bit more than you originally had, but I'm curious if that's the general gist of what you're after. If it differs at all, please let us know where.
For reference, I'll try and help where I can, but I'm most likely going to give you pointers, not write the code for you. Sending email via Outlook is easy. I don't recall calendar appointments being too tough.
It's the marking/unmarking of the vacation days that concern me. Your data structure isn't ideal for that... it would be better suited if you had one list of vacation days rather than 12 tables, then you could use PivotTables to update. It would take some work to convert it, but would probably be easier to maintain in the long run. Regardless though, it's up to you and certainly CAN be done in your current format. There's just a few tricks that I think you'd want to observe to keep it maintainable as you add new employees. (Like creating named ranges over your 12 tables on the Holiday Master page.)
Bookmarks