Extract email address from string (1 Viewer)

cpampas · Jul 25, 2020

Hi,
I am trying to extract the email address from webpage source code, that I previously passed into a string variable (webStr) :
I am only pasting part of the string wich is way too long :

inc38_columns1_col_bg_2" rel="inc38_container1"></div><div class="wp_column inc38_columns1_col_1"><div class="wp_bg inc38_columns1_col_bg_1" rel="inc38_container4"></div><div
class="wp_column inc38_columns1_col_0"><div class="wp_bg inc38_columns1_col_bg_0" rel="inc38_container3"></div><div id="inc38_container3" class="wp_col_content inc38_columns1_col_box_0"><div style="height:0px;width:0px;"> </div><div id="inc38_title2" class="inc38_title2_wp_outer"><h4 class="title">Contacte-Nos</h4><div class="clear_div" style="clear:both;height:0px;"> </div></div><div id="inc38_htmltext2" class="inc38_htmltext2_wp_outer"><span>Rua D. Francisco Manuel Melo 13,3º, 1070-085 Lisboa LISBOA, Portugal</span></div><div id="inc38_htmltext6" class="inc38_htmltext6_wp_outer"><a href="tel:213863076" aria-label="Link para App Mobile 213863076">Telefone: 213863076</a><div class="clear_div" style="clear:both;height:0px;"> </div></div><div id="inc38_htmltext7" class="inc38_htmltext7_wp_outer">E-mail: noemiacoelho-5087L@adv.oa.pt<div class="clear_div" style="clear:both;height:0px;"> </div></div><div class="clear_div" style="clear:both;height:0px;"> </div></div><div id="inc38_container4" class="wp_col_conten
t inc38_columns1_col_box_1"><div style="height:0px;width:0px;"> </div><div id="inc38_title1" class="inc38_title1_wp_outer"><h4 class="title">mapa</h4><div class="clear_div" style="clear:both;height:0px;"> </div></div><div id="inc38_map1" class="inc38_map1_wp_outer"><div id="inc38_mmap1_mapi" style="width: 100%; height: 100%; display:none;" aria-disabled="true"><span>Map goes here</span></div><div class="clear_div" style="clear:both;height:0px;"> </div></div><div class="clear_div" style="clear:both;height:0px;"> </div></div><div id="inc38_container1" class="wp_col_content inc38_columns1_col_box_2"><div style="height:0px;width:0px

I found the position of the character "@" with:
Pos_@ = InStr(1, WebStr, "@", vbTextCompare)

the following does not get part of the email, I wonder why? and how should I set the first character and lenght of the mid function ?

?mid(WebStr,First_@-25,50)
/ld+json">//<![CDATA[{"@context":"https://schema

Gasman · Jul 25, 2020

I would find Email: first
Then using that as a starting point find <div>

Then use the difference for the length, plus or minus 1 as required.?

arnelgp · Jul 25, 2020

s="E-mail:"
pos=instr(1,webStr, s)
if pos>0 then
sEmail=trim(mid(webstr, pos+len(s)))
pos=instr(sEmail, "<")
if pos>0 then
sEmail=Left(sEmail, pos-1)
end if
end if

arnelgp · Jul 25, 2020

or you can use a Function:

strEmail=ExtractEmail(WebStr)

Code:

'https://excelfox.com/forum/showthread.php/1092-VBA-To-Extract-Email-Address-From-Text
Function ExtractEmail(strInputText As String) As String
    Dim regEx As Object
    Dim varResults As Object
    Dim varEach
    Dim lng As Long
    Set regEx = CreateObject("vbscript.RegExp")
    regEx.Pattern = "(?:[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*|""(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*"")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])"
    regEx.IgnoreCase = True 'True to ignore case
    regEx.Global = True 'True matches all occurances, False matches the first occurance
    If regEx.Test(strInputText) Then
        Set varResults = regEx.Execute(strInputText)
        For lng = 1 To varResults.count
            ExtractEmail = ExtractEmail & varResults.Item(lng - 1).value & "|||"
        Next
        ExtractEmail = Left(ExtractEmail, Len(ExtractEmail) - Len("|||"))
        ExtractEmail = Join(Split(ExtractEmail, "|||"), ", ")
    End If
    
End Function

theDBguy · Jul 25, 2020

I was going to offer the same thing, but Arnel beat me to it. Just in case though, here it is.