VBA Allow User to PickFolder

WizzardOfOz

New member
Joined
Sep 4, 2013
Messages
184
Reaction score
0
Points
0
Location
Australia
Excel Version(s)
Office 365
Google searching says that the PickFolder function can't display a user defined message. I'm asking the user to select three folders. The inbox, the project archive and a duplicate (or bin) folder. As per the code below I ask using a message box to display the requested folder, then the PickFolder function. I don't want to do a user defined form and add a treeview control because the project folder is HUGE and I don't want it to add the whole structure.

Any simple ideas. I thought of Inputbox and getting the user to select the folder in the normal navigation pane but there is no Type variable in Outlook's InputBox function


The recursive function RDuplicateMails then moves each item from the Inbox to either the project folder or if it's there already to the duplicates folder.

Code:
'inspired by http://randomscribblepad.blogspot.com.au/2011/01/automating-tasks-in-outlook-using-vb.html
Public Sub DeleteDuplicateMails()
Dim oInFolder As Folder, oProjFolder As Folder, oDupfolder As Folder    'the folders
Dim oProjMailItems                                        'the sorted collections


    'ask the user for the three folders
    If vbNo = [COLOR=#ff0000]MsgBox("Please select the folder with NEW e-mails", vbInformation + vbYesNo, "Select Inbox") [/COLOR]Then Exit Sub 'allow the user to cancel
    [COLOR=#0000ff]Set oInFolder = Outlook.GetNamespace("MAPI").PickFolder[/COLOR]         'where is the new information
    
    If vbNo = MsgBox("Please select the destination PROJECT folder", _
        vbInformation + vbYesNo, "Select Project Archive") Then Exit Sub 'allow the user to cancel
    Set oProjFolder = Outlook.GetNamespace("MAPI").PickFolder       'where is the Project archive
    
    If vbNo = MsgBox("Please select the backup DUPLICATE folder", _
        vbInformation + vbYesNo, "Select Duplicate Archive") Then Exit Sub 'allow the user to cancel
    Set oDupfolder = Outlook.GetNamespace("MAPI").PickFolder        'where do the duplications go to
    
    If vbNo = MsgBox("Inbox: " & oInFolder.Name & vbCrLf & _
                    "Project : " & oProjFolder.Name & vbCrLf & _
                    "Duplicates : " & oDupfolder.Name, vbInformation + vbYesNo, "Final Confirmation") Then Exit Sub 'allow the user to cancel
    


    'set the prj items
    Set oProjMailItems = oProjFolder.Items
    oProjMailItems.Sort "[ReceivedTime]", olAscending
    
    Call RDuplicateMails(oInFolder, oProjFolder, oDupfolder, "", oProjMailItems)  'Call recursive
    
    'clean up any references
    Set oProjMailItems = Nothing
    Set oInFolder = Nothing
    Set oProjFolder = Nothing
    Set oDupfolder = Nothing
    
    Call MsgBox("done")


End Sub
 
I'm not totally following the rationale on not going with a custom form. Why not create a simple form like this:
10-3-2014 8-26-09 AM.png

You could let the user type in the name of the folder, or select it using the Browse button. I'm thinking that you could leverage the PickFolder method to retrieve the path, and you probably wouldn't even need to go to a Treeview. (To be fair, I haven't worked with PickFolder... I assume it returns a folder path?)

Last, I'd just add a little validation in the "Do it!" button to make sure that all the paths are valid before you run the rest of the routine.

I'd think that would still be pretty lightweight overall...

If you want it, I've also uploaded the components that you could import the form as pictured here. No code yet, but would give you a design to start.
 

Attachments

  • UserForm1.zip
    1.3 KB · Views: 92
Hi Ken,
Yes PickFolder brings a standard dialogue box up and allows the user to browse thru the list to select a folder (or return the list of child folders with .Folders). It seems to read the standard Outlook navigation bar so if the public folder is already open it reads significantly quicker then doing so via code.

Unfortunately I have no control of the public folder structure, the path is /Public Folders - username/All Public Folders/Country/State/Projects and Clients/Client/Project folder. Unfortunately it's a large global company and at a minimum they should be splitting the folder into country. Asking a user to type a 7 level deep project folder is not going to work. Returning all the folders just to select one is hitting the problem with a very large hammer. Works but not elegant or fast.

If I go the treeview path I would only display one level down and if the user selected a subfolder then add that branch. Alternatively if there was a way to escape the program and allow the user to select the folder (with currentfolder) such as input box does in Excel.

Thanks anyway, will put this one on hover for now
 
Back
Top