Mailing Form from excel?

[Guest]

I have a spreadsheet which I've protected and am sending out to many
locations to complete. Once they've completed the form I would like to have a
button, link or something that they could click on in the spreadsheet and it
would automatically do the following:

1. Save their copy (just in case) and open their mail client
2. email to me the worksheet
3. populate the subject line with the filename and the words "- completed"

The master sheet I'm using has 100 locations on it so I'm using another
macro I located to automatically break this sheet out based on the names in
the columns. Example:
column 1
Hilton
Hilton
Hilton
Harrahs
Harrahs

Would produce 2 xls files named hilton.xls and harrahs.xls with 3 rows in
the first one and 2 in the 2nd. The problem i've seen is I tried to create a
button with a macro for onbuttonclick to send mail to the address. This
didn't work as the button is NOT copied over from the master file to the
others when I run the fanout macro. So I need something that will go over
from the master (hyperlink perhaps?)

Thanks,

 

[Ron de Bruin]

Hi Scott

This is a example for creating separate files
http://www.rondebruin.nl/copy5.htm

You can use something like this in your code that create the files to add a button on the sheet with
code in the click event.

Change the Thisworkbook to the reference of the workbook in your macro
And change the sheet name ?

Sub test()
Dim WS As Worksheet
Dim Btn As OLEObject
Set WS = ThisWorkbook.Worksheets("Sheet1")
With WS
Set Btn = .OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Left:=.Range("C3").Left, Top:=.Range("C3").Top, _
Width:=100, Height:=30)
End With
Btn.Object.Caption = "Click Me"
Btn.Name = "TheButton"

With ThisWorkbook.VBProject.VBComponents(WS.CodeName).CodeModule
.InsertLines .CreateEventProc("Click", Btn.Name) + 1, _
vbTab & "ThisWorkbook.SendMail ""(e-mail address removed)"", ""This is the Subject line"""
End With
End Sub