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:
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!