Return hyperlink value

micko1

Board Regular
Joined
Feb 10, 2010
Messages
80
I have a workbook that has names in column "A" each one of these names has a sheet with all of their information on it. This name is hyperlinked to an email address on their page. I have emails that are auto generated and pull up the name in column "A" as the email address. Can I get the macro to return the hyperlink value not the cell value. Below is the line of code that gets the name.
Thanks in anticipation.

Mick

EmailSendTo = rngCell.Offset(0, 0).Value
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

CWatts

Well-known Member
Joined
Jan 22, 2010
Messages
701
Here's a sub with the syntax to get hyperlink value that you can modify for your needs. I put a hyperlink in A1 and the link is returned in A5. I just put my email address in and then changed the display value in A1. A5 returned "mailto:email@nowhere.com".

Code:
Sub link()
    [a5].Value = [a1].Hyperlinks(1).Address
End Sub
 

micko1

Board Regular
Joined
Feb 10, 2010
Messages
80
That works excellent. Thank you very much for your assistance.

Mick
 

micko1

Board Regular
Joined
Feb 10, 2010
Messages
80
May have been a little bit premature. Yes the code pulls the hyperlink information but when an email hyperlink is created in Excel it places "MailTo:" in front of the email address. Is there a way to stop this from happening. Sorry about being a nuisance and thanks for you help so far.

Mick
 

CWatts

Well-known Member
Joined
Jan 22, 2010
Messages
701

ADVERTISEMENT

Code:
Sub link()
    [a5].Value = Replace([a1].Hyperlinks(1).Address, "mailto:", "")
End Sub
 

micko1

Board Regular
Joined
Feb 10, 2010
Messages
80
Back again, tried heaps of different things but can't seem to get it this time.

[Original Code]
EmailSendTo = rngCell.Offset(0, 0).Value

[Changed to this]
EmailSendTo = rngCell.Offset(0, 0).Hyperlinks(1).Address
This worked but returned "MailTo:asfaf@test.com"

[This is one of my atempts]
EmailSendTo = rngCell.Offset(0, 0) = Replace([rngCell].Hyperlinks(1).Address, "mailto:", "")
This returns "FALSE" in the email address cell.

???????
Wish I knew what I was doing.

Thanks again for your time and patience.

Mick
 

CWatts

Well-known Member
Joined
Jan 22, 2010
Messages
701

ADVERTISEMENT

EmailSendTo = rngCell.Offset(0, 0) = Replace([rngCell].Hyperlinks(1).Address, "mailto:", "")

You basically set an evaluation because you entered multiple equal signs. For example, if you go into a normal excel sheet and type =5=6 it tests if one value equales the other. 5 doesn't equal 6 so therefore the cell value is false. Same with your code. The string does not equal the stripped string.

Try this.

EmailSendTo = Replace(rngCell.Hyperlinks(1).Address, "mailto:", "")

Also, if you're offsetting the cell by 0 and 0, you aren't really offsetting it so I cant see why you'd need that.
 

micko1

Board Regular
Joined
Feb 10, 2010
Messages
80
Thanks again for your help. Last code works.
Still trying to get my head around how things work. Thanks also for the tip re offset. Realy appreciate your assistance.

Mick
 

CWatts

Well-known Member
Joined
Jan 22, 2010
Messages
701
I hear you. Part of the reason I do this is to help others because I know the frustration of learning and getting stuck, but it also helps me become more familiar with the coding and in my own trial and errors I pick things up.

If someone smarter beats me to the punch, I can see what they did and learn from that too. :)

Good luck!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,993
Messages
5,526,118
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top