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!