Combining Hyperlinks

[MIchael Shef]

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

 

[BenMiller]

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?

 

[MIchael Shef]

I'd like to create one link to open all the emails

 

[lmvk99]

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.

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!

 

[MIchael Shef]

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.

 

[lmvk99]

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.

 

[MIchael Shef]

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

 

[MIchael Shef]

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

 

[lmvk99]

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:

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!

 

[MIchael Shef]

Followed the directions and when I hit F5 I get the following:

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