CODEWITHSUNDEEP
excelvba
Jishan
Jishan

Reputation: 1684

Store multiple email addresses from single line to a list/collection

So I am downloading a storing a JSON file in my temp folder and reading it as such (the output is a single line of string):

Dim strFileContent As String
Dim iFile As Integer: iFile = FreeFile
Open strFilePath For Input As #iFile
strFileContent = Input(LOF(iFile), iFile)
Close #iFile

This file is of the following structure:

JSON struct

Now the emails are very standard format: [email protected]. Instead of going to a full JSON parsing library, is there a way to extract just the emails to a list from the string strFileContent?

Here's a sample text of the JSON on how the email looks, just in case: "work_email":{"display_name":"Work Email","data":"[email protected]"},

I was looking at https://github.com/VBA-tools/VBA-JSON, but I was hoping for a simpler RegEx solution (instead of a JSON parser) because if the API gives me a XML file tomorrow, my code will be obsolete.

Upvotes: 0

Views: 526

Answers (1)

Ryan Wildry
Ryan Wildry

Reputation: 5677

I'd recommend going the JSON parser route, it'll be much more reliable. However, if you want a regex solution, maybe this will work.

Public Function ExtractEmails(ByVal InputString, Optional pattern As String = "\"":""\w+@\w+\.\w+") As Object
    With CreateObject("vbscript.regexp")
        .Global = True
        .MultiLine = True
        .pattern = pattern
        Set ExtractEmails = .Execute(InputString)
    End With
End Function

Public Sub Example()
    Dim someString As String
    Dim emails     As Object
    Dim email      As Variant

    someString = "klasdkjasdkjha [email protected] ""data"":""[email protected]"" ""data"":""[email protected]"" ""data"":""[email protected]"" asdkjhaksjdhaksjhd"
    Set emails = ExtractEmails(someString)

    For Each email In emails
        Debug.Print Replace(Replace(email, """", vbNullString), ":", vbNullString)
    Next

End Sub

Returns ([email protected] doesn't match the pattern, it should precede ":"):

[email protected]
[email protected]
[email protected]

Upvotes: 1

Related Questions