How to concatenate elements of a single-dimensional array using VBA

Question

In Outlook, I'm attempting to loop through email messages, remove any unique IDs, and combine them into one string.

Each message has numerous unique IDs that are repeated throughout the email, such as these:

ID 1111, ID 2222
ID 1111, ID 33333, ID 2222
ID 1111, ID 2222, ID 444, ID 33333
ID 2222, ID 1111

Then expected result would be (sorted):

ID 444, ID 1111, ID 2222, ID 33333

Here is my code.

Sub Scrap_IDs()

Dim olApp As Outlook.Application: Set olApp = New Outlook.Application
Dim olFolder As MAPIFolder: Set olFolder = olApp.Session.GetDefaultFolder(olFolderInbox).Folders("Folder_name")

    Dim olMail As Variant: For Each olMail In olFolder.Items
        Dim mBody As String: mBody = olMail.Body
    
        With olMail
        ' Scrap all IDs using regex
             With New RegExp
                    .Global = True
                    .Pattern = "ID[ \d]+"
            
            Dim MatchID As Object: For Each MatchID In .Execute(mBody)
                    Dim i As Long: Dim arrMatchID(): ReDim Preserve arrMatchID(i)
                    arrMatchID(i) = MatchID.Value
                    i = i + 1
                Next
            End With
            
        ' Remove duplicates from array
            Dim RemArrDups As Variant: RemArrDups = WorksheetFunction.Sort(WorksheetFunction.Unique(WorksheetFunction.Transpose(arrMatchID)))
    
        ' Concatenate array items
            Dim IDs As String: IDs = Join(RemArrDups, ", ")
    
        End With
    Next

End Sub

Run-time error 5 ("Invalid procedure call or argument") is displayed for the concatenation-related line. How come?

The values won't be unique if I use RemArrDups; it works if I use the Join function on arrMatchID.

Answer 1

Using Microsoft 365's UNIQUE and SORT in VBA

' This is a 1D array, a single row.
Dim arrMatchId(): arrMatchId = Array( _
    "ID 667", "ID 3", "ID 1111", "ID 2222", "ID 3", "ID 44", "ID 667")

' Remove duplicates and sort the array.
' Keep it a single row (don't transpose). Use 'True' as the parameters
' for the 'col' arguments of both functions.
Dim RemArrDups(): RemArrDups _
    = Application.Sort(Application.Unique(arrMatchId, True), , , True)

' Join.
Dim IDs As String: IDs = Join(RemArrDups, ", ")

Debug.Print IDs