Combining Hyperlinks

MIchael Shef

New Member
Joined
Jun 5, 2012
Messages
6
I'm using an excel sheet to keep track of list of parents & volunteer for a Cub Scout Pack.

I have them broken down into dens and each den has a hyperlink of all the email address in that specific group.

I'd like to create a hyperlink that collects all the individual den emails and combines them into one 'master' hyperlink.

When I try to cut and paste all the email addresses into one hyperlink it wouldn't allow that many addresses in one link.

Please advise.

Thanks
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
So - you want to have one link to open email messages for all the emails? Or one message with all the addresses on it?

What exactly are you trying to accomplish?
 
Upvote 0
If you have a lot of emails and they are in a list, this is a quick way of collecting them into a single cell with a multi-address hyperlink. I'm not sure if this is exactly what you're after? Just name your list of hyper linked email addresses "emails" first.

Code:
Sub CollectEmails()
Dim hypname As String
    For Each cell In Range("emails")
    hypname = Right(cell.Hyperlinks(1).Address, Len(cell.Hyperlinks(1).Address) - 7)
    If Range("b2").Value = "" Then
    Range("B2").Value = hypname
    Else
    Range("B2").Value = Range("B2").Value + "; " + hypname
    End If
    Next
    Range("b2").Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="mailto:" + Range("B2").Value
End Sub

Good luck!
 
Upvote 0
SOrry I'm not sure how to use the coding you have just sent me...I have all the emails as one of the columns in the spreadsheet. They are group by Dens.

Each of the dens has a hyperlink containing all the emails for the specific den.

What i want to do is create a single hyperlink that combines the emails contained in the den 1 hyperlink, with the den 2 hyperlink all the way through den 11 hyperlink.
 
Upvote 0
I think I am misunderstanding the structure of your sheet, you can copy the table and paste it in your reply post and I can cutomise.
 
