Open Outlook PST from Excel VBA (Office 2003)

Topic

New Reply

I plan to do a project that involves manipulating emails. Although I started to do this in Outlook VBA, some plans I have to e.g. report intermediate results made me decide to switch to Excel (I’m also more ‘at home’ in Excel VBA to be honest).

The actions I’m planning will occur (only) on separate PST-files [Outlook Personal Folders] (I know there are also OST files, don’t know the difference but for now, again, my focus is on PST files as I have these).

Now there seem to be two ways to get to the emails in the PST file:

(A) Indirectly. Load the PST in Outlook and then program in Excel to open an Outlook.app and do the processing from there (what I find everywhere when I search the Internet).

( Forget about Outlook and work with the PST file directly (of course then the PST shouldn’t be open in Outlook at the same time as it will then be locked).

Option “B” seems most elegant. Is that possible? In other words, can I select a PST-file in EXCEL VBA en then open it (if required, providing the password) and then read, process and write it (using normal “Outlook” calls)? In the first ‘sweep’ I just want to go over the folder structure and acquire information on all emails (type, size, #attachments, size of attachments, names of attachments, dates subject etc.). After I’m done, could I even ‘compress’ it the way its done via Outlook?

Again, I can get to the emails using option “A” pretty easily but I’d like B much better as it is ‘cleaner’ in its approach.

Ideas? Code snippets?

Thanks!

Erik Jan

Reply | Quote

Replies

You can use Automation to control Outlook from Excel (plan A) or you can use CDO (Collaborative Data Objects) to read mail folders. Here’s a very simple example:

Dim ses As New MAPI.Session
Dim fld As MAPI.Folder
Dim msg As MAPI.Messages
ses.Logon
Set fld = ses.GetDefaultFolder(CdoDefaultFolderInbox)
Set msg = fld.Messages
MsgBox msg.Count
ses.Logoff
Set msg = Nothing
Set fld = Nothing
Set ses = Nothing

To run this code, you need to set a reference to the Microsoft CDO 1.21 Library.

See the CDO reference on MSDN.

Reply | Quote

Thanks but these are both option A, right? Anything on option B?

Reply | Quote

The CDO option doesn’t start Outlook, so it’s more like option B.

Reply | Quote

Not sure… the “ses.logon” comes up with a prompt for my Outlook profile. Then the count tells me how many items I have in my Inbox.

My option B should work if e.g. outlook is not installed and/or enabled; just the PST file should be required (I hope).

Maybe I’m not seeing this yet, I’m expecting a line that makes me select the PST file from somewere and tells me (e.g.) the name of the top-folder etc. Things like Inbox, contacts, calendar, notes should have nothing to do with that, my PST ONLY has emails in a directory structure.

EJ

Reply | Quote

Look up Login in the CDO reference – you can specify a profile.

There is no way to process a .pst file if Outlook is not installed, as far as I know.

Reply | Quote

OK, I will use CDO to do the work here.

So now I should find a way to have the user select the PST (or a subfolder thereof). The non-CDO code is simple and elegant:

    Dim olApp As Outlook.Application, SelFldrID
    '
    Dim nsp As Namespace
    Dim fld1 As MAPIFolder
    Set olApp = New Outlook.Application
    Set nsp = olApp.GetNamespace("MAPI")
    Set fld1 = nsp.PickFolder
    SelFldrID = fld1.StoreID
    Set nsp = Nothing
    Set olApp = Nothing

Maybe I’m all wrong here but using the “nsp.PickFolder” is so nice and easy. All I need now is to ‘save’ the IDs from the selected folder somehow.
The ‘fld1’ and ‘SelFldrID’ are attempts to “inherit” this information over to the main part where I use CDO:

    Dim ses As New MAPI.Session
    Dim fld As MAPI.Folder
    Dim msg As MAPI.Messages
    ses.Logon , , False, False
    Set fld = ses.GetFolder(fld1, SelFldrID)
    '
    ' Code goes here
    '
    ses.Logoff
    Set msg = Nothing
    Set fld = Nothing
    Set ses = Nothing

Regretfully the red line does not work like this. Maybe I should get the parent-name first and use that somehow?

Any suggestions or alternate approaches I could use?

Reply | Quote

Use

SelFldrID = fld1.EntryID
…
Set fld = ses.GetFolder(SelFldrID)

Reply | Quote

Doesn’t work:

Run-time error ‘-2147220991 (80040201)’:

[MAPI – [MAPI_E_UNKNOWN_ENTRYID(80040201)]]

Reply | Quote

It works for me if I pick a folder in my default .pst file (that’s what I tested on before I posted my previous reply), but not if I pick a folder in one of the other open .pst files – that causes the error message you mention. Apparently the Session object can only access the default .pst. I’m afraid I have no idea how to get around this.

Reply | Quote

Got it…

    SelFldrEID = fld1.EntryID
    SelFldrSID = fld1.StoreID

and later in CDO:

    Set fld = ses.GetFolder(SelFldrEID, SelFldrSID)

Up to my next hurdle

Reply | Quote

Good for you! Thanks for sharing the solution!

Reply | Quote

Just a little piece of information I found yesterday, not relevant to the progress in this thread but interesting to know in the context of the discussion here.

This http://www.pstwalker.com/index.html%5B/url%5D software does walk through a PST without the need for Outlook (it’s beyond me how it’s done and the code is not available but it seems to be possible).

Reply | Quote

Thanks, that’s interesting.

Reply | Quote

Nice Find. It would be useful to repost this find in the Outlook Forum – if it does what it claims, it may help Loungers with corrupted PST files.

Reply | Quote