Two parts:
First, create the function (which is a subroutine) in Excel by doing the following:
1. Press ALT-F11 to enter the Visual Basic Editor (VBE).
2. In the VBE, choose "Module" from the "Insert" menu.
3. Create a blank subroutine by typing the following, substituting with the name you'd like (something descriptive would be good; no spaces or illegal characters):
Sub ()
End Sub
4. Back in Excel, go to the View menu, select Toolbars, and then Forms. Click onthe button icon that appears on that toolbar, then click on the spreadsheet. The button should appear asking what macro it should be attached to. Locate your new subroutine in the list, click it, and hit OK.
Second, build in the code to pull the name by doing the following:
1. In the VBE, write the code as follows:
Sub ()
' Defines a variable for the workbook name
dim strWBookName as string
' Sets the variable for the workbook name to the value in the active cell
strWBookName = activecell.value
' If the workbook name is blank, pop up a message and exit the subroutine
if strWBookName = "" then
msgbox("No name in cell.")
exit sub
end if
' Save the workbook with a name stored in the variable, appending ".xls" on the end
activeworkbook.saveas filename:=strWBookName & ".xls"
' Send the workbook to someone
ActiveWorkbook.SendMail recipients:="thisperson@thiscompany.com", subject:="My Subject"
End Sub
That should get you going - if you hit problems or want to modify it and aren't sure how let me know and I'll be more than happy to help.
