easiest way to make an appointment for me + one of 8 rooms

Posted on 2011-10-06
Last Modified: 2012-08-13
I'm using Outlook 2007. I'm trying to come up with an easy way to book an appointment for me + one of 8 rooms.

If I make a Group Schedule for me + the 8 meeting rooms, the scheduler tries to find a way to find a time when me + all 8 of the meeting rooms are available. I want a time when me and any one of the 8 rooms is available (the rooms are all the same).

What would people recommend as a good way to teach people how to do this?

If someone has an idea about a macro that could do it, I could go that way if necessary (I'm an experienced VBA programmer).
Question by:GordonPrince
    LVL 6

    Expert Comment

    I can do this in Outlook 2003 as follows:

    1) Create a new Meeting Request, and add all of the attendees in the To: field, and all of the rooms in the Resources: field.
    2) Click on the Scheduling tab, and under the Options button, select AutoPick > Required People and One Resource.
    3) Click the AutoPick Next or Previous button to locate the appropriate time slot. Only one of the free resources (rooms) will be selected, and the others will be automatically disabled/excluded from the invite.
    LVL 76

    Expert Comment

    by:David Lee
    Hi, GordonPrice.

    It's been awhile.  Hope you're doing well.

    I think I can do what you want in a macro.  Armed with the names of the rooms and a starting and ending time a macro can search for the first room that's free and add it to the appointment.  Is that what you had in mind?  
    LVL 4

    Author Comment

    BlueDevilFan: The macro seems like the way to go, if you can think of something that would work.

    Needs to become a button on the Outlook toolbar that offers next available appointment for me + one of the 8 rooms. A display would be nice, but a pop-up with the date/time would be adequate to start with.
    If the user answers vbYes then book meeting in the room,
    vbNo = show me the next availability,
    vbCancel = stop showing me the choices.

    My rooms are IR1, IR2, IR3... IR8.

    I've worked with Recipient.FreeBusy recently, so that's one option. But if there's something that could be stitched together in a macro, I'd like to consider that, too.

    Any ideas greatly appreciated.
    LVL 76

    Expert Comment

    by:David Lee
    I haven't forgotten about this question.  I should have something today.
    LVL 76

    Accepted Solution

    This appears to work properly.  You should test it with a few appointments to make sure though.  Follow these instructions to add the code to Outlook.

    1.  Start Outlook
    2.  Click Tools > Macro > Visual Basic Editor
    3.  If not already expanded, expand Microsoft Office Outlook Objects
    4.  If not already expanded, expand Modules
    5.  Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting Insert > Module.
    6.  Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook's VB Editor window
    7.  Edit the code as needed.  I included comments wherever something needs to or can change
    8.  Click the diskette icon on the toolbar to save the changes
    9.  Close the VB Editor

    To use this solution

    1.  Create an appointment
    2.  Fill in the starting and ending times
    3.  Run the macro FindConferenceRoom (You can add a button to the QAT that runs the code.)

    This macro works by searching the list of conference rooms for one that's free.  It does this by retrieving the free and busy information for each room using the duration of your meeting as the interval.  The information returned is a character string where each character equals one interval (e.g. if the appointment is 30 minutes long, then each character equals 30 minutes).  The code then calculates which intervals to check and looks at them to see if the room is free.  If the interval has a 0 in it, then the room is free.  If it has a 1, then it isn't.  If the code determines the room is free for all the required intervals, then it adds the room to the appointment.  If instead the code determines that the room is not free, then it skips to the next room.  If all eight rooms are free, then the code will always book the first room in the list.  

    I tested this on my system with a few different meeting lengths (e.g. 30 minutes, 45 minutes, 1 hour) and it always picked a free room.
    Sub FindConferenceRoom()
        Const CONF_RM_LIST = "IR1,IR2,IR3,IR4,IR5,IR6,IR7,IR8"
        Dim olkAppt As Outlook.AppointmentItem, _
            olkRcp As Outlook.Recipient, _
            olkRoom As Outlook.Recipient, _
            arrRooms As Variant, _
            varRoom As Variant, _
            strFB As String, _
            intStart As Integer, _
            intEnd As Integer, _
            intIdx As Integer, _
            bolFree As Boolean
        arrRooms = Split(CONF_RM_LIST, ",")
        Set olkAppt = Application.ActiveInspector.CurrentItem
        intStart = CalculateInterval(olkAppt.Start, olkAppt.Duration, True)
        intEnd = CalculateInterval(olkAppt.End, olkAppt.Duration, False)
        For Each varRoom In arrRooms
            bolFree = True
            Set olkRcp = Session.CreateRecipient(varRoom)
            strFB = olkRcp.FreeBusy(olkAppt.Start, olkAppt.Duration, False)
            For intIdx = intStart To intEnd
                If Mid(strFB, intIdx, 1) = 1 Then
                    bolFree = False
                    Exit For
                End If
            If bolFree Then
                Set olkRoom = olkAppt.Recipients.Add(varRoom)
                olkRoom.Type = olResource
                Exit For
            End If
        Set olkAppt = Nothing
        Set olkRcp = Nothing
    End Sub
    Function CalculateInterval(datStart As Date, intDuration As Integer, bolStart As Boolean) As Integer
        Dim intMinutes As Integer
        intMinutes = (Hour(datStart) * 60) + Minute(datStart)
        If bolStart Then
            CalculateInterval = Int(intMinutes / intDuration) + 1
            CalculateInterval = Int(intMinutes / intDuration)
            Select Case intDuration
                Case Is <= 60
                    If 60 Mod intDuration = 0 Then
                        CalculateInterval = CalculateInterval + 0
                        CalculateInterval = CalculateInterval + 1
                    End If
                Case Is > 60
                    If intDuration Mod 60 = 0 Then
                        CalculateInterval = CalculateInterval + 0
                        CalculateInterval = CalculateInterval + 1
                    End If
                End Select
            End If
    End Function

    Open in new window

    LVL 4

    Author Closing Comment

    This is excellent. Thanks.
    LVL 76

    Expert Comment

    by:David Lee
    Thanks and you're welcome.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Suggested Solutions

    Find out how to use dynamic social media in email signatures with this top 10 DOs & DON’Ts.
    Use these top 10 tips to master the art of email signature design. Create an email signature design that will easily wow recipients, promote your brand and highlight your professionalism.
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now