Creating Outlook appointments using Excel and VBA

Hi all,

I’ve been working on VBA stuff for a while now learning from tutorials and examples but I think I’ve bitten off more than I can chew with my current project. I’m helping optimise work-flow for an employer as a self-imposed test of my abilities and although my current code works it’s far from perfect.

The idea behind it is to use Excel to automatically schedule events using pre-selected data and dropdown boxes (this bit is complete and functioning), then create an Outlook appointment for each event, and send an email inviting people specified in set cells to the appointment.

My current code for the Outlook segment is below; it creates a loop then creates an appointment in the creator’s personal Outlook calendar which is changed to a meeting, then invites two people – an administrative shared account, and the person specified in a cell to the meeting.

Please Login or Register  to view this content.

There are a number of issues with this:
• First and foremost it creates the appointment in the person running the macro’s personal calendar. I would really prefer it to be set in the admin shared calendar instead. Code I tried for placing the appointment there directly (included below) wouldn’t seem to let me send emails inviting others to attend the appointment and didn’t seem to show the appointment in their personal calendars though did show them in the ‘attending’ panel for the appointment in the shared calendar.
• When creating appointments the loop requests permission to send emails on every iteration. The alternative code I tried only required you to give permission once (giving permission for up to ten minutes). I would much prefer this.
• Least important, but desirable: The process is currently performed manually with appointments being set by hand then added to the admin account’s shared Outlook calendar and inviting the specified individual for each appointment. As it’s a task that happens every week and the appointments are for the same purpose (the calendar records a number of different types of appointment which are colour coordinated by purpose) a custom label is used that highlights the appointment in a certain colour. It would be nice if this code can also automatically set that.

This is the alternative code I tried in case it helps anyone (it was written first, and I know it would need a little modification to make it loop-friendly):

Please Login or Register  to view this content.

Thanks in advance for any help or pointers.