none
Using Outlook VBA to compare calendars RRS feed

  • Question

  • I need a solution that will allow me to search calendars and compare appointments.  Specifically, I need to search a user calendar and find all appointments for today.  Then I need to find all the locations.  Then I need to search all the location calendars (rooms) to see if there is a match on the appointment (subject?).  I'd generate a report showing any appointments on the user calendar that are not on the room calendar that they should be. For this example, assume permissions are not an issue.

    Another nice tweak would be to be able to search for any appointment that was created in the last 24 hours rather than just searching today. 

    The question of why I need to do this is probably to come first from any reader.  I have a lot of high profile users who ignore room declines.  I need a report so an assistant can poke them to change the room.  Using delegates is not a viable option.

    Here is a sample code I'm using to get the appointment info from one user.  I just don't know where to start with the compare.

    Dim olap As New Outlook.Application
    Dim olns As NameSpace
    Dim oloneag As Object
    Dim olappts As Outlook.MAPIFolder
    Dim wsenddate As Date
    Dim woneap, oneweek As Object
    Dim wsstartdate As Date
    
    Sub GetOutlookInfo()
    
    wsstartdate1 = Date
    wsstartdate = Date
    wsenddate = Date
    tdystart = Trim(Str(wsstartdate)) & " 12:00 AM"
    tdyend = Trim(Str(wsenddate)) & " 11:59 PM"
    getquery$ = "[Start] >= """ & tdystart & """ and [Start] <= """ & tdyend & """"
    Selection.TypeText "CalWeek"
    ActiveDocument.Content.Select
    Selection.Delete
        With Selection.Font
            .Name = "Arial"
            .Size = 10
            .Bold = False
            .Italic = False
            .Underline = wdUnderlineNone
            .StrikeThrough = False
            .DoubleStrikeThrough = False
            .Outline = False
            .Emboss = False
            .Shadow = False
            .Hidden = False
            .SmallCaps = False
            .AllCaps = False
            .ColorIndex = wdAuto
            .Engrave = False
            .Superscript = False
            .Subscript = False
            .Spacing = 0
            .Scaling = 100
            .Position = 0
            .Kerning = 0
            .Animation = wdAnimationNone
        End With
    
     ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=1, NumColumns:= _
            5
        Selection.TypeText Text:="Start"
        Selection.MoveRight Unit:=wdCell
        Selection.TypeText Text:="End"
        Selection.MoveRight Unit:=wdCell
        Selection.TypeText Text:="Subject"
        Selection.MoveRight Unit:=wdCell
        Selection.TypeText Text:="Location"
        Selection.MoveRight Unit:=wdCell
        Selection.TypeText Text:="Organizer"
        Selection.MoveRight Unit:=wdCell
    
    Set olns = olap.GetNamespace("MAPI")
    L$ = "Smith, John"
    Set olappts = olns.GetSharedDefaultFolder(olns.CreateRecipient(L$), olFolderCalendar)
    Set mycol = olappts.Items
    mycol.Sort "[Start]"
    mycol.IncludeRecurrences = True
    Set myrange = mycol.Restrict(getquery)
    myrange.Sort "[Start]"
    
    With Selection
     For Each woneap In myrange
     mdaystart = Format(woneap.Start, "m/dd/yyyy h:MM")
     mdayend = Format(woneap.End, "m/dd/yyyy h:MM")
     ptime = mdaystart & mdayend
    
    .TypeText mdaystart
        Selection.MoveRight Unit:=wdCell
     .TypeText mdayend
        Selection.MoveRight Unit:=wdCell
     .TypeText woneap.Subject
        Selection.MoveRight Unit:=wdCell
     .TypeText woneap.Location
        Selection.MoveRight Unit:=wdCell
    .TypeText woneap.Organizer
        Selection.MoveRight Unit:=wdCell
    
     Next
    End With
    
    
    End Sub

    Tuesday, October 2, 2012 2:26 PM

Answers

  • I've been able to do most of what I need using php-ews.  Still some bugs to work out, but mostly there.
    • Marked as answer by MikeM-2468 Thursday, October 11, 2012 1:10 PM
    Thursday, October 11, 2012 1:05 PM

All replies

  • Hi Mike,

    Thanks for posting in the MSDN Forum.

    Based on your issue, I would recommend you this thread which contains some samples of searching Outlook appointment by uisng a macro. You will find how to search for appointment items in a specified date range, for example Today. Please refer to

    http://msdn.microsoft.com/en-us/library/office/dd490722(v=office.12).aspx

    Hope it can help you.

    Best Regards,


    Leo_Gao [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, October 4, 2012 3:19 AM
    Moderator
  • I don't see anything that isn't already in my sample above.  My task is to find an appointment in a calendar and then see if it exists on another calendar using the location in the first calendar item to determine which other calendar to compare with. 
    Thursday, October 4, 2012 12:10 PM
  • Hi Mike,

    Accprding to your description, can I understand your objective is to find an appointment which has the same location with the specified one (the appointment in the first calendar) via interating all the appointments of an another calendar, then get this calendar?

    Thanks,


    Leo_Gao [MSFT]
    MSDN Community Support | Feedback to us


    Monday, October 8, 2012 6:27 AM
    Moderator
  • My objective is to search a user's calendar, find an appointment, get the location and time, then search the location's room mailbox to see if there is a corresponding appointment with the same subject.  Output would be a list of appointments from the user's calendar that don't match appointments on the room mailbox.
    • Edited by MikeM-2468 Monday, October 8, 2012 11:55 AM
    Monday, October 8, 2012 11:55 AM
  • Hi Mike,

    I will involve some experts who are familiar with this issue, and it may take some time. Much appreciate for your patience.

    Best Regards,


    Leo_Gao [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, October 10, 2012 1:29 AM
    Moderator
  • I've been able to do most of what I need using php-ews.  Still some bugs to work out, but mostly there.
    • Marked as answer by MikeM-2468 Thursday, October 11, 2012 1:10 PM
    Thursday, October 11, 2012 1:05 PM
  • Hi Mike,

    I understand that you were able to find a solution of the issue by yourself. I wanted to check if you still needed any help from us on this issue. Also, if you don’t mind, you can share the solution that you had found on this issue with the rest of the folks using this forum.

    Thanks,
    Shailesh Rajput

    Friday, November 16, 2012 7:57 PM
  • As I mentioned above, I used PHP-EWS.  Info on what that is can be found here.
    Saturday, November 17, 2012 12:20 PM