Excel VBA for Sending Outlook Appointment Calendar Invites

Purpose

During exam season, immense amounts of time are dedicated to sending out Outlook Calendar appointment invitations. Each participant of the meeting has to be looked up and their E-mail address added individually. Furthermore, while templates are available for the various invites, much of the content has to be customized, including the meeting dates / time, the meeting room (location), the list of instructors & support staff, list of support rooms (for larger exams) etc.

While innate variations between exams exist, and some customizations are probably always present, much of the content could still be automated to minimize the amount of time spent searching, scrolling and pasting information. The Macro-Containing Spreadsheet in this repo includes VBA code that automates much of these procedures.

How to Use

1. Check: https://ubc.instructure.com/courses/6040/pages/how-to-use-the-send-calendar-invite-spreadsheet for user's manual complete with images.
2. Ensure Outlook is open
3. Download the Excel File in this repo "send_exam_invites.xlsm" (or fetch/pull repo through GitHub)
4. Open "send_exam_invites.xlsm"
5. If you see a warning ribbon on top, click "Enable Macro" or "Enable Content"
6. Open your browser, go to the google spreadsheet for Sauder Exams
   • Ask LS Ops for link/access if you don't have it
7. Go to the worksheet for the relevant term in the google sheet
8. Copy content in the google sheet worksheet
9. Paste content from google sheet into "Exam Sheet" in "send_exam_invites.xlsm"
   • Ensure header is in first row of spreadsheet
10. Go to the "Mail List" worksheet in the google sheet
11. Copy content in the worksheet
12. Paste content from "Mail List" from google sheet into "Mail List" in "send_exam_invites.xlsm"
13. Check that formatting in "send_exam_invites.xlsm" is up to standards
14. Once ready, select the courses in the first column that needs invite drafted
15. Click on "RECOMMENDED: Draft Selected" in top left
    • Alternatively: click "Draft / Send with Custom Settings" to customize settings
16. Calendar invites will be made
17. Once done, check the "CALENDAR INVITE" column to see if any courses "FAILED"
18. Check the "PEOPLE INVITED" column to see who was found for the invite
19. Update the "CALENDAR INVITE" column on google sheet (can copy and paste)
20. BEFORE EXAM: make necessary tweaks and double check info are correct

Importing VBA into Blank Spreadsheet

These steps are only for importing the code into a blank sheet. If you are unsure what this means then only refer to "How to Use" to run the spreadsheet.

1. Pull this repo
2. Open a blank Excel File
3. Save as a Macro-Enabled Spreadsheet
4. Go to "File"
   1. => "Save As"
   2. => choose "Excel Macro-Enabled Workbook (*.xlsm)"
5. Enable Developer Ribbon
   1. Go to "File"
   2. => "Options"
   3. => "Customize Ribbon"
   4. => in menu, find and tick checkmark for "Developer"
   5. => "Ok"
6. Enable References for Excel VBAProject
   1. Go to "Developer"
   2. => Visual Basic
   3. => "Tools"
   4. => "References"
   5. => ensure the following are checked:
      1. "Visual Basic For Applications"
      2. "Microsoft Excel 16.0 Object Library"
      3. "Microsoft Office 16.0 Object Library"
      4. "Microsoft Outlook 16.0 Object Library"
      5. "Microsoft Scripting Runtime"
      6. "OLE Automation"
      7. "Miscrosoft VBScript Regular Expression 5.5"
7. Import .bas and .cls files into Project
   1. In VBA window, go to "File"
   2. => "Import File"
   3. => Choose the files from the "src" directory pulled out of this repo
   4. => Files have to be imported one by one
8. Done