What it does
Uses a settings sheet (Bulk_Edit_Appointments) to add, delete, update, or export a list of appointments in bulk, integrating Excel and Outlook.

When to use it
When you need to add multiple appointments in Outlook that don’t necessarily recur consistently, this is an easy and controlled way to do it.  Common use cases: sports schedules, list of holidays, appointment-based to-do lists.

Why to use it
It’s an extremely quick way to import or export calendar items between Outlook and Excel.  Leverage the power of Excel with cell references, copy/paste, find/replace, filter/sort to quickly add items to your calendar with consistency.  Link fields like dates to inputs so you can reuse your import file over and over again, saving even more time.

Default shortcut
None

Other Details

• Category: MS Office / Outlook
• Difficulty: 4/5
• Usage/frequency: 1/5
• Automation factor: 5/5 (estimated 300 seconds saved each time used)
• Type: Bulk
• Date added: 8/15/2019
• Tags: Outlook, calendar, schedule, bulk, appointments

Related Macros
Show Schedule Availability
Create Send Meeting Requests

Other Articles
None

Prerequisites
Ensure you have an email/calendar set up in Microsoft Outlook.

Instructions
Run the Bulk Edit Appointments macro.  It will prompt you to either export a list of appointments (see below) or add new ones.  If adding new appointments, (option A at the prompt), it will create a sheet called Bulk_Edit_Appointments.  This is where you’ll configure all the appointments to create.  These are the columns within the Bulk_Edit_Appointments sheet you’ll want to fill in:

• Column A – Start Date (required): enter the date the appointment should start.  Leverage formulas to add recurring items like daily or weekly items.  To reuse your import file as a template, consider linking dates and other fields to an inputs sheet.
• Column B – Start Time (required): enter the time the appointment should start.
• Column C – Duration in hours (required): enter the duration of the appointment in hours.
• Column D – Reminder On/Off (required): enter TRUE to add a reminder alert, or FALSE to ignore a reminder alert.  If TRUE, column E (reminder minutes before) is required.
• Columns E – Reminder minutes before (required if column D value = TRUE): enter the number of minutes before the appointment that the reminder alert should trigger.
• Column F – Subject (required): enter a descriptive subject for the appointment.  This will be displayed in the schedule view within Outlook.
• Column G – Location (optional): enter the location for the appointment.  If entered, this will be displayed in the schedule view within Outlook below the subject.
• Column H – Description (optional): enter other details for the appointment as a cell comment.  Note that cell comments are used to reduce clutter in your sheet and overcome cell character limits.  This will be displayed when you open the Outlook appointment.
• Column I – Show Time As (required): select an item from the drop-down list related to how the appointment should appear in Outlook (default is 2 for busy).
• Column J – Categories (optional): enter one or more Outlook categories to assign to the appointment.  Separate multiple categories with a comma.  To see a list of available categories, view them in Outlook or use the Bulk Edit Categories macro.
• Column K – Importance (required): select an item from the drop-down list related to the importance of the appointment (default is 1 for normal).
• Column L – All Day (required): enter TRUE if the appointment should be noted as occurring all day, or FALSE if it is not all day.
• Column M – Privacy (required): select an item from the drop-down list related to the privacy of the appointment (default is 0 for normal).
• Column P – Add/Delete (required): enter [NEW] to import new items.  Enter [DELETE] to delete existing items.  Enter [UPDATE] to update existing items.  Note that to update or delete, the appointment ID must be in column O.  This is usually retrieved by first exporting appointments within a date range and filtering to the items you want to update or delete.

Once you fill in the appointment details, run the Bulk Edit Appointments macro again and confirm at the prompt to process.  If the data is valid, the appointments will be imported to Outlook and the ID for each appointment will be listed.

To update or delete bulk appointments, first export a list of appointments.  Run the Bulk Edit Appointments macro, enter U, then a space, then a date range.  Note that the bigger the date range, the longer it may take to export, depending on how many items are in your calendar.  Any appointments in the date range will be listed in a new sheet called Bulk_Edit_Appointments.  If that sheet already exists, you will be prompted whether to process – add/update/delete – the contents.  To delete items, enter [DELETE] in column P.  To update items, enter [UPDATE] in column P.  Filter to and delete rows for any items you don’t want to update or delete.  When ready, run the Bulk Edit Appointments macro again to process any items to update or delete.

TBD