Storing an Outlook Folder Location

[deafmetal1]

Hello,

In my quest to build a email message parsing tool, I can manually VBA code the folder location I wish to parse. But I'd rather provide a button on the form to which would result in a folder menu for the user to select where the parsing is to occur. This prevents a novice user from having to enter VBA to manually edit the folder location, and quite possibly screw it up.

I can get the folder selection menu to work, but I cannot figure out how to store the location they select. I built a table that I tried to store the selected location in... no joy. I'd also like my VBA to pull the folder location from that table.

Here's the folder list code I have so far that I cannot figure out how to store and retrieve:

Private Sub FLDR_Click()
    Dim rs As DAO.Recordset
    Dim strPath As String
        
    strPath = Application.CodeProject.Path
    
    Dim olApp As Outlook.Application
    Set olApp = New Outlook.Application
    
    Dim olNS As Outlook.NameSpace
    Dim MyFolder As Outlook.MAPIFolder
    
    On Error Resume Next
    
    Set olNS = olApp.GetNamespace("MAPI")
    Set MyFolder = olNS.PickFolder
        
    Set rs = rs.OpenRecordset("Folder")
             
        rs.AddNew
        
        rs("Folder").Value = Left(olNS, 255)
        
    Set rs = Nothing
    Set olNS = Nothing
    Set MyFolder = Nothing
    
End Sub

Here's the hard coded folder location that I have been using in lieu of a user friendly selection box:

Dim strPath As String
    strPath = Application.CodeProject.Path
    
Dim olApp As Outlook.Application
    Set olApp = New Outlook.Application
    
Dim olNS As Outlook.NameSpace
    Set olNS = olApp.GetNamespace("MAPI")
    
    ' this is the location of the messages
    ' Public Folder Code below:
Dim olFld As Outlook.MAPIFolder
    Set olFld = olNS.Folders("Public Folders").Folders("All Public Folders").Folders("Testing")

Appreciate your assistance!!

 

[deafmetal1]

With some help from Ace, we've come up with this so far:

Private Sub FLDR_Click()

    Dim rs As DAO.Recordset
    Dim strPath As String
        
    strPath = Application.CodeProject.Path
    
    Dim olApp As Outlook.Application
    Set olApp = New Outlook.Application
    
    Dim olNS As Outlook.NameSpace
    Dim MyFolder As Outlook.MAPIFolder
    
    On Error Resume Next
    
    Set olNS = olApp.GetNamespace("MAPI")
    Set MyFolder = olNS.PickFolder
   
    [COLOR=blue]with CurrentDb     
       Set rs = .OpenRecordset("Folder")
       rs.AddNew
       rs("Folder").Value = Left([COLOR=red]MyFolder.FolderPath[/COLOR], 255)
        rs.update
    end with[/COLOR]    
    
    Set rs = Nothing
    Set olNS = Nothing
    Set MyFolder = Nothing
    
End Sub

Still haven't figured out how to keep only one path stored in the table, no matter what you change it to. And be able to call up the path to use in the parsing Sub.

 

[deafmetal1]

Ok, bear with me, we're learning together. Here's how I got rid of table data to ensure their is only one path at all times:

Private Sub FLDR_Click()
'clear table that records skipped messages
Dim objQry As QueryDef
Dim db As Database
Dim rs As DAO.Recordset
Dim strSQL As String
 
'clear table used for input of Folder Path
Set db = CurrentDb
 
strSQL = "DELETE MSG_Folder.* FROM MSG_Folder;"
Set objQry = db.CreateQueryDef("", strSQL)
 
DoCmd.SetWarnings False
objQry.Execute
DoCmd.SetWarnings True
 
Set objQry = Nothing
 
Set db = Nothing
 
 
 
'Dim rs As DAO.Recordset
Dim strPath As String
 
strPath = Application.CodeProject.Path
 
Dim olApp As Outlook.Application
Set olApp = New Outlook.Application
 
Dim olNS As Outlook.NameSpace
Dim MyFolder As Outlook.MAPIFolder
 
On Error Resume Next
 
Set olNS = olApp.GetNamespace("MAPI")
Set MyFolder = olNS.PickFolder
 
With CurrentDb
 
Set rs = .OpenRecordset("MSG_Folder")
rs.AddNew
rs("Folder").Value = Left(MyFolder.Name, 255)
rs("FolderPath").Value = Left(MyFolder.FolderPath, 255)
rs.Update
End With
 
Set rs = Nothing
Set olNS = Nothing
Set MyFolder = Nothing
End Sub

Now that I have the path in the table, when I do the MAPI Outlook access code for the parsing, how do I point it to the path listed in my table?