extracting mail to in pictures

[jspinx]

Does anyone know how to take a picture that is hyperlinked with an email address and just extract the email address in the next column over? I have attached a sample.
thank you

[jspinx]

anyone have any idea?

[Leith Ross]

Hello jspinx,

This macro can be used on the worksheet like a formula or in your VBA code.

Please Login or Register  to view this content.

Worksheet Example

Please Login or Register  to view this content.

[jspinx]

Thanks, but i cant seem to get this to work. Perhaps I am not familiar enough with macros

[Leith Ross]

Hello jspinx,

I have attached a workbook with three of your original pictures. Each one has an email hyperlink. A few columns over in an adjacent cell is the UDF. It appears just like a worksheet formula. The return value is the email address.

The UDF (User Defined Function) is in Module1 of the VBA Project.

To view the UDF Maco Code
1. Open the workbook in Excel
2. Press the keys ALT+F11 to open the Visual Basic Editor

To add the name of the Shape to the UDF formula
1. Right click on the Shape
2. Left Click in the Name Box
3. This is the box to left of the Formula Bar
4. Copy the name using Ctrl+C
5. Paste the name into the UDF formula using CTRL+V

[jspinx]

thanks for the instructions.
Once I right click on the shape what do i do? your instructions say to left click on the name box and thats where i get lost.

[Leith Ross]

Hello jspinx,

Have a look at the attached picture. It shows the name box highlighted in blue after left clicking it (follow the graphic "Name Box"). Use CTRL+C to copy the name and the use CTRL+V to paste into the UDF between double quotes.

[jspinx]

im not seeing a picture attached

[jspinx]

sorry, once i refreshed the page, it showed up

[jspinx]

oh i see what your saying, well i have thousands of entries, this has to be done one by one or can this be automated?

[Leith Ross]

Hello jspinx,

It is possible to automate. If all the shapes that have email hyperlinks have similar names and are differently named from the other shapes then it will be easier. You should post a sample (better that the first one) workbook. The layout is important, so be sure what you post accurately represents what you will be using.

[jspinx]

ok thank you, i will post what you suggested