vba to copy email address from web page and paste in excel

ssu02193 · Jul 4, 2012

Hi and thanks in advance. I'm trying to automate a process of collecitng email addresses from an IE page and pasting it in excel 2007.

i'd like the macro to: find the email address on the web page (there's only one), copy it, and paste it into excel.

the source code is for the email is:
[FONT=&quot]<a href=”mailto:billybob@blahblahblah.com>"billybob@blahblahblah.com</a>

i've not been able to find a way to get the element's attribute or content without all the information between the quotation marks.

Any ideas?

REALLY appreciate it!

[/FONT]

ssu02193 · Jul 4, 2012

whoops, code converted to text, had to remove the "a" tags to get the code to show

href=”mailto:billybob@blahblahblah.com”>billybob@blahblahblah.com

Rick Rothstein · Jul 4, 2012

ssu02193 said:
i'd like the macro to: find the email address on the web page (there's only one), copy it, and paste it into excel.

Any chance you can post a link to a webpage that has your single email address on it so that we can test any code with try to construct?

ssu02193 · Jul 4, 2012

Thanks for getting back so quicky. Can't use the real page i'll use it with - it's an internal corporte directory that I can only log into from work, but here's a similar example: http://www.weissinc.com/contact

thanks

Rick Rothstein · Jul 4, 2012

ssu02193 said:
Thanks for getting back so quicky. Can't use the real page i'll use it with - it's an internal corporte directory that I can only log into from work, but here's a similar example: http://www.weissinc.com/contact

thanks

Put the following two procedures (a macro and a function) in a standard Module, change the URL constant (the Const statement) to your actual webpage and then run the GetEmail macro (the Email variable will contain the email address from that webpage)...

Rich (BB code):

Sub GetEmail()
  Dim IE As Object, WebText As String, Email As String
  Const URL As String = "http://www.weissinc.com/contact"
  Set IE = CreateObject("InternetExplorer.Application")
  IE.Navigate URL
  While IE.ReadyState <> 4
    DoEvents
  Wend
  WebText = IE.Document.body.innerhtml
  IE.Quit
  Set IE = Nothing
  Email = GetEmailAddress(WebText)
  '
  '  The Email variable contains the email address
  '  so do whatever you want with it here.
  '
End Sub

Function GetEmailAddress(ByVal S As String) As String
  Dim X As Long, AtSign As Long
  Dim Locale As String, Domain As String
  Locale = "[A-Za-z0-9.!#$%&'*/=?^_`{|}~+-]"
  Domain = "[A-Za-z0-9._-]"
  AtSign = InStr(S, "@")
  If AtSign = 0 Then Exit Function
  For X = AtSign To 1 Step -1
    If Not Mid(" " & S, X, 1) Like Locale Then
      S = Mid(S, X)
      If Left(S, 1) = "." Then S = Mid(S, 2)
      Exit For
    End If
  Next
  AtSign = InStr(S, "@")
  For X = AtSign + 1 To Len(S) + 1
    If Not Mid(S & " ", X, 1) Like Domain Then
      S = Left(S, X - 1)
      If Right(S, 1) = "." Then S = Left(S, Len(S) - 1)
      GetEmailAddress = S
      Exit For
    End If
  Next
End Function

ssu02193 · Jul 4, 2012

whoa! this is perfect, thanks so much! can't wait to try it out tomorrow. Stunned that it's so complicted...

as a non-programmer (clearly) i would have hoped that getAttribute(strAttributeName As String, [lFlags As Long]) would have been all i needed and that i was missing a parameter or something.

Thanks again

Rick Rothstein · Jul 4, 2012

ssu02193 said:
whoa! this is perfect, thanks so much! can't wait to try it out tomorrow. Stunned that it's so complicted...

as a non-programmer (clearly) i would have hoped that getAttribute(strAttributeName As String, [lFlags As Long]) would have been all i needed and that i was missing a parameter or something.

I guess most of the complication comes from my GetEmailAddress function. There are simpler code procedures for getting an email address from text (look for and @, back up to the former space, move forward to the next space), however these can be fooled by the appearance of an @ sign in the text that is not part of an email address. The GetEmailAddress function I posted is a "robust" email locator which should be relatively hard to fool. I won't promise it cannot be fooled, but it is built on the actual specifications for forming email addresses, so I am hoping not.

ssu02193 · Jul 5, 2012

Worked like a charm at work, thanks so much Rick! Can you direct me to a good online reference source for commands you're using here?

Really appreciate it.

Rick Rothstein · Jul 5, 2012

ssu02193 said:
Worked like a charm at work, thanks so much Rick! Can you direct me to a good online reference source for commands you're using here?

I am glad the code worked out for you. Here is a link to the InternetExplorer object which my macro made heavy use of...

http://msdn.microsoft.com/en-us/library/aa752084(v=vs.85).aspx

As for any other function, statement or method... within the VB editor window, simply click any keyword to put the text cursor in it and then press F1 to bring up the help file for that keyword. Note that the help files for XL2003 are much "friendlier" than XL2007 (and I am guessing XL2010 as well).

charchlo2008 · Jan 19, 2016

Rick Rothstein said:
Put the following two procedures (a macro and a function) in a standard Module, change the URL constant (the Const statement) to your actual webpage and then run the GetEmail macro (the Email variable will contain the email address from that webpage)...

Hi Rick,

I've been trying to adapt the code you posted to work for a column of hyperlinks I have. I have about 1,110 hyperlinks in column A and I want to get the emails from those pages and copy to column H. Any ideas or help are appreciated. One example URL is https://schools.aws.org/school?mv_arg=14491#

Thanks

vba to copy email address from web page and paste in excel

ssu02193

New Member

Excel Facts

ssu02193

New Member

Rick Rothstein

MrExcel MVP

ssu02193

New Member

Rick Rothstein

MrExcel MVP

ssu02193

New Member

Rick Rothstein

MrExcel MVP

ssu02193

New Member

Rick Rothstein

MrExcel MVP

charchlo2008

New Member

Similar threads

Forum statistics

Share this page

vba to copy email address from web page and paste in excel

New Member

Excel Facts

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock