Return hyperlink value

[micko1]

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

 

[CWatts]

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".

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

 

[micko1]

That works excellent. Thank you very much for your assistance.

Mick

 

[micko1]

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]

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

 

[micko1]

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]

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]

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]

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!