How to change hyperlinks to address

Microsoft Microsoft office excel 2007 ac...
June 8, 2010 at 19:08:52
Specs: Windows 7
Here is the thing. This is the best way to give u an example.:
http://aaahcs.com/excel/test4paf.xlsx
In the (D) column you see it says "click to email". I dont want it to say that, i want it to say tha actual email address that it is supposed to say. What ive been doing is right click D1, left click edit hyperlink and then copy email address to the TEXT TO DISPLAY area. then click. But this is getting strenuous. Because i neeed to do 4000 of those.
I saw a cde that looked like it might work on here, but i couldnt edit it to make it work. So can someone pls help me.
Sub MakeEmails()
For Each e_address In Range("A1:A45")
ActiveSheet.Hyperlinks.Add Anchor:=e_address, Address:="mailto:" & e_address.Value, TextToDisplay:=e_address.Value
Next
End Sub


See More: How to change hyperlinks to address

Report •


#1
June 8, 2010 at 19:12:04
matter fact i just noticed, i need it to go backwards. I need the TEXT to DISPLAY to display whats in the Email Address . Preferably without the mailto: in it.im trying to reverseit but dont know how.

Report •

#2
June 9, 2010 at 07:19:03
Hi,

Try this macro.
It assumes that there is only the person's name in the cells in column D, and that these names are sufficient for the e-mail address line.

Sub MakeEmails()
Dim e_address As Range
For Each e_address In Range("D1:D4000")
ActiveSheet.Hyperlinks.Add Anchor:=e_address, _
        Address:="mailto:" & e_address.Text, _
        TextToDisplay:=e_address.Text
Next
End Sub

Change D1 and D4000 to match the range of cells to change.

Regards


Report •

#3
June 9, 2010 at 09:05:58
thats the same thing i have above. Im not tryin to m,ake the email says whats in text. Im trying to make the text say whats in email

Report •

Related Solutions

#4
June 9, 2010 at 09:19:26
If it's any help,
in the Edit Hyperlink window,
all you have to do is Delete the string Click to E-MailĀ 
in the Text to Display box
and the email address will be the default display.

Sorry, I do not know how to do this in VBA

MIKE

http://www.skeptic.com/


Report •

#5
June 9, 2010 at 09:39:20

Report •

#6
June 9, 2010 at 11:09:40
Hi,

I was able to look at the workbook you posted.

The following macro will take the existing information in column D and display the email address and the (non-visible) address will start with 'mailto:'

I noticed that cell D1 had mailto twice. Having corrected that the macro appears to work OK.
For example cell D3 will display:
crabb@gmail.com
and in the hyperlink, the email address will be:
mailto:crabb@gmail.com

Here it is:

Sub MakeEmails()
Dim e_address As Range
Dim h As Hyperlink
For Each e_address In Range("D1:D4000)
    For Each h In Worksheets("Sheet1").Hyperlinks
        If h.Range.Address = e_address.Address Then
            ActiveSheet.Hyperlinks.Add Anchor:=e_address, _
            Address:=h.Address, _
            TextToDisplay:=Right(h.Address, Len(h.Address) - 7)
        End If
    Next
Next
End Sub

I am sure that you are testing options on backups of your data, as changes made by macros cannot be undone with the Undo function.

Regards


Report •

#7
June 9, 2010 at 13:02:01
Thank You Humar

Report •

#8
June 9, 2010 at 21:44:05
You're welcome

Regards

Humar


Report •


Ask Question