5 Minute Snack: Retrieving data from Exchange (2013) public folders using Outlook (2016) and Office Automation
Yet another thing that surprised me in the most recent release of Delphi was the fact that the import for ActiveX libraries has been improved significantly. A customer has contacts stored in a public folder on an Exchange server. Microsoft urges us to use the new alternatives to public folders, but that is only a theoretical statement. In most companies public folders are still used for mails and contact data.
Thus, the task can be summed up pretty nicely: Connect to Exchange and read data from a public folder.
I tried with Visual Studio and several tools to use the new HTTP API that Exchange 2013 offers – a hopeless endeavor. The Exchange administrator still has no idea why that interface does not work properly.
Thus, even with Visual Studio and C# the same approach is still viable. Just a remark for the sceptical crowd that always needs to point out that with Delphi you need to use older APIs in order to get things done. Definitely not the case if you want to access Exchange.
The keyword is Office Automation. We will use Outlook to read the data from the Exchange server. This does necessitate Outlook to be installed on the system the data access is supposed to work. You also need to make sure that you installed a feature called “.NET library support”. Any ActiveX library is also a .NET library most of the time, thus the name.
Create a new VCL Forms Application and select the “Import Component…” command from the “Component” menu. Import the Outlook library as follows:
Add the generated unit to your project. Delphi will also create all the necessary dependencies – mostly common types that are used by Microsoft Office applications.
If you have a closer look at the generated unit, you will see that all the classes and other types that can be found in the unit are also named exactly the same in the Microsoft documentation for the Microsoft Office Object Model. Thus, all the examples that you find on Microsoft sites can be easily transfered to Delphi. Furthermore, you use the classes in exactly the same way as you would do in C#.
If you use Google to find out more about Office Automation you will find scary code snippets. The Delphi language has developed since then. The code will look much easier and readable now.
Drop a button and a TListBox control on the form. The button will initiate the connection and the TListBox will be used for debug information that we will output to demonstrate the progress.
The click event of the button will the only thing we will implement. Please pay attention to the verbose comments as they explain exactly what you need to do. An explanation will also be given after the code snippet.
procedure TForm1.Button1Click(Sender: TObject); var // object for the Outlook Application instance lApp : _Application; // list of all data stores lStores: Stores; // selected data store lStore: Store; // counter i : Integer; // store of Public Folders lPublicFolders: Store; // root folder of the public folders lRootFolder: Folder; // all folders lFolders: Folders; // folder that contains the contacts lContactFolder: Folder; begin // open the connection // -- if Outlook is not running it will be after this command // -- set Visible to true if wanted lApp := CoOutlookApplication.Create; // get all stores of the current session lStores := lApp.Session.Stores; lPublicFolders := nil; // iterate all the stores and locate the store for // the public folders for i := 1 to lStores.Count do begin // select store lStore := lStores.Item(i); // log List.Items.Add( lStore.DisplayName ); // check for public folder if lStore.ExchangeStoreType = olExchangePublicFolder then begin lPublicFolders := lStore; end; end; if lPublicFolders <> nil then begin // log name List.Items.Add( 'Exchange Public Folder Storage is: ' + lPublicFolders.DisplayName ); // get the root folder lRootFolder := lPublicFolders.GetDefaultFolder( olPublicFoldersAllPublicFolders ); if lRootFolder <> nil then begin // log name List.Items.Add( 'Root folder is ' + lRootFolder.FullFolderPath ); // get folders of root folder lFolders := lRootFolder.Folders; if lFolders <> nil then begin // get the contact folder by its name lContactFolder := lFolders.Item( '<insert your name here>' ); // log the full path List.Items.Add( 'Contact folder is ' + lContactFolder.FullFolderPath ); if lContactFolder <> nil then begin // log number of items List.Items.Add( lContactFolder.Items.Count.ToString + ' contacts in folder.'); // proceed to import/modify contacts end; end; end; end; // -- close Outlook if desired // lApp.Quit; end;
So what does the code do?
We create an instance of CoOutlookApplication which allows us access to Outlook. If Outlook has not been started it will be. This example will not take care of this Outlook instance when you no longer use it. We cannot simply “release” it, because if Outlook was running before our application connects to it and we released it, the Outlook instance that we did not initiate would close. So we cannot simply call the method Quit, for example. There are many means to determine if you got a new Outlook instance (have a look at the Visible property, for example) or are using one that was already opened by the user.
Any Outlook client contains seperate “stores”. If you have multiple email accounts, every account will get its store. Thus, also the Public Folders of an Exchange server will be in their own store. The screenshot at the end of this post shows the log of the running app. In my case I have a separate store for iCloud, Internet Calendars and my Exchange email.
I had to blur any personal information.
There are several constants to determine the type of a store: olExchangePublicFolder is the one we are interested in.
Inside this store, we do have a root folder that contains all the folders. We access this folder by name. This is specific to the name that you used for the public folder and might be something like “Company Contacts”.
After we found the folder, we can access its individual contact items and can proceed as we see fit. For this example we simply log the number of items inside the folder to prove that it worked.
As you can see it is pretty straight forward using Delphi to control Microsoft Office applications and retrieve data from them. The only downside of this approach is the fact that you need to have the office application installed on the same system as your application is executed on.