I have never used macros before. I have a spreadsheet that lists the email
addresses for a group of parties. Then I have a single cell that pulls all
the emails addresses in a particular row into a mass email hyperlink (eg):

=HYPERLINK("mailto:"&INDEX('Contacts (2)'!$BG$2:$BG$10,MATCH($F4,'Contacts
(2)'!$F$2:$F$10,0),1)&"; "&INDEX('Contacts
(2)'!$BG$2:$BG$10,MATCH($G4,'Contacts (2)'!$F$2:$F$10,0),1)&";
"&INDEX('Contacts (2)'!$BG$2:$BG$10,MATCH($K4,'Contacts
(2)'!$F$2:$F$10,0),1)&"; "&INDEX('Contacts
(2)'!$BG$2:$BG$10,MATCH($O4,'Contacts (2)'!$F$2:$F$10,0),1)&";
"&INDEX('Contacts (2)'!$BG$2:$BG$10,MATCH($S4,'Contacts
(2)'!$F$2:$F$10,0),1)&"; "&INDEX('Contacts
(2)'!$BG$2:$BG$10,MATCH($W4,'Contacts (2)'!$F$2:$F$10,0),1),"Email
Distribution List")

I would like to put a form button in place of the hyperlink so that the
spreadsheet looks more presentable, but I can't figure out how to record a
hyperlink. When I try to record it, the macro only takes me to the cell that
the hyperlink is in, it doesn't actually act on the hyperlink. Any help?

Hi Judge,
Try the following:
-Add the VB toolbar to your menus: View/Toolbars/Control toolbox
-On that menu select the button looking control (sixth icon on my bar)
and draw a button on your spreadsheet by dragging your mouse
-you should now see a button on your spreadsheet that reads
"CommandButton1".
-doubleclick on your button, you should get a code window with the
following lines:
Private Sub CommandButton1_Click()

End Sub
-Add the following two instructions to your code, just replace A40
with the cell where your hyperlink is; your function should look like
this at the end:
Private Sub CommandButton1_Click()
Range("A40").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
End Sub
-Close that window.
-On the bar you added look for the "Exit Design Mode" button (first
one on my bar). As soon as you click it, you should turn to "play"
mode.
-If you click on the button, it should action the hyperlink
-Just color white the hyperlink text on the cell so the users cannot
see it.

Hope this helps ;)

Thanks, Leonardo. I followed those instructions, changing only the cell
reference. However, when I click the button, I get the error message
"subscript out of range", and in VB the second line (starting with
Selection.Hyperlinks(1)) is highlighted. Any ideas?

" wrote:

Hi Judge,
Try the following:
-Add the VB toolbar to your menus: View/Toolbars/Control toolbox
-On that menu select the button looking control (sixth icon on my bar)
and draw a button on your spreadsheet by dragging your mouse
-you should now see a button on your spreadsheet that reads
"CommandButton1".
-doubleclick on your button, you should get a code window with the
following lines:
Private Sub CommandButton1_Click()

End Sub
-Add the following two instructions to your code, just replace A40
with the cell where your hyperlink is; your function should look like
this at the end:
Private Sub CommandButton1_Click()
Range("A40").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
End Sub
-Close that window.
-On the bar you added look for the "Exit Design Mode" button (first
one on my bar). As soon as you click it, you should turn to "play"
mode.
-If you click on the button, it should action the hyperlink
-Just color white the hyperlink text on the cell so the users cannot
see it.

Hope this helps ;)