Outlook VBA to Replicate 'Sort Subfolders A to Z' in the Folder Pane

Question

Is there a method in VBA to achieve the same effect of right-clicking on a folder in the folder pane and selecting 'Sort Subfolders A to Z'?

As a comparison, the code below from Microsoft.com sorts Items in a folder; however, it does not appear that the .Sort method used in this code is available for the Folders object like it is for the Items object.

Sub SortByDueDate() 
 Dim myNameSpace As Outlook.NameSpace 
 Dim myFolder As Outlook.Folder 
 Dim myItem As Outlook.TaskItem 
 Dim myItems As Outlook.Items 
 
 Set myNameSpace = Application.GetNamespace("MAPI") 
 Set myFolder = myNameSpace.GetDefaultFolder(olFolderTasks) 
 Set myItems = myFolder.Items 
 myItems.Sort "[DueDate]", False 
 For Each myItem In myItems 
 MsgBox myItem.Subject & "-- " & myItem.DueDate 
 Next myItem 
End Sub

Additionally, it does not appear that there are any methods available for moving folders in the tree.

Is more extensive code required to replicate the native 'Sort Subfolders A to Z' action with VBA?

Can this be achieved with PropertyAssessor and, if so, what is the proper syntax for setting the PR_SORT_POSITION property? For example, the code below results in an error, as commented in the code.

Sub Example()

  Dim myProp As String
  Dim myValue As Variant
  Dim oFolder As Folder
  
  Set oFolder = Application.Session.GetDefaultFolder(olFolderInbox)
  myProp = "http://schemas.microsoft.com/mapi/proptag/0x30200102"
  myValue = "FD7F"
  oFolder.PropertyAssessor.SetProperty myProp, myValue 'Run-time error '438': Object doesn't support this property or method


End Sub

Answer 1

The Outlook object model doesn't provide any property or method to sort folders. You may find the NavigationPane object helpful. See Customizing the Navigation Pane for more information.

Answer 2

You can sort the folders in the Outlook UI by explicitly setting the PR_SORT_POSITION property on each subfolder - see Get folder list ordered as displayed

Answer 3

I posted my code here because this was high in Google results and all other threads were closed https://answers.microsoft.com/en-us/outlook_com/forum/all/sorting-outlook-subfolders-z-a/9aef727c-510c-49e0-869d-4234373b71d7 https://answers.microsoft.com/en-us/outlook_com/forum/all/sort-order-of-subfolders/a3b55181-4f5a-43c1-82b3-94eb68a8407b

I've made custom VBA code to sort subfolders Z-A - it will load the folder order [unfortunately you still need to order it A-Z within outlook] and then reverse it so it is Z-A

I needed to quickly adjust a tonne of folders and couldn't find any code anywhere, so I quickly made the below to help patch the issue.

I didn't have the time to write lots of detail about how it works.

Known issues with the code: It doesn't always sort the first folder. No idea why. It doesn't seem to like it when you're looking at the list of subfolders - minimise it then run the code

This code is used to reverse the sorting of subfolders under Inbox, you'll need to adjust as required.

Sub sortZA()
Dim email_name:  email_name = "email@emails.com"  'write the name of the mailbox as it appears in outlook

Dim objMainFolder As Outlook.Folder
Dim Folders As Outlook.Folders
Dim Folderx As Outlook.Folder
Dim sort_order, sort_order_b, arr
Set arr = CreateObject("System.Collections.ArrayList")
Set arr_sorted = CreateObject("System.Collections.ArrayList")
dim found_folder:   found_folder=0
Set Folders = Application.ActiveExplorer.Session.Folders
For Each Folderx In Folders
   If LCase(Folderx.Name) = LCase(email_name) Then
       Set objMainFolder = Folderx.Folders("Inbox") 'adjust as required. Add more folders via .folders("name")
       found_folder=1
   End If
Next
     
if found_folder =0 then
    msgbox "the email folder with the name '" & email_name & "' was not found"
    exit sub
end if

Dim reloadFolder As Outlook.Folder
Dim propertyAccessor As Outlook.propertyAccessor

For Each Folderx In objMainFolder.Folders
    ' if there is an error, then there might not be any order data. Try reordering them manually. Also make sure loading the email as the main profile instead of as an additional mailbox.
    'On Error Resume Next
    
    Set propertyAccessor = Folderx.propertyAccessor
    sort_order = propertyAccessor.BinaryToString(propertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x30200102"))

    arr.Add Folderx.Name & "##~~##" & sort_order
    arr_sorted.Add Folderx.Name & "##~~##" & sort_order
Next

arr.Sort 'keep A-Z (the original list from outlook isn't in A-Z order)
arr_sorted.Sort 'make A-Z
arr_sorted.Reverse 'make Z-A


Dim t, a, b, i, t2, a2, b2
i = 0

For Each arr_folder In arr
    t = Split(arr_folder, "##~~##")
    a = t(0)  'which folder name?
    b = t(1)  'what is the original order? [should already be A-Z]
    
    Set Folders = Application.ActiveExplorer.Session.Folders
    For Each Folderx In Folders
       'On Error Resume Next
       If LCase(Folderx.Name) = LCase(email_name) Then
           Set reloadFolder = Folderx.Folders("Inbox").Folders(a)
       End If
    Next

    t2 = Split(arr_sorted(i), "##~~##")
    a2 = t2(0)  'which folder name?
    b2 = t2(1)  'what is the reversed order?
    
    Set propertyAccessor = reloadFolder.propertyAccessor

    propertyAccessor.SetProperty "http://schemas.microsoft.com/mapi/proptag/0x30200102", propertyAccessor.StringToBinary(b2)
    i = i + 1
Next
End Sub

Additional Notes: I did try experimenting with applying ordering data manually. I couldn't get it to work properly. All the binary converting code wasn't producing the correct values, and I ended up using HEX(). Here is an example of what I was doing:

Dim custom_order As Long
custom_order = 15

For Each arr_folder In arr
    'the array only contains a list of folder names.. we need to load the folder in outlook to process it again. The below line of code loads the main email inbox, then the subfolder from the array [different from the above code]
    Set reloadFolder = Application.Session.GetDefaultFolder(olFolderInbox).Folders(arr_folder)
    Set propertyAccessor = reloadFolder.propertyAccessor
    
    hexval = Hex(custom_order)
    
    propertyAccessor.SetProperty "http://schemas.microsoft.com/mapi/proptag/0x30200102", propertyAccessor.StringToBinary(hexval)

    custom_order = custom_order + 1
Next
End Sub