(Print this page)

A .Net application to extract appointments from Office 365 public calendars
Published date: Sunday, May 20, 2018
On: Moer and Éric Moreau's web site

Last week, we had an issue in the office. A big issue! One of the employee took one of the meeting rooms without booking it because he thought it was available. But a meeting was already scheduled for 15 minutes later. A big issue. A disaster.

Of course, I am joking about the amplitude of the issue but guess what, I was the one to whom they asked to solve the issue. Why not, I am the one who created the room resources in Office 365 after all!

People in that office are often too lazy to look at the various rooms in their Outlook calendar but they are good at reading emails. So, what I have done, I have created a .Net application that reads a specific public calendar (that the company use to collect various appointments important for the whole company) and also room resources appointments. The various are aggregated into a single email and sent around at 4PM for the events of the next 3 weekdays.

The downloadable code

This month’s downloadable demo solution contains both VB and C#. The solution was created using Visual Studio 2017 but can be used in most older versions as well.

Office 365 & (surely) Exchange

This article will show how to extract the appointments for “Rooms & equipment” resources as well as appointments from a calendar in the public folders.

I have tested this code against Office 365 but I am pretty sure that it would also work against an on-premise Exchange server (mainly because I use the old EWS library).

Of course, you will need to use an account that has at least reader privileges on the calendars you want to access.

Building the UI

The UI has nothing complex. A button to start the process and a textbox to show the results.

Figure 1: The demo application in action

Required reference

In order to get the code to compile, you will need to add a reference to “Microsoft.Exchange.WebServices” which is available as a Nuget package as shown in figure 2.

This code is using an old library but it is still working like a charm with today’s technologies.

Figure 2: Adding the reference

With this reference in place, you will also want to add the Imports (or using) statement of this new class (Microsoft.Exchange.WebServices.Data) to the top of your form.

The code

Surprisingly, the code is not that hard. The complete code of the form is just above 100 lines total (and the form is self-contained – no classes doing all the heavy lifting!).

I have inserted inline comments to the code so it is easier to read.

The big steps are:

  • Establish the range of dates you want to extract
  • Authenticate to Office 365/Exchange
  • Extract from the “Rooms & equipment” resources
  • Extract from the Public folders

Here is the full code that does this:

Private Sub btnStartProcess_Click(sender As Object, e As EventArgs) Handles btnStartProcess.Click
    ReadCalendars()
End Sub

' Extract appointments from rooms resources and public folders
Private Sub ReadCalendars()
    txtResults.Clear()

    'calculate the range of dates. 
    'I want from the next business date for business 3 days
    Dim dtmStart As DateTime = AddBusinessDays(DateTime.Today, 1)
    Dim dtmEnd As DateTime = AddBusinessDays(dtmStart, 3).AddSeconds(-1)
    txtResults.Text += "Searching from " + dtmStart.ToString("yyyy-MM-dd HH:mm") +
                        " to " + dtmEnd.ToString("yyyy-MM-dd HH:mm") + Environment.NewLine

    Dim objCalendarView As CalendarView = New CalendarView(dtmStart, dtmEnd, 100) 'limit to 100 items
    'limit the results to only 3 properties
    objCalendarView.PropertySet = New PropertySet(ItemSchema.Subject, AppointmentSchema.Start, AppointmentSchema.End)

    'Connect to Exchange/Office 365
    txtResults.Text += "Trying to connect to Exchange..." + Environment.NewLine
    Dim objExchangeService As ExchangeService = New ExchangeService(ExchangeVersion.Exchange2013_SP1) With {
        .Credentials = New WebCredentials("youremail@yourdomain.com", "YourPassword")
    }
    objExchangeService.AutodiscoverUrl("youremail@yourdomain.coma", AddressOf RedirectionUrlValidationCallback)

    'Read "rooms" resources
    For Each strRoomName As String In {"BoardRoom1@yourdomain.com", "BoardRoom2@yourdomain.com", "nycroom@yourdomain.com"}
        txtResults.Text += Environment.NewLine & strRoomName + Environment.NewLine
        Dim objCalendarFolderId As FolderId = New FolderId(WellKnownFolderName.Calendar, strRoomName)

        Try
            Dim lstAppointments As FindItemsResults(Of Appointment) = objExchangeService.FindAppointments(objCalendarFolderId, objCalendarView)
            DisplayAppointments(lstAppointments)
        Catch exception As Exception
            txtResults.Text += "Exception occured: " + exception.Message + Environment.NewLine
        End Try
    Next

    'now search in the public folder
    txtResults.Text += Environment.NewLine + "Public folder' calendar" + Environment.NewLine
    Try
        Dim objSearchFilter As SearchFilter = New SearchFilter.IsEqualTo(FolderSchema.DisplayName, "Public Folders name")
        Dim objFindFolders As FindFoldersResults = objExchangeService.FindFolders(WellKnownFolderName.PublicFoldersRoot, objSearchFilter, New FolderView(1))

        If objFindFolders.TotalCount > 0 Then
            objSearchFilter = New SearchFilter.IsEqualTo(FolderSchema.DisplayName, "Group Calendar name")
            Dim objFindFoldersCalendar As FindFoldersResults = objExchangeService.FindFolders(objFindFolders.Folders(0).Id, objSearchFilter, New FolderView(1))

            If objFindFoldersCalendar.TotalCount > 0 Then
                Dim lstAppointments As FindItemsResults(Of Appointment) = (CType(objFindFoldersCalendar.Folders(0), CalendarFolder)).FindAppointments(objCalendarView)
                DisplayAppointments(lstAppointments)
            Else
                txtResults.Text += "Cannot find the calendar in the public folder" + Environment.NewLine
            End If
        Else
            txtResults.Text += "Cannot find the public folder" + Environment.NewLine
        End If

    Catch exception As Exception
        txtResults.Text += "Exception occured: " + exception.Message + Environment.NewLine
    End Try

    'completed
    txtResults.Text += Environment.NewLine + "Process completed" + Environment.NewLine
