Title: A database-like approach for accessing Outlook data from Delphi
Question: How can I use Delphi to read Outlook data without having to learn automation?
Answer:
Seeking the web for help on how to read Outlook messages or address books by Delphi, one usually is led to information on Outlook automation such as http://www.djpate.freeserve.co.uk/AutoOutl.htm or http://www.delphi3000.com/articles/article_3336.asp. Playing around with these methods, however, I found out that they are awfully slow when accessing larger folder files. Another annoying issue is the security alert displayed by newer versions of Outlook when reading an address book. And besides that, automatition appears to me to be poorly documented and difficult to understand.
By chance I came across a microsoft site (see at the end of this articel) which opens a completely different approach to read Outlook data by Delphi, namely by ADO in a similar way to Access databases. It seems that the Jet ADO provider encorporates routines to connect to Exchange and to Outlook as well. Setting up the correct connection string to be used in a TADOConnection component is the key to this approach. For accessing data from the Exchange server the connection string should be composed like that:
connstr := 'PROVIDER=Microsoft.Jet.OLEDB.4.0;'+
'EXCHANGE 4.0;'+
'MAPILEVEL=;'+
'DATABASE=C:\Temp;'+
'TABLETYPE=0;';
- The section "PROVIDER" identifies the Jet ADO provider, just in the same way as with Access databases for example.
- The phrase "EXCHANGE 4.0" directs the Jet ADO provider to use the Exchange IISAM (installable indexed sequential access method). "OUTLOOK 9.0" can be used as well, my experiments, however, indicate some performance loss.
- The section "MAPILEVEL" indicates which folder is to be opened. An empty MAPILEVEL setting means that the names of the top level folders will be extracted. If MAPILEVEL is set to a top level folder, a pipe symbol ( | ) must be appended. The names of deeper-lying folders need to be separated by backslashes ( \ ). The exact operation will be explained in more detail by some examples below.
- The section "DATABASE" refers to a directory where the Jet ADO provider can store a "schema.ini" file describing the database fields read from Outlook. Omitting this specification or selecting a non-existent directory is not harmful; the current directory will be used for this purpose instead.
- The TABLETYPE entry specifies whether messages (TABLETYPE=0) or adress entries (TABLETYPE=1) will be retrieved from Outlook. In the former case the TABLETYPE section can be omitted entirely, in the latter case the MAPILEVEL specification is not necessary instead.
To experiment with ADO to read Outlook data, let's open a new project and put a TADOConnection, TADOTable, TDatasource and a TDBGrid component on a form. For the following explanations the components will be named in the standard way (i.e ADOConnection1, ADOTable1, etc). Connect the components as usual (DBGrid.Datasource := Datasource1, Datasource1.Dataset := ADOTable1; ADOTable1.Connection := ADOConnection1).
Now suppose an Outlook folder structure with a personal folder "Old Mails" and a standard mailbox named "Mailbox WP" as sketched here:
|
+--- Old Mails
| +--- mail coming in
| | +--- important
| | +---- confidential
| +--- mail sent
| +--- important
| +---- confidential
| +---- not confidential
|
+--- Mailbox - WP
| +--- inbox
| +--- outbox
| +--- contacts
| +--- ....
Now let's do some experiments:
- In order to display all messages of the inbox in the standard mailbox use the MAPILEVEL setting
"MAPILEVEL=Mailbox WP|"
in the above-mentioned connection string to be typed into the ConnectionString property of ADOConnection1. Note the trailing pipe symbol after the mailbox name to indicate that this is a top level folder. Set ACOConnection1.Connected to true, select "inbox" from the ADOTable1.TableName dropdown list, open this table by setting ADOTable1.Active to true, and you will see all your inbox messages in the DBGrid.
- To retrieve the "important" "confidential" messages contained in the "mail sent" folder of the personal folder "Old Mails", use the setting
"MAPILEVEL=Old Mails|mail sent\important"
in the corresponding part of the connection string, and select the table "confidential" from the ADOTable1.TableName list. Again, the messages contained in that folder will appear in the DBGrid.
As is often the case with ADO, the setting of the CursorLocation property is critical for correct functioning and optimum performance. Retrieving messages from a large personal folder, for example, can be speeded up extremely (from minutes down to seconds) if the clUseServer setting is used. The same setting, however, prevents successful data retrieval from my server-based mail box; clUseClient was needed in this case. No idea to understand that...
I do not want to finish this article without mentioning some drawbacks of this approach. So far, I could not find a way to extract attachments from the mails, and I also had to observe a crash when accessing the "contacts" folder of my mailbox. But nevertheless, maybe this is an interesting approach for some users in the Delphi community.
References:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsmart00/html/sa00h12.asp
http://www.rntsoft.com/database/connectionstrings.asp