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!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

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:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,951
Messages
5,834,554
Members
430,295
Latest member
amdis

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