Consulting

Results 1 to 4 of 4

Thread: Solved: Using MAPI in Excel to Access Outlook Public Folders

  1. #1
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location

    Solved: Using MAPI in Excel to Access Outlook Public Folders

    A consultant designed an Outlook survery form that pulls survery data out of messages from a specific Outlook folder into Excel

    He used the MAPI object from Excel to access the Outlook messages. Two queries

    1) Why use MAPI rather than Outlook?
    2) I've tried early binding from Excel with the CDO Reference rather than late binding. I get a runtime error (MAPI_E_No_ Found) as I track down the folder sytem to "Minerals". For some reason the Folder points to "Favourites" rather than "All Public Folders". The consultant made reference to this issue in his code. If i use
    [vba]
    Set MAPIfold = MAPIobj.InfoStores("Public Folders").RootFolder.Folders(2)
    [/vba]
    it works but i'd like to know why there is an issue



    Cheers

    Dave


    --His Code--

    [vba]
    Set oObject = CreateObject("MAPI.Session")
    oObject.Logon , , False, False
    Set oFolder = oObject.InfoStores("Public Folders")
    Set oFolder = oFolder.RootFolder

    ' Strange thing you seem to have to do for the RootFolder of an InfoStore!

    For i = 1 To oFolder.Folders.Count
    If oFolder.Folders(i).Name = "All Public Folders" Then
    Set oFolder = oFolder.Folders(i)
    Exit For
    End If
    Next
    Set oFolder = oFolder.Folders("Minerals")
    [/vba]

    ---My code----

    [vba]
    Sub GeteMail()
    Dim MAPIobj As MAPI.Session, MAPIfold As MAPI.Folder
    Set MAPIobj = New MAPI.Session
    MAPIobj.Logon , , False, False

    Set MAPIfold = MAPIobj.InfoStores("Public Folders").RootFolder.Folders("All Public Folders")
    'my code fails below
    Set MAPIfold = MAPIfold.Folders("Minerals")
    End Sub
    [/vba]

  2. #2
    VBAX Tutor jamescol's Avatar
    Joined
    May 2004
    Location
    Charlotte, NC
    Posts
    251
    Location
    The Namespace object represents the messaging service provider or message store. Currently the only available message store in Outlook is MAPI, which allows access to all Outlook folders and items.

    In your case, you should use
    [vba]
    Set MAPIobj = CreateObject("MAPI.Session")
    [/vba]

    instead of

    [vba]
    Set MAPIobj = New MAPI.Session
    [/vba]

    The latter is used when you are already within an established Outlook session, such as when working within Outlook itself. You may also consider including a logon command in case Outlook is not already running.

    [vba]
    objSession.Logon ("<Your Profile Name Here>")
    [/vba]

    Getting to subfolders is not the easiest task. I use this syntax myself working with a specific subfolder, as it is easy to follow and debug.

    [vba]
    Set olApp = CreateObject("Outlook.Application")
    Set olns = ol.GetNameSpace("MAPI")
    Set MyFolder1 = olns.Folders("Public Folders")
    Set MyFolder2 = MyFolder1.Folders("All Public Folders")
    Set MyFolder3 = MyFolder2.Folders("Minerals")
    [/vba]

    Another method you can use is to obtain the EntryID and StoreId of the folder and use them to directly set an object to the public folder you want. The only drawback is with 1) a distributed application where users may be using different Exchange servers, thus different StoreIDs, or 2) situations where users may be accessing replicated public folders where again, the StoreID will differ. You can eliminate the StoreID and simply use the EntryID, but searching for the PF might be slow in teh above two cases.

    Also, if the PF is ever relocated to a different store, its EntryID will change as well as the StoreID.

    So, to continue the example above:

    [vba]
    Dim myEntryID As String
    Dim myStoreID As String
    Dim PublicMineralFolder As Outlook.MAPIFolder

    myEntryID = myFolder3.EntryID
    myStoreID = myFolder3.StoreID

    'Save the above values as constants, and all you have to do is
    'use the following code to set the Minerals folder

    Set PublicMineralFolder = myOlApp.Session.GetFolderFromID(myEntryID, _
    myStoreID)
    [/vba]

    Hope this helps!

    Cheers,
    James
    "All that's necessary for evil to triumph is for good men to do nothing."

  3. #3
    VBAX Regular
    Joined
    May 2004
    Location
    Sydney, Australia
    Posts
    36
    Location
    Brett,

    TO add to what James wrote. I spent the last hour researching this (what the hell, it's Friday so I'm not working ). Anyway, I think this may be of relevance:-

    href=http://msdn.microsoft.com/library/de...ion_object.asp

    In particular, this bit:-

    Note The Item(searchValue) syntax uses the IMAPITABLE::FindRow method, which performs a search dependent on the current sort order of the table underlying the collection. Not all tables are sorted alphabetically. The Microsoft Exchange Public Folders folder, for example, is held in a nonalphabetic order, and you should access its subfolders using the Item(index) syntax.
    HTH
    Dan

  4. #4
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location
    James & Dan

    Thank you kindly for your help.

    I've adopted the method that James used (but with my original early binding) to drill down to the correct folder

    Much appreciated

    Cheers

    Dave

Posting Permissions

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