Find an email address in a string

[katib]

I've go a string holding a company name, address, email address, phone and fax number.

e.g. string="Quadrant Subscription Services Rockwood House Perrymount Road Haywards Heath E-mail: [email protected] Telephone: +44 (0) 1444 445517 Fax: +44 (0) 1444 445526"


How can I find the email address and put a <B> tag in front of it and a </B> tag at the end?

like this: <B>[email protected]</B>

Thanks

Kati

[Mc Brain]

Try:

Dim s As String
    Dim iEmail As Integer
    Dim iEndEmail As Integer
    
    s = "Quadrant Subscription Services Rockwood House Perrymount Road Haywards Heath E-mail: " & _
      "[email protected] Telephone: +44 (0) 1444 445517 Fax: +44 (0) 1444 445526"
    iEmail = InStr(1, s, "E-mail:", vbTextCompare)
    If iEmail Then
        iEndEmail = InStr(iEmail + 8, s, " ")
        If iEndEmail > iEmail Then
            s = Left$(s, iEmail + 7) & "<B>" & Mid$(s, iEmail + 8, iEndEmail - iEmail - 8) & _
            "</B>" & Mid$(s, iEndEmail)
        End If
    End If
    
    Debug.Print s

[katib]

Thanks for you reply.
The problem is that I can't rely on the string containing "E-mail:", so I will have to find the "@" symbol somehow and then find the beginning and the end of that string.

Any ideas?

Thanks

Kati

[Mc Brain]

Thanks for you reply.
The problem is that I can't rely on the string containing "E-mail:", so I will have to find the "@" symbol somehow and then find the beginning and the end of that string.

Any ideas?

Thanks

Kati

Try...

Dim s As String
    Dim iAt As Integer
    Dim iEndEmail As Integer
    Dim iStartEmail As Integer
    
    s = "Quadrant Subscription Services Rockwood House Perrymount Road Haywards Heath E-mail: " & _
      "[email protected] Telephone: +44 (0) 1444 445517 Fax: +44 (0) 1444 445526"
      
    iAt = InStr(1, s, "@")
    If iAt Then
        iStartEmail = InStrRev(Left$(s, iAt), " ")
        If iStartEmail Then
            iEndEmail = InStr(iAt, s, " ")
            
            If iEndEmail > iStartEmail Then
                s = Left$(s, iStartEmail) & "<B>" & Mid$(s, iStartEmail + 1, _
                  iEndEmail - iStartEmail - 1) & "</B>" & Mid$(s, iEndEmail)
            End If
        End If
    End If
    
    Debug.Print s

[katib]

Thanks a lot! That worked well.

Kati

[Mc Brain]

Thanks a lot! That worked well.

Kati

You might want to add "[Resolved]" in the post's subject and/or rate the post.