Getting email address from hyperlink 1

[AOLBoy]

I have an excel worksheet that contains cells which have an envelope icon. In excel if I move my mouse over the icon the mail address is displayed. Is there any way that I can obtain this information programmatically using VBA?

Any solutions would be gratefully appreciated and save me a lot of time. Thanks

 

[combo]

You need to access its hyperlink, depending on you have a cell or shape:

Dim h As Hyperlink
Set h = ActiveSheet.Range("A1").Hyperlinks(1)
MsgBox Right(h.Address, Len(h.Address) - 7)
Set h = ActiveSheet.Shapes(1).Hyperlink
MsgBox Right(h.Address, Len(h.Address) - 7)

combo

 

[AOLBoy]

Thanks for this combo. I am having a bit of trouble.

Firstly, as the hyperlinks I am trying to unravel are small envelopes that I copied and pasted from an internet site - I assume these would be classed as 'shapes'.

In your example you coded

Set h = ActiveSheet.Shapes(1).Hyperlink

Is it possible for me to replace 'ActiveSheet' with 'ActiveCell.Offset(x,y)' as this is the way I am looping through the worksheet cells?

Regards

 

[combo]

It is rather hard to get directly a shape in a given range so rather loop all shapes (and test for hyperlink) or hyperlinks (and test for shape). If you need to limit validation to a given area, check TopLeftCell or bottomRightCell for the shape returned. An example, without the latest validation:

Dim hs As Hyperlinks, h As Hyperlink, sFullAddress As String
Set hs = ActiveSheet.Hyperlinks
If hs.Count > 0 Then
    For Each h In hs
        If TypeName(h.Parent) = "Shape" And (h.Address Like "mailto:*") Then
            sFullAddress = h.Address
            MsgBox Right(sFullAddress, Len(sFullAddress) - 7)
        End If
    Next h
End If

combo

 

[AOLBoy]

So to get information from the shape I need to read through the shapes. Whilst doing this is there anyway to determine the cell location of the shape on the worksheet? I want to take the email address from the shape/hyperlink and write into the cell to the left of where the shape is located.

 

[combo]

See TopLeftCell or BottomRightCell properties.

combo