Linking an Outlook Folder with VBA

NauticalGent said:

Greetings AWF,

I have posted a question in a different thread regarding using VBA to link a public OutLook Folder; the VBA equivalent to choosing External Data->  More -> Outlook Folder from the menu.

To date, I was not able to get quite what I was looking for. The issue is that Outlook stores a replicated version of folders that you have access to within the user’s profile and synchs them with the server version. Because of this the user’s profile name is stored with the connection string of the linked OL folder/table.

So what’s the big deal?

The deal is this; whenever I did a new installation or FE Update, the connection string contained MY profile and the table would not be linked. Without a means of linking/re-linking with VBA, I either had to go around to each workstation and link then manually, or allow the users to “get under the hood (hold the shift key down at start-up)” and link the folder themselves. Neither one was optimal or efficient.

I stumbled across the answer and thought I would share it here.
The key is to link the table manually to determine the connection. The easiest way obtain the connection is to open the linked folder in design view, ignoring the “linked tables cannot be modified…” message and click “Yes”. Choose Property Sheet from the Show/Hide group and copy the string in the Description field. It should look like this:

Outlook 9.0;MAPILEVEL=Public Folders - John.Doe@somedomain.com|\Favorites\;PROFILE=Default;TABLETYPE=0;TABLENAME=tblMyTable;COLSETVERSION=12.0;DATABASE=C:\Users\JOHN.DOE\AppData\Local\Temp\1\;TABLE=tblMyTable

Once you have that you are ready to write your module: Bear in mind that I have a separate module that checks for a valid connection string. If it isn’t, the linkOutLookFolder() sub is called.

Code:

Public Sub linkOutLookFolder()
On Error GoTo err_handler
Dim myDb as Database
Dim myTD as TableDef
Dim strConnect as String
 
' This sub checks to see if the table exists. Even though we know at this
' point the connection string is bad, the table can still be in the Navigation
' pane and the code will error out with a “Table already exists” message

       If ObjectExists(“tblMyTable”) Then
           DoCmd.DeleteObject actable, “tblMyTable”
      End If

     Set myDb = Currentdb
    
      strConnect = “Outlook 9.0;MAPILEVEL=Public Folders – “ & _
      fosUserName() & “@somedomain.com|” & _
     “\Favorites\;PROFILE=Default;TABLETYPE=0 & _
     ";TABLENAME=tblMyTable;COLSETVERSION=12.0;” & _
     “DATABASE=C:\Users\” & fosUserName() & _
      “\AppData\Local\Temp\1\;TABLE=OutLookFolder”

     ' The fosUserName() function can be found in this forum. It is needed
    ' to make this sub dynamic.
 
     Set myTD = myDb.CreateTableDef(“tblMyTable”)
     myTD.Connect = strConnect
     myTD.SourceTableName = “OutLookFolder”
     myDb.TableDefs.Append myTD
 
err-handler:
     Debug.Print Err.Description
     MsgBox Err.Description

End Sub

One thing to note it that the table will not show up in the Navigation Pane immediately, but the OL folder DID in fact get linked.
Hope others find this useful and if there are any improvements that can be made, I am willing to hear them.

Click to expand...