VBA - HOW TO: SCRAPE EMAILS FROM WEBSITE - VBA
Really simple way to scrape emails from a website.
This specifically looks at "a" tags and emails embedded in Hrefs
Sub Scrape_emails()
Dim Collection As MSHTML.IHTMLElementCollection
Dim element As Object
Dim i As Integer
i = 1
URL = "Http://air-vid.com/wp/listings/"
Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")
XMLHTTP.Open "GET", URL, False
XMLHTTP.setRequestHeader "Content-type", "text/xml"
XMLHTTP.send
Set HTML = CreateObject("htmlfile")
HTML.body.innerHTML = XMLHTTP.responseText
Set Collection = HTML.getElementsByTagName("a")
For Each element In Collection
On Error Resume Next
Email = element.href
If InStr(Email, "@") Then
ThisWorkbook.Sheets(1).Cells(i, 1).Value = element.href
Email = Replace(Email, "mailto", "")
ThisWorkbook.Sheets(1).Cells(i, 2).Value = Email
i = i + 1
End If
End If
Next element
End Sub