End Sub

' calculate a new BUSINESS date (not including week-ends)
Private Function AddBusinessDays(ByVal pStartDate As DateTime, ByVal pNbDays As Integer) As DateTime
    Dim intDoW As Integer = pStartDate.DayOfWeek
    Dim decTemp As Decimal = pNbDays + intDoW + 1
    If intDoW <> 0 Then decTemp -= 1
    Return pStartDate.AddDays(Math.Floor(decTemp / 5) * 2 - intDoW + decTemp - 2 * Convert.ToInt32(decTemp Mod 5 = 0))
End Function

' Output the appointments in a formated way
Private Sub DisplayAppointments(ByVal pAppointments As FindItemsResults(Of Appointment))
    If pAppointments.Items.Count > 0 Then

        For Each objAppointment As Appointment In pAppointments
            txtResults.Text += objAppointment.Start.ToString("yyyy-MM-dd HH:mm") & " - " +
                                objAppointment.[End].ToString("yyyy-MM-dd HH:mm") & " - " +
                                objAppointment.Subject + Environment.NewLine
        Next
    Else
        txtResults.Text += "No meetings found" & Environment.NewLine
    End If
End Sub

' Just in case your Exchange/Office 365 is hosted in https
' Required by the auto discover
Private Shared Function RedirectionUrlValidationCallback(ByVal pRedirectionUrl As String) As Boolean
    Dim result As Boolean = False
    Dim redirectionUri As Uri = New Uri(pRedirectionUrl)

    If redirectionUri.Scheme = "https" Then
        result = True
    End If

    Return result
End Function
private void btnStartProcess_Click(object sender, EventArgs e)
{
    ReadCalendars();
}

// Extract appointments from rooms resources and public folders
private void ReadCalendars()
{
    txtResults.Clear();

    //calculate the range of dates. 
    //I want from the next business date for business 3 days
    DateTime dtmStart = AddBusinessDays(DateTime.Today, 1); 
    DateTime dtmEnd = AddBusinessDays(dtmStart, 3).AddSeconds(-1); 
    txtResults.Text += "Searching from " + dtmStart.ToString("yyyy-MM-dd HH:mm") +
                        " to " + dtmEnd.ToString("yyyy-MM-dd HH:mm") + Environment.NewLine;
    CalendarView objCalendarView = new CalendarView(dtmStart, dtmEnd, 100) //limit to 100 items
    {
        //limit the results to only 3 properties
        PropertySet = new PropertySet(ItemSchema.Subject, AppointmentSchema.Start, AppointmentSchema.End)
    };

    //Connect to Exchange/Office 365
    txtResults.Text += "Trying to connect to Exchange..." + Environment.NewLine;
    ExchangeService objExchangeService = new ExchangeService(ExchangeVersion.Exchange2013_SP1)
    {
        Credentials = new WebCredentials("youremail@yourdomain.com", "YourPassword")
    };
    objExchangeService.AutodiscoverUrl("youremail@yourdomain.com", RedirectionUrlValidationCallback);

    //Read "rooms" resources
    foreach (string strRoomName in new[] { "BoardRoom1@yourdomain.com", "BoardRoom2@yourdomain.com", "nycroom@yourdomain.com" })
    {
        txtResults.Text += Environment.NewLine + strRoomName + Environment.NewLine;
        FolderId objCalendarFolderId = new FolderId(WellKnownFolderName.Calendar, strRoomName);
        try
        {
            FindItemsResults<Appointment> lstAppointments = objExchangeService.FindAppointments(objCalendarFolderId, objCalendarView);
            DisplayAppointments(lstAppointments);
        }
        catch (Exception exception)
        {
            txtResults.Text += "Exception occured: " + exception.Message + Environment.NewLine;
        }
    }

    //now search in the public folder
    txtResults.Text += Environment.NewLine + "Public folder' calendar" + Environment.NewLine;
    try
    {
        SearchFilter objSearchFilter = new SearchFilter.IsEqualTo(FolderSchema.DisplayName, "Public Folders name"); //name of the public folder
        FindFoldersResults objFindFolders = objExchangeService.FindFolders(WellKnownFolderName.PublicFoldersRoot, objSearchFilter, new FolderView(1));
        if (objFindFolders.TotalCount > 0)
        {
            objSearchFilter = new SearchFilter.IsEqualTo(FolderSchema.DisplayName, "Group Calendar name"); //name of the calendar
            FindFoldersResults objFindFoldersCalendar = objExchangeService.FindFolders(objFindFolders.Folders[0].Id, objSearchFilter, new FolderView(1));
            if (objFindFoldersCalendar.TotalCount > 0)
            {
                FindItemsResults<Appointment> lstAppointments = ((CalendarFolder)objFindFoldersCalendar.Folders[0]).FindAppointments(objCalendarView);
                DisplayAppointments(lstAppointments);
            }
            else
            {
                txtResults.Text += "Cannot find the calendar in the public folder" + Environment.NewLine;
            }
        }
        else
        {
            txtResults.Text += "Cannot find the public folder" + Environment.NewLine;
        }
    }
    catch (Exception exception)
    {
        txtResults.Text += "Exception occured: " + exception.Message + Environment.NewLine;
    }

    //completed
    txtResults.Text += Environment.NewLine + "Process completed" + Environment.NewLine;
}

// calculate a new BUSINESS date (not including week-ends)
private DateTime AddBusinessDays(DateTime pStartDate, int pNbDays)
{
    int intDoW = (int)pStartDate.DayOfWeek;
    decimal decTemp = pNbDays + intDoW + 1;
    if (intDoW != 0)
        decTemp--;
    return pStartDate.AddDays((double)(Math.Floor(decTemp / 5) * 2 - intDoW + decTemp - 2 * Convert.ToInt32(decTemp % 5 == 0)));
}
        
// Output the appointments in a formated way
private void DisplayAppointments(FindItemsResults<Appointment> pAppointments)
{
    if (pAppointments.Items.Count > 0)
    {
        foreach (Appointment objAppointment in pAppointments)
        {
            txtResults.Text += objAppointment.Start.ToString("yyyy-MM-dd HH:mm") + " - " +
                                objAppointment.End.ToString("yyyy-MM-dd HH:mm") + " - " +
                                objAppointment.Subject +
                                Environment.NewLine;
        }
    }
    else
    {
        txtResults.Text += "No meetings found" + Environment.NewLine;
    }
}

// Just in case your Exchange/Office 365 is hosted in https
// Required by the auto discover
private static bool RedirectionUrlValidationCallback(string pRedirectionUrl)
{
    // The default for the validation callback is to reject the URL.
    bool result = false;

    Uri redirectionUri = new Uri(pRedirectionUrl);

    // Validate the contents of the redirection URL. In this simple validation
    // callback, the redirection URL is considered valid if it is using HTTPS
    // to encrypt the authentication credentials. 
    if (redirectionUri.Scheme == "https")
    {
        result = true;
    }
    return result;
}

As you can see, there is not much more in here. The trick is to gain access to the list of appointments and from there, your imagination is the limit!

Conclusion

Many time, information that is available from (what we think is) a neat place, it isn’t convenient to lazy people. This is when we need to collect information, rework the information, and broadcast it differently.

I have to admit that if you have many meeting rooms, that tool can become handy!


(Print this page)