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

Feb 16, 2023 in Others by Kithuzzz
• 38,010 points
354 views

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, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

How to make an error flagging array in VBA and translate all array elements as a string message?

In my opinion, using an array in ...READ MORE

answered Mar 17, 2023 in Others by Kithuzzz
• 38,010 points
336 views
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,670 points
753 views
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, 2023 in Others by narikkadan
• 63,420 points
417 views
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, 2023 in Others by narikkadan
• 63,420 points
350 views
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, 2023 in Others by narikkadan
• 63,420 points
500 views
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, 2023 in Others by narikkadan
• 63,420 points
1,361 views
0 votes
1 answer

Loops through Check Box in VBA

Please attempt the next option. Assuming you ...READ MORE

answered Apr 1, 2023 in Others by narikkadan
• 63,420 points
554 views
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
• 63,420 points
1,858 views
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, 2023 in Others by narikkadan
• 63,420 points
2,102 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP