View Full Version : VBA Allow User to PickFolder

2014-10-03, 06:28 AM
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.

'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 = MsgBox("Please select the folder with NEW e-mails", vbInformation + vbYesNo, "Select Inbox") Then Exit Sub 'allow the user to cancel
Set oInFolder = Outlook.GetNamespace("MAPI").PickFolder '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

Ken Puls
2014-10-03, 04:31 PM
I'm not totally following the rationale on not going with a custom form. Why not create a simple form like this:

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.

2014-10-06, 02:33 AM
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