Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Vancouver, Washington, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automate Email Address From Hyperlink Formatted Ce

    Hi Everyone,

    It's been some time since I've been here, and I've missed you all! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I need to understand how to automate manual steps I am now taking in Excel 2007.

    Here's my scenario:
    <UL><LI>I receive a workbook that has at least 100 rows of data. Sometimes, many more rows.
    <LI>In each of those rows one cell contains a hyperlink formatted cell that displays a client's name. For example: Abraham Lincoln.
    <LI>The hyperlink is Abe's email address, abe_lincoln@logcabin.com
    <LI>Currently, I am clicking each hyperlink, open a new email in Outlook, then I copy the email address, and paste the email address back into the workbook in a different cell, not formatted as a hyperlink.[/list]I need a way to automate this process, so that I don't have to open 100+ new emails and do all these manual copy and paste steps.

    Any suggestions?

    Cheers, and Thanks,
    Rich

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automate Email Address From Hyperlink Formatted Ce

    Welcome back!

    You could run this macro, after adjusting the constants at the beginning. SourceCol is the column containing the e-mail hyperlinks, and TargetCol is the column where you want the e-mail addresses.

    Sub ExtractAddresses()
    Const SourceCol = "A"
    Const TargetCol = "B"
    Dim r As Long
    Dim m As Long
    m = Range(SourceCol & Rows.Count).End(xlUp).Row
    For r = 1 To m
    Range(TargetCol & r) = Mid(Range(SourceCol & r).Hyperlinks(1).Address, 8)
    Next r
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Vancouver, Washington, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automate Email Address From Hyperlink Formatte

    Hi Hans,

    Cheers, and thanks for your quick reply.

    I know I can create this macro in Excel 2003.

    Can I create a macro like this in Excel 2007?

    If so, how would I create the macro and run it?

    Cheers,
    Rich

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automate Email Address From Hyperlink Formatte

    The macro should work in Excel 2007 too - the object model hasn't changed all that much, at least as far as the basics are concerned.

    I don't have Office 2007, but I assume that macros, the Visual Basic Editor etc. can be reached from the Developer tab of the Ribbon. *goes off*

    *returns* Yes, see Create or delete a macro.

  5. #5
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Vancouver, Washington, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automate Email Address From Hyperlink Formatte

    Hi Hans,

    Yes, after I asked you, I went to Office Online Excel 2007 Help & How-to and found out that you can do this in Excel 2007!

    Like you, I haven't yet installed Excel 2007. Eventually, I'll be forced to that, but until then.......!!!

    My client was thrilled with your solution, and will save them tons of time!

    Thanks and Cheers,
    Rich

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •