Automated Excel Calendar from Outlook

Background: We have a weekly meeting that we all sit around and dish out our schedules and manually input them into a master excel sheet. This is inconvenient, time consuming, and inefficient. We would like to automate the process. What we need: Outlook Calendars (7 in total) -> Master Excel Sheet -> Member Schedule Excel Sheet Outlook needs: 1) We need all 7 outlook calendars to go into one single excel sheet. We want it to happen on a weekly basis on Fridays. 2) The excel sheet needs to have variables for the owner, category, subject, start date, end date, attendees. a. This is already in the code below 3) The code below needs to be edited to where it is automatic and not manual. At the moment we have to manually select the dates that the code draws from on the calendar. We want it to be an automated process to take place every Friday evening. 4) Also, we have a system of classification in place to say if the file is confidential or not. This has caused problems with the code when trying to save since it cannot tell the program what to do. This is a minor problem that we could probably work around, but would be nice to have it automated as well. Master excel sheet needs: 1) The 7 calendars need to be imported into this one sheet 2) The variables mentioned above should be the columns 3) The code below does this well, but as mentioned, we need it to be automated Member Schedule Excel Sheet: 1) This excel sheet has a list of the members with dates by day and month. 2) We need this excel sheet to be filled based on criteria from the master excel sheet a. Example: if Person1 has a vacation scheduled for 02/04/2017 to 02/10/2017, we need the corresponding boxes filled with a “V” on those dates for that person inside of the excel sheet. 3) The criteria needed for the sheet is: a. Date of event matches on both sheets b. Owner of Calendar matches Person (this will have to be searched by keyword… example: First Last on the Member Schedule Excel sheet will be displayed as “[login to view URL]\calendar “ on the master excel sheet.) c. Look for certain keywords (ie. “vacation”, “personal”, etc… we will set these) inside of the master sheet subject box column to determine if the specific date and person has added is a vacation day, personal day, half day vacation, etc. This command should fill in the sheet with the appropriate symbol to indicate what type of day it is d. If an event contains 2 or more of the Persons, then the column should be yellow with “Major Events/Meetings” being filled with the name of the event 4) The criteria need to return the correct code corresponding with the correct person, date and event 5) If an event is more than one day, the master excel will only have the start date and end date, we will need for all days in between to be highlighted with the correct symbol. The uploaded file contains the same information plus images. We have anticipation of more projects in the future and would like to find a freelancer to stay with us on these.