Next Available Meeting with Userform Variables

[Daniel Murphy]

Good Afternoon All,

This is my 1st post. I'll try and be as complete in my request as possible.

We have (a charitable housing association in the UK) scenarios where we want to book a meeting, invite others and book a room for a set time defined by the organiser. Another variable the organiser may choose to exercise is if the room has a screen.

The rooms have the capacity of the room in brackets within the name field (not in the column marked capacity - which has been left blank (I'm in Finance not IT))

I am comfortable creating userforms in the VBA builder but not as strong within Macros.

I envisage a macro that operates from either excel or outlook and pops up a userform. The user can enter up to 12 emails (attendees)
, selects if they need a room and or if they require a screen,
selects the duration of the meeting,
selects the minimum and maximum start and finish times (9am and 5pm as a default).

I believe it'd be easier to manage the variables in a separate table saved within the macro ie define each room and it's properties, but ultimately returns the next available space that meets all requirements and gives the option to send the invite with a variable subject line.

The macro should work from any computer. We use Microsoft Outlook Exchange for all calendars and colleagues.

You can see that the "location" field within the below calendar (that has been shared with me) is blank. although it says in the box below ... "When posting to this folder use: IPM.Appointment"

I'd like the macro to be able to saved within either outlook or excel. If outlook isn't open it should open it, if outlook is open it should use that instance.

I hope that's clear enough! Really hope you can help, it'll save days over a year.

 

[Diane Poremsky]

not in the column marked capacity - which has been left blank (I'm in Finance not IT))

Don't blame IT for this one. That is just an informational field - it's more or less useless as it doesn't prevent anyone from inviting more. The advantage of adding it to the display name is that if you add the calendars to profile as shared calendars, you can see the capacity with little effort.

envisage a macro that operates from either excel or outlook and pops up a userform. The user can enter up to 12 emails (attendees)
, selects if they need a room and or if they require a screen,
selects the duration of the meeting,
selects the minimum and maximum start and finish times (9am and 5pm as a default).

it might be easier and more portable to put it in a sheet - code behind custom forms is picks and needs to be enabled on each computer. With it in Excel, as long as the users can run macros, you won't have a problem.

See Create a drop-down list - Office Support for one way to do a selection. Then you just need a macro to do a lookup and send that information to a new meeting - then the user picks the recipients.

Did you want the macro to pick a room or just present the user with the options to pick a room? This would be the hardest part - it might be easier to do this in outlook because you have the room find to show you if the room is available.

although it says in the box below ... "When posting to this folder use: IPM.Appointment"

That's not a problem - it's the default for all calendars - appointments because meetings when you add addresses.