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.
Processing PST files with VBA Excel
-
- BronzeLounger
- Posts: 1242
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
-
- Administrator
- Posts: 78481
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Processing PST files with VBA Excel
As far as I know, you do need Outlook to read a PST file. You can use
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:
Warning: make sure that the filename is correct. If not, Outlook will create a new PST file!
Code: Select all
dim objOL As Object
Set objOL = CreateObject("Outlook.Application")
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
...
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1242
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
Re: Processing PST files with VBA Excel
For Each st In Application.Session.Stores
"Object doesn't support this property or method"
"Object doesn't support this property or method"
-
- Administrator
- Posts: 78481
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Processing PST files with VBA Excel
Sorry, replace all instances of Application with objOL
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1242
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
Re: Processing PST files with VBA Excel
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 ;-)
I checked some more and found this was caused by the fact that this PST was password protected. Just reporting here ;-)
You do not have the required permissions to view the files attached to this post.
-
- BronzeLounger
- Posts: 1242
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
Re: Processing PST files with VBA Excel
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?
-
- Administrator
- Posts: 78481
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Processing PST files with VBA Excel
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.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1242
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
Re: Processing PST files with VBA Excel
Thanks Hans. Seems like it stops here as I also wouldn't have a clue and no one else engages ;-)