Regex for get a specific word in an email vba

Question

I have a code for get a string and try cut this in little parts for me get this information, my code:

 sText = olItem.Body

     Set Reg1 = CreateObject("VBScript.RegExp")
    ' \s* = invisible spaces
    ' \d* = match digits
    ' \w* = match alphanumeric
    'Reg1.Pattern = "\s"
    With Reg1
        '.Pattern = "(\s*(\w*)\s*(\w*)\s*(\w*)\s*([\d-\.]*))"
        .Pattern = "\S"
        .Global = True
        .MultiLine = True
        .IgnoreCase = True
    End With
    'Debug.Print Reg1.test(sText)
    'MsgBox (Reg1.test(sText))
    If Reg1.test(sText) Then

' each "(\w*)" and the "(\d)" are assigned a vText variable
        Set M1 = Reg1.Execute(sText)

        For Each M In M1
           vText = Trim(M.SubMatches(1))
           'vText2 = Trim(M.SubMatches(2))
           MsgBox (vText)
           'vText3 = Trim(M.SubMatches(3))
           'vText4 = Trim(M.SubMatches(4))
           'vText5 = Trim(M.SubMatches(5))
           'MsgBox (vText3)
        Next
    End If

In Outlook VBA

And I receive this type of E-mail:

Fisrt Name  Guilherme
Last Name   Souza
City        Piracicaba

I want get these information and yours respective values for exemple:

Dim FName As string
FName = Guilherme

For get it I'm trying this type of Regex Pattern:

.Pattern = "First Name"

But it's not working, because I can have any name and several other things, but my vba is wrong and I can't get just one or multiple.

I have other typoe of email and i want get too, following the answer of Peh i tryed it:

(Modelo) +(\S+)|(plaqueta) +(\S+)|(Serial Number) +(\S+)|(nome do departamento) +(\S+)|(Destino (estoque ou nome do departamento)) +(\S+)|(Nome do Usuário) +(\S+)|(MAC Address) +(\S+)|(IP do Equipamento) +(\S+)|(MAC Address) +(\S+)|(Número do chamado (se houver)) +(\S+)

For this String:

Segue movimentação de ativos.

Itens   Equipamento/Computador Retirado Equipamento/Computador  Entregue
Modelo      Lenovo 5020
Número de Ativo (plaqueta)      302235
Serial Number       H000000
Origem (estoque ou nome do departamento)        Estoque
Destino (estoque ou nome do departamento)       Office
Nome do Usuário     Guilherme Souza
MAC Address     0000000000
IP do Equipamento       -
Número do chamado (se houver)   -

And i want get the valuesfor exemple for the IP i want get IP = - and MAC = 00000000

score 1 · Accepted Answer · answered May 15 '20 at 12:15

1

Your pattern .Pattern = "\S" is not good for what you are looking for:

If the data in your email is …

First Name  Guilherme
Last Name   Souza
City        Piracicaba

… then better use a pattern like:

(First Name) +(\S+)|(Last Name) +(\S+)|(City) +(\S+)

See: https://regex101.com/r/lHnQoo/1

So you would get the following matches with submatches:

For further information about RexEx in VBA check out:
How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

answered May 15 '20 at 12:15

Pᴇʜ

56,719
10
49
73

Thanks a lot, but a have other email and not is working with this sintax – Guiherme Souza May 15 '20 at 12:22
is a different email * but im trying do the same way – Guiherme Souza May 15 '20 at 12:23
1

@DbaAlone Well, then the data in that other mail is not that same structure and does not match the criteria. Note that we cannot magically know what is different. Either you can [edit] your original question and show a proper example of what **exactly** your issue is or we cannot help you. – Pᴇʜ May 15 '20 at 12:32
Yes i edited my ask, if u can help me ;) – Guiherme Souza May 15 '20 at 12:33
@DbaAlone Check out this pattern: https://regex101.com/r/aq2WY0/1 • Note that if you have special characters like parenthesis `(plaqueta)` in what you want to lookup you need to escape them like `\(plaqueta\)`. – Pᴇʜ May 15 '20 at 12:39
For me get these informatio, it is? For Each M In M1 vText = Trim(M.Submatches.Item(5)) MsgBox (vText) Next – Guiherme Souza May 15 '20 at 13:11

Regex for get a specific word in an email vba

1 Answers1