Upvote 0
<table border="0" cellpadding="0" cellspacing="0" width="889"><colgroup><col style="mso-width-source:userset;mso-width-alt:5096;width:131pt" width="175"> <col style="mso-width-source:userset;mso-width-alt:6632;width:171pt" width="228"> <col style="mso-width-source:userset;mso-width-alt:3188;width:82pt" width="110"> <col style="mso-width-source:userset;mso-width-alt:7633;width:197pt" width="262"> <col style="mso-width-source:userset;mso-width-alt:3304;width:85pt" width="114"> </colgroup><tbody><tr style="height:12.6pt" height="17"> <td colspan="5" class="xl83" style="height:12.6pt;width:666pt" height="17" width="889">Pack 406</td> </tr> <tr style="height:13.2pt" height="18"> <td class="xl67" style="height:13.2pt" height="18">Name </td> <td class="xl67">Email </td> <td class="xl67">Phone </td> <td class="xl65">Parents</td> <td class="xl65">Cell #</td> </tr> <tr style="height:13.2pt" height="18"> <td class="xl79" colspan="2" style="height:13.2pt;mso-ignore:colspan" height="18">
</td> <td class="xl80">
</td> <td class="xl80">
</td> <td class="xl81">
</td> </tr> <tr style="height:12.6pt" height="17"> <td class="xl65" style="height:12.6pt" height="17">
</td> <td class="xl68">
</td> <td class="xl65">
</td> <td class="xl66">
</td> <td>
</td> </tr> <tr style="height:12.6pt" height="17"> <td class="xl65" style="height:12.6pt" height="17">
</td> <td class="xl68">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td>
</td> </tr> <tr style="height:12.6pt" height="17"> <td class="xl65" style="height:12.6pt" height="17">
</td> <td class="xl68">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td>
</td> </tr> <tr style="height:12.6pt" height="17"> <td rowspan="2" class="xl76" style="height:25.2pt" height="34">
</td> <td class="xl68">
</td> <td rowspan="2" class="xl76">
</td> <td rowspan="2" class="xl76">
</td> <td>
</td> </tr> <tr style="height:12.6pt" height="17"> <td class="xl68" style="height:12.6pt" height="17">
</td> <td>
</td> </tr> <tr style="height:12.6pt" height="17"> <td rowspan="2" class="xl76" style="height:25.2pt" height="34">
</td> <td class="xl68">
</td> <td class="xl70">
</td> <td class="xl65">
</td> <td>
</td> </tr> <tr style="height:12.6pt" height="17"> <td class="xl68" style="height:12.6pt" height="17">
</td> <td class="xl70">
</td> <td class="xl65">
</td> <td>
</td> </tr> <tr style="height:12.6pt" height="17"> <td rowspan="2" class="xl76" style="height:25.2pt" height="34">
</td> <td class="xl68">
</td> <td rowspan="2" class="xl76">
</td> <td class="xl65">
</td> <td>
</td> </tr> <tr style="height:12.6pt" height="17"> <td class="xl69" style="height:12.6pt" height="17">
</td> <td class="xl65">
</td> <td>
</td> </tr> <tr style="height:12.6pt" height="17"> <td rowspan="2" class="xl76" style="height:25.8pt" height="35">
</td> <td class="xl68">
</td> <td rowspan="2" class="xl76">
</td> <td class="xl65">
</td> <td>
</td> </tr> <tr style="height:13.2pt" height="18"> <td class="xl69" style="height:13.2pt" height="18">
</td> <td class="xl65">
</td> <td>
</td> </tr> <tr style="height:13.2pt" height="18"> <td class="xl71" colspan="2" style="height:13.2pt;mso-ignore:colspan" height="18">
</td> <td class="xl72">
</td> <td class="xl72">
</td> <td class="xl82">
</td> </tr> <tr style="height:12.6pt" height="17"> <td class="xl65" style="height:12.6pt" height="17">
</td> <td class="xl68">
</td> <td class="xl65">
</td> <td class="xl66">
</td> <td>
</td> </tr> <tr style="height:12.6pt" height="17"> <td class="xl65" style="height:12.6pt" height="17">
</td> <td class="xl68">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td>
</td> </tr> <tr style="height:12.6pt" height="17"> <td rowspan="2" class="xl76" style="height:25.2pt" height="34">
</td> <td class="xl68">
</td> <td rowspan="2" class="xl76">
</td> <td class="xl65">
</td> <td>
</td> </tr> <tr style="height:12.6pt" height="17"> <td class="xl68" style="height:12.6pt" height="17">
</td> <td class="xl67">
</td> <td>
</td> </tr> <tr style="height:12.6pt" height="17"> <td class="xl65" style="height:12.6pt" height="17">
</td> <td class="xl68">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td>
</td> </tr> <tr style="height:12.6pt" height="17"> <td class="xl65" style="height:12.6pt" height="17">
</td> <td class="xl68">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td>
</td> </tr> <tr style="height:12.6pt" height="17"> <td class="xl65" style="height:12.6pt" height="17">
</td> <td class="xl68">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td>
</td> </tr> <tr style="height:12.6pt" height="17"> <td class="xl65" style="height:12.6pt" height="17">
</td> <td class="xl68">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td>
</td> </tr> <tr style="height:12.6pt" height="17"> <td class="xl65" style="height:12.6pt" height="17">
</td> <td class="xl68">
</td> <td class="xl67">
</td> <td class="xl65">
</td> <td>
</td> </tr> <tr style="height:13.2pt" height="18"> <td class="xl65" style="height:13.2pt" height="18">
</td> <td class="xl68">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td>
</td> </tr> <tr style="height:13.2pt" height="18"> <td class="xl71" colspan="2" style="height:13.2pt;mso-ignore:colspan" height="18">
</td> <td class="xl72">
</td> <td colspan="2" class="xl77" style="border-right:1.0pt solid black">
</td> </tr> <tr style="height:12.6pt" height="17"> <td rowspan="2" class="xl76" style="height:25.2pt" height="34">
</td> <td class="xl73" style="border-top:none">
</td> <td rowspan="2" class="xl76">
</td> <td class="xl66" colspan="2" style="mso-ignore:colspan">
</td> </tr> <tr style="height:12.6pt" height="17"> <td class="xl74" style="height:12.6pt" height="17">
</td> <td class="xl66">
</td> <td>
</td> </tr> <tr style="height:12.6pt" height="17"> <td class="xl65" style="height:12.6pt" height="17">
</td> <td class="xl68">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td>
</td> </tr> <tr style="height:12.6pt" height="17"> <td class="xl65" style="height:12.6pt" height="17">
</td> <td class="xl68">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td>
</td> </tr> <tr style="height:12.6pt" height="17"> <td class="xl65" style="height:12.6pt" height="17">
</td> <td class="xl68">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td>
</td> </tr> <tr style="height:12.6pt" height="17"> <td class="xl65" style="height:12.6pt" height="17">
</td> <td class="xl68">
</td> <td>
</td> <td class="xl65">
</td> <td class="xl75">
</td> </tr> </tbody></table>
 
Last edited by a moderator:
Upvote 0
Where it has Den 1 in column A that it a hyperlink containing the email for the Duffy,williams,towers, aadal, ballentine,Grillo and Thomann families

Where it has Den 2 in Column A that it a hyperlink containing an email for the Sanoiemma, Rigel, Rodriquez, Kramer, Krasese, Chinsky, Della Vedova, Coletti and Robert Families.

I'd like to create 1 hyperlink that contains all of the emails found in the individual den hyperlinks

Thanks
 
Upvote 0
Okey dokey. Highlight cells B3:B32 and name the range "emails" (just in case: to name the range select your list then go to the white box to the left of the formula bar and type "emails" in it).

Then go to the Developer section and under Code select Visual Basic. In the blank space copy and paste:
Code:
Sub CollectEmails()
Dim hypname As String
    For Each cell In Range("emails")
        If cell.Value = "" Then
        Else
            hypname = Right(cell.Value, Len(cell.Value) - 7)
            If Range("b1").Value = "" Then
            Range("B1").Value = hypname
            Else
            Range("B1").Value = Range("B1").Value + "; " + hypname
            End If
        End If
    Next
    Range("b1").Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="mailto:" + Range("B1").Value
End Sub
Then put your cursor in the middle of that text somewhere and hit F5. Now your cell B1 should populate with a list of all emails in column B and it will put them all into the hyperlink for you.

Good Luck!
 
Upvote 0
Followed the directions and when I hit F5 I get the following:

Run Time Error '1004'
Application-defined or object defined error
 
Upvote 0

Forum statistics

Threads
1,211,802
Messages
6,104,058
Members
447,892
Latest member
madara uchiha

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