Results 1 to 15 of 15
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open Outlook PST from Excel VBA (Office 2003)

    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).

    ([img]/forums/images/smilies/cool.gif[/img] 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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open Outlook PST from Excel VBA (Office 2003)

    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.

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Outlook PST from Excel VBA (Office 2003)

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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open Outlook PST from Excel VBA (Office 2003)

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

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Outlook PST from Excel VBA (Office 2003)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open Outlook PST from Excel VBA (Office 2003)

    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.

  7. #7
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Outlook PST from Excel VBA (Office 2003)

    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:<pre><font color=blue>
    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
    </pre>

    </font color=blue>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:
    <pre><font color=blue> Dim ses As New MAPI.Session
    Dim fld As MAPI.Folder
    Dim msg As MAPI.Messages
    ses.Logon , , False, False
    </font color=blue><font color=red>Set fld = ses.GetFolder(fld1, SelFldrID)</font color=red><font color=blue>
    '
    ' Code goes here
    '
    ses.Logoff
    Set msg = Nothing
    Set fld = Nothing
    Set ses = Nothing
    </pre>

    Regretfully the <font color=red>red line</font color=red> 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?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open Outlook PST from Excel VBA (Office 2003)

    Use

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

  9. #9
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Outlook PST from Excel VBA (Office 2003)

    Doesn't work:

    Run-time error '-2147220991 (80040201)':

    [MAPI - [MAPI_E_UNKNOWN_ENTRYID(80040201)]]

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open Outlook PST from Excel VBA (Office 2003)

    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.

  11. #11
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Outlook PST from Excel VBA (Office 2003)

    Got it...
    <pre> SelFldrEID = fld1.EntryID
    SelFldrSID = fld1.StoreID</pre>

    and later in CDO:<pre> Set fld = ses.GetFolder(SelFldrEID, SelFldrSID)</pre>

    Up to my next hurdle <img src=/S/duck.gif border=0 alt=duck width=23 height=23>

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open Outlook PST from Excel VBA (Office 2003)

    Good for you! Thanks for sharing the solution!

  13. #13
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Outlook PST from Excel VBA (Office 2003)

    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 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).

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open Outlook PST from Excel VBA (Office 2003)

    Thanks, that's interesting.

  15. #15
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Open Outlook PST from Excel VBA (Office 2003)

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •