+ Reply to Thread
Results 1 to 9 of 9

Show actual e-mail address in hyperlink in Excel

  1. #1
    Jens
    Guest

    Show actual e-mail address in hyperlink in Excel

    I have spend many hours searching for an easy way of changing a columb
    where the visible text is "e-mail" and it's a hyperling to some e-mail
    addresses, that I can see if I move the curser over the cells. I have
    found some solutions concerning macros but as I'm not used to working
    with macros, this seems very difficult for me!
    Anybody have an idea?
    Maybe I need to buy a small programme, or is it free ware?
    br/Jens


  2. #2
    Ron de Bruin
    Guest

    Re: Show actual e-mail address in hyperlink in Excel

    Try this one with the e-mail links in column A
    It show you the mail address in column B

    Sub test()
    Dim hlnk As Hyperlink
    For Each hlnk In ActiveSheet.Hyperlinks
    hlnk.Parent.Offset(0, 1).Value = Right(hlnk.Address, Len(hlnk.Address) - 7)
    Next
    End Sub


    --
    Regards Ron de Bruin
    http://www.rondebruin.nl


    "Jens" <jki@tdcspace.dk> wrote in message news:1148822527.995039.88330@u72g2000cwu.googlegroups.com...
    >I have spend many hours searching for an easy way of changing a columb
    > where the visible text is "e-mail" and it's a hyperling to some e-mail
    > addresses, that I can see if I move the curser over the cells. I have
    > found some solutions concerning macros but as I'm not used to working
    > with macros, this seems very difficult for me!
    > Anybody have an idea?
    > Maybe I need to buy a small programme, or is it free ware?
    > br/Jens
    >




  3. #3
    Ron de Bruin
    Guest

    Re: Show actual e-mail address in hyperlink in Excel

    Oops

    This one look only in column A

    Sub test()
    Dim hlnk As Hyperlink
    For Each hlnk In Columns("A").Hyperlinks
    hlnk.Parent.Offset(0, 1).Value = Right(hlnk.Address, Len(hlnk.Address) - 7)
    Next
    End Sub


    --
    Regards Ron de Bruin
    http://www.rondebruin.nl


    "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message news:urmK%23wlgGHA.3628@TK2MSFTNGP04.phx.gbl...
    > Try this one with the e-mail links in column A
    > It show you the mail address in column B
    >
    > Sub test()
    > Dim hlnk As Hyperlink
    > For Each hlnk In ActiveSheet.Hyperlinks
    > hlnk.Parent.Offset(0, 1).Value = Right(hlnk.Address, Len(hlnk.Address) - 7)
    > Next
    > End Sub
    >
    >
    > --
    > Regards Ron de Bruin
    > http://www.rondebruin.nl
    >
    >
    > "Jens" <jki@tdcspace.dk> wrote in message news:1148822527.995039.88330@u72g2000cwu.googlegroups.com...
    >>I have spend many hours searching for an easy way of changing a columb
    >> where the visible text is "e-mail" and it's a hyperling to some e-mail
    >> addresses, that I can see if I move the curser over the cells. I have
    >> found some solutions concerning macros but as I'm not used to working
    >> with macros, this seems very difficult for me!
    >> Anybody have an idea?
    >> Maybe I need to buy a small programme, or is it free ware?
    >> br/Jens
    >>

    >
    >




  4. #4
    JimMay
    Guest

    Re: Show actual e-mail address in hyperlink in Excel

    Back up your file before attempting this, but

    With your sheet - the active sheet - Press Alt+F11 (the function key)
    This will open the VBE Environment window - at the menu Veiw, select
    Immediate Window, type in:

    Application.Activesheet.hyperlinks.delete

    And press the enter key.

    All your hyperlinks should disappear.

    HTH
    Jim May


    "Jens" <jki@tdcspace.dk> wrote in message
    news:1148822527.995039.88330@u72g2000cwu.googlegroups.com:

    > I have spend many hours searching for an easy way of changing a columb
    > where the visible text is "e-mail" and it's a hyperling to some e-mail
    > addresses, that I can see if I move the curser over the cells. I have
    > found some solutions concerning macros but as I'm not used to working
    > with macros, this seems very difficult for me!
    > Anybody have an idea?
    > Maybe I need to buy a small programme, or is it free ware?
    > br/Jens



  5. #5
    JimMay
    Guest

    Re: Show actual e-mail address in hyperlink in Excel

    Sorry, but JUST for Column A: enter

    Application.Activesheet.columns(1).hyperlinks.delete

    "JimMay" <jmay@cox.net> wrote in message
    news:3Rheg.33841$fG3.15567@dukeread09:

    > Back up your file before attempting this, but
    >
    > With your sheet - the active sheet - Press Alt+F11 (the function key)
    > This will open the VBE Environment window - at the menu Veiw, select
    > Immediate Window, type in:
    >
    > Application.Activesheet.hyperlinks.delete
    >
    > And press the enter key.
    >
    > All your hyperlinks should disappear.
    >
    > HTH
    > Jim May
    >
    >
    > "Jens" <jki@tdcspace.dk> wrote in message
    > news:1148822527.995039.88330@u72g2000cwu.googlegroups.com:
    >
    > > I have spend many hours searching for an easy way of changing a columb
    > > where the visible text is "e-mail" and it's a hyperling to some e-mail
    > > addresses, that I can see if I move the curser over the cells. I have
    > > found some solutions concerning macros but as I'm not used to working
    > > with macros, this seems very difficult for me!
    > > Anybody have an idea?
    > > Maybe I need to buy a small programme, or is it free ware?
    > > br/Jens



  6. #6
    Jens
    Guest

    Re: Show actual e-mail address in hyperlink in Excel

    Hi Jim,
    Yes it works, but it only removes the hyperlinks (and I actually still
    want them) - I want to see the actual addresses - now all that I see is
    the text that was there also before "E-MAIL"
    br/Jens


  7. #7
    Jens
    Guest

    Re: Show actual e-mail address in hyperlink in Excel

    Hi Ron,
    Thanks!
    I have never actually worked in VBE, but just tried my way, adn puff
    the magic was doen and I have opened all the addresses!
    Thanks again!
    br/Jens


  8. #8
    Ron de Bruin
    Guest

    Re: Show actual e-mail address in hyperlink in Excel

    Try my example Jens

    --
    Regards Ron de Bruin
    http://www.rondebruin.nl


    "Jens" <jki@tdcspace.dk> wrote in message news:1148831638.868294.294310@j33g2000cwa.googlegroups.com...
    > Hi Jim,
    > Yes it works, but it only removes the hyperlinks (and I actually still
    > want them) - I want to see the actual addresses - now all that I see is
    > the text that was there also before "E-MAIL"
    > br/Jens
    >




  9. #9
    JimMay
    Guest

    Re: Show actual e-mail address in hyperlink in Excel

    Jens;
    Obviously, I misunderstood your request.
    It would be best if you follow Ron's suggestion.
    Jim

    "Jens" <jki@tdcspace.dk> wrote in message
    news:1148831638.868294.294310@j33g2000cwa.googlegroups.com:

    > Hi Jim,
    > Yes it works, but it only removes the hyperlinks (and I actually still
    > want them) - I want to see the actual addresses - now all that I see is
    > the text that was there also before "E-MAIL"
    > br/Jens



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1