Automate Email Extraction from Website Using Excel VBA

Automating email extraction from a website using Excel VBA can be interesting and useful under a variety of situations. One of the easier ways to extract emails automatically from a website is to use the fact that an email address can be a link like:

<a href=”mailto:[email protected]”>Contact</a>

Our strategy would now be to capture the above and using a text function in Excel extract the complete text or string containing the email address. Given below is the complete Excel VBA code to automate the process of email extraction from a web page. You will notice that two lines of code have been highlighted. These two lines of code have changed from the last training video that we used to scrape links from a website:

Sub scrapeHyperlinksWebsite()
Dim ie As InternetExplorer
Dim html As HTMLDocument
Dim Link As Object
Dim ElementCol As Object
Dim erow As Long
Application.ScreenUpdating = False
Set ie = New InternetExplorer
ie.Visible = False
‘ie.navigate “”
‘ie.navigate “”
ie.navigate “”

Do While ie.readyState <> READYSTATE_COMPLETE
Application.StatusBar = “Loading website…”

Set html = ie.document
‘Range(“A2”) = html.DocumentElement.innerHTML
Set ElementCol = html.getElementsByTagName(“a”)
‘Set ElementCol = html.getElementsByTagName(“mailto:”)

For Each Link In ElementCol
If InStr(Link, “mailto:”) Then
erow = Worksheets(“Sheet1”).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Value = Link
Cells(erow, 1) = Right(Link, Len(Link) – InStr(Link, “:”))
Cells(erow, 1).Columns.AutoFit
End If

Set ie = Nothing
Application.StatusBar = “”
Application.ScreenUpdating = True

End Sub

Watch the Excel training video:

You can view this training video on YouTube.

Further Reading:
Extracting An Email Address From Text

Download a sample file by clicking on the Excel icon: