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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
 
Upvote 0
Thank you for the help! :)

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

Thanks again!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

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
Back
Top