Processing PST files with VBA Excel

[ErikJan]

I've been searching for hours but don't seem to find clear solutions to what I'm looking for.

From Excel, I'd like to find PST files on my system (that works now) and then I'd like to read these PST files (process emails, get their size etc.).

My system does have Outlook installed.

I'd think there's two situations:

1. The PST is not open in Outlook (either because Outlook isn't running or because the PST isn't loaded in Outlook).
-- In this case I probably need to process the PST 'outside' Outlook

2. The PST is open in Outlook.
-- In this case I'll need Outlook calls to process

I believe the two states are different (not sure) and if they are I first need to find out which state the file is in. Alternatively (and that would be fine), I can first test is Outlook is open and let it be closed first before processing. Maybe that's the best way?

In any case, how do I then process the PST? All I need is a start to figure it out from there.

[HansV]

As far as I know, you do need Outlook to read a PST file. You can use

Code: Select all

    dim objOL As Object
    Set objOL = CreateObject("Outlook.Application")

If Outlook is running, this will grab the running instance; otherwise it will start Outlook.

Next, you can loop through the objOL.Session.Stores collection:

Code: Select all

    Dim stPath As String
    Dim st As Object
    stPath = "C:\Outlook\Archive.pst"
    For Each st In Application.Session.Stores
        If st.IsDataFileStore Then
            If st.FilePath = stPath Then
                Exit For
            End If
        End If
    Next st
    If st Is Nothing Then
        Application.Session.AddStore stPath
        For Each st In Application.Session.Stores
            If st.IsDataFileStore Then
                If st.FilePath = stPath Then
                    Exit For
                End If
            End If
        Next st
    End If
    ' Do something with st
    ...

Warning: make sure that the filename is correct. If not, Outlook will create a new PST file!

[ErikJan]

For Each st In Application.Session.Stores

"Object doesn't support this property or method"

[HansV]

Sorry, replace all instances of Application with objOL

[ErikJan]

Yep, but now it failed here: objOL.Session.AddStore stPath

I checked some more and found this was caused by the fact that this PST was password protected. Just reporting here ;-)

2020 Dec 19 152601-Microsoft Visual Basic.png

[ErikJan]

What I see is that PST files are loaded in Outlook. I'd like to prevent that (and many commercial tools can do that too). How would I do that?

[HansV]

As far as I know, you can't do that with standard VBA. You'd have to use a programming library that can read .pst files directly. I don't know of such a library, so I cannot help you with this.

[ErikJan]

Thanks Hans. Seems like it stops here as I also wouldn't have a clue and no one else engages ;-)