Copying/pasting a link in a hyperlink

masonislane

New Member
Joined
Jun 23, 2008
Messages
6
So I have a list of names in excel that are currently linked to e-mail addresses (i.e. mailto:...) and what I want to do is take the e-mail addresses in the hyperlink and paste them in a new column (i'm essentially making a contact information list). Is there any way to do this besides clicking on each link, waiting for the outlook "new message" window to open, copying the e-mail address that comes up, then pasting in the appropriate cell? There are hundreds of names on the list. Any way to do it all at once?

Thanks so much!
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Case_Germany

Active Member
Joined
May 13, 2008
Messages
408
Hi,

the following code functions, if your e-mail links are located in column A. The code writes your e-mail links in column B:

Code:
Sub EX_Hyps_Next_Column()
    Dim strHyp As String
    Dim lngRow As Long
    On Error GoTo Fin
    Application.ScreenUpdating = False
    For lngRow = 1 To Range("A" & Rows.Count).End(xlUp).Row
        If Cells(lngRow, 1).Hyperlinks.Count = 1 Then
            With Cells(lngRow, 1)
                strHyp = Application.WorksheetFunction.Substitute(.Hyperlinks(1).Address, "mailto:", "")
                .Offset(0, 1).Formula = strHyp
            End With
        End If
    Next lngRow
Fin:
    Application.ScreenUpdating = True
End Sub
Case_Germany
 

masonislane

New Member
Joined
Jun 23, 2008
Messages
6
Thank you for the help! :)

I'm not great with excel, though. How do i go about activating/executing this code?

Thanks again!
 

Case_Germany

Active Member
Joined
May 13, 2008
Messages
408
Hi again,


  • Press Alt+F11 to go to the Visual Basic Editor.
  • Choose menu - Insert / Module to add the new module.
  • Double click on Module1 in project explorer window, the blank code area of the Module1 is appeared at the top right side. Here insert the code.

Back in Excel choose Alt+F8 - select Macro and execute.

Reminder - functions only with the conditions specified above in my first post.

Case_Germany
 

masonislane

New Member
Joined
Jun 23, 2008
Messages
6
Thank you! Thank you! Thank you!

Works great, you saved me a lot of work! Thanks so much! :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,651
Members
414,399
Latest member
Ninjee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top