[SOLVED] Pulling e-mail adress from website with VBA

Pulling e-mail adress from website with VBA

Given a list of links where each link leads to a homepage where an e-mail adress is listed, how can we utilize VBA to pull these e-mail addresses into Excel?

Solution

So I found an answer to my questions, unfortunately it is very specific due to my homepage being login protected so that you cannot try it out. I will post anyway and hope it can help somehow.

Sub emailextract()

    Set appIE = CreateObject("internetexplorer.application")
    Dim ele As Object
    Dim i As Integer
    Dim y As Integer


    For i = 1 To 150

    appIE.Visible = False
    #web-links are in Row A sheet "links"
    appIE.navigate Sheets("links").Range("A" & i)

    Do While appIE.Busy = True Or appIE.readyState <> 4: DoEvents: Loop


    y = i
    For Each ele In appIE.document.getElementsByTagName("dd")
        '
        Debug.Print ele.textContent

        Sheets("adressen").Range("A" & y).Value = ele.textContent
        y = y + 1


    Next

    Next i

    ActiveWorkbook.Save

End Sub