Create a form button for email

[Judge Platt]

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?

 

[leonardo.paez]

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

 

[Judge Platt]

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?