Accesing Exchange Public Folders via Excel VBA Script

[getrighteous]

I am trying to write a VBA script in Excel that will allow me to access other spreadsheets that have been posted to a Public Folder in Exchange. I have researched this for a long time, tried some code samples, but nothing seems to work.

Here is where I am at currently:

  Dim objNS As Outlook.Namespace
  Dim colFolders As Outlook.Folders
  Dim objFolder As Outlook.MAPIFolder
  Dim arrFolders() As String
  Dim I As Long
  On Error Resume Next

  strFolderPath = "Public Folders\All Public Folders\company\testfolder"
  arrFolders() = Split(strFolderPath, "\")

  Set objNS = Application.GetNamespace("MAPI")
  Set objFolder = objNS.Folders.Item(arrFolders(0))
  If Not objFolder Is Nothing Then
    For I = 1 To UBound(arrFolders)
      Set colFolders = objFolder.Folders
      Set objFolder = Nothing
      Set objFolder = colFolders.Item(arrFolders(I))
      If objFolder Is Nothing Then
        MsgBox ("Empty")
        Exit For
      Else
        MsgBox (I)
      End If
    Next
  End If

  Set GetFolder = objFolder
  Set colFolders = Nothing
  Set objNS = Nothing
  Set objApp = Nothing

But objNS is getting set to nothing. Am I missing something? Is there a more elegant solution?

Thanks!

 

[getrighteous]

OK, I bascially got this to work by removing "Application."

My next question...

I have navigated down to the appropriate subfolder in Public Folders, and within that subfolder I have a single Excel file that I now want to open. Something like:

Set openwb = Workbooks.Open(Filename:=vPath & vFileName)

that could be used to open a file on the filesystem doesn't work since I am working in Public Folders.

Any ideas?