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

0 votes

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
            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

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.

Feb 16 in Others by Kithuzzz
• 35,300 points

1 answer to this question.

0 votes

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
answered Feb 16 by narikkadan
• 60,820 points

Related Questions In Others

0 votes
1 answer
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
0 votes
1 answer

VBA How to extract the date and time of arrival of a answered email

Use MailItem.ReceivedTime property. I hope this helps you ...READ MORE

answered Jan 9 in Others by narikkadan
• 60,820 points
0 votes
1 answer

Copy the respective values from another workbook depend on specific conditions

Try this: Sub Get_Respective_Values_Of_Last_Closing_Date() Dim wb1 ...READ MORE

answered Feb 6 in Others by narikkadan
• 60,820 points
0 votes
1 answer

VBA How do I replace the range with an array in SUMIF

You can't, in my opinion. When you ...READ MORE

answered Feb 7 in Others by narikkadan
• 60,820 points
0 votes
1 answer

Excel VBA compare values on multiple rows and execute additional code

I would use a Dictionary & Collection ...READ MORE

answered Feb 7 in Others by narikkadan
• 60,820 points
0 votes
1 answer

Retrieve Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to insert a new row in the specified cell only, of Excel sheet using c#?

I have this worksheet with a matrix ...READ MORE

answered Nov 24, 2022 in Others by narikkadan
• 60,820 points
0 votes
1 answer

How to increment the Range of a For Each loop - Excel VBA

Your formula seems to sum 1 single ...READ MORE

answered Jan 7 in Others by narikkadan
• 60,820 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP