Excel VBA Getting email Hyperlinks from sahpes into cells

[Guest]

Each month I web access a customer list and put in an Excel workbook. Each
customer cell - Row 3 of the sheet - contains up to 3 shapes - one of which
can contain a hyperlink containing an email address. I have tried all I can
think of to try to grab the email address from every shape that contains one
and place it in the cell to its right. So far totally unsuccessful.

I have recorded many macros, looked at all parts of the code in Help and
looked at all relaated properties and methods???

Any ideas??

Thanks,

 

[Jim Cone]

Gus,

Here is my idea...
'--------------------------------------
Sub GetShapeHyperlinkAddress()
'Loops thru all shapes on active sheet.
'Adds 4 properties of each shape to columns E:H on active sheet.
'Jim Cone December 24, 2004

Dim shpCustomer As Excel.Shape
Dim arrString(1 To 4) As String
Dim lngNum As Long

arrString(1) = "Name is: "
arrString(2) = "Address is: "
arrString(3) = "Sub Address is: "
arrString(4) = "Location is: "
Range(Cells(1, 5), Cells(1, 8)).Value = arrString
Range(Cells(1, 5), Cells(1, 8)).Font.Bold = True

For lngNum = 1 To ActiveSheet.Shapes.Count
Set shpCustomer = ActiveSheet.Shapes(lngNum)
arrString(1) = shpCustomer.Name
arrString(2) = shpCustomer.Hyperlink.Address
arrString(3) = shpCustomer.Hyperlink.SubAddress
arrString(4) = shpCustomer.TopLeftCell.Address(False, False)
Range(Cells(lngNum + 1, 5), Cells(lngNum + 1, 8)).Value = arrString
Next 'lngNum

Columns("E:H").AutoFit
Set shpCustomer = Nothing
End Sub
'--------------------------------------

Regards,
Jim Cone
San Francisco, USA

 

[Guest]

Thanks it worked like a charm! I couldn't figure out to get back to the
newsgroup to read your answer after getting notified that their was a
response until now - Microsoft certainly doesn't make it easy ;-)
Thanks again
Gus