Changing hyperlink email address fail

Hi,

This code will take the contents of each cell and create a hyperlink in that cell to email the address in the cell. Adjust the range A1:A10 to suit your data.

Please Login or Register  to view this content.

I have a couple of addresses that have information in the cell that opens up an outlook message to a different email address. Hovering over a cell with aaaaaa@xyz.com shows the message mailto:xxxxx@abc.com.
How do I change the hyperlink to the correct email address?

Below is a VBA solution - helpful because it checks all hyperlinks:
NOTE: I am assuming that the link is correct, but that the displayed value is incorrect

In the attached file:
- FixedSheet (= bad data) is not altered by the macro
- initially identical to sheet "SomeLinks"
- during testing it is useful to be able to copy the "bad data" back to "SomeLinks"

The VBA:
- loops through all links
- checks "display text" against related "link"
- if different "display text" is replaced
- "bad data" printed to new sheet

To test:
- open the file
- hover over the links to spot erroneous "friendly name"
- run macro with {CTRL} t
- hover over cells in sheet "SomeLinks" to see if problem sorted

If it does not do what you want let me know
eg - you may not want "MailTo:" to appear at beginning of "display text", etc

The code can be reduced in length significantly by not printing "bad values" to worksheet
- I thought this might be useful during testing however

Let me know what you think

If there are very few problem links and you know which they are, then there are 2 possibilities

IF the link is by formula
=HYPERLINK("abc@gmail.com","xyz@gmail.com")
- SOLUTION = edit the formula


IF the link is embedded
- SOLUTION = follow the pictures after right-clicking on the cell

EditLink01.jpg

EditLInk002.jpg