Member Panel

I'm creating a "Stock List" with excel 2000 and wondered if it's possible to create a button on the bottom of the page which I can set to email the workbook to an email address predetermined by me. If it's possible how's it done?. I'd also like to know if I can create a "Save as" button (for a different project) which will save a workbook naming it as the text of a specified cell?

Hey ian,

Yes, pritty much anything is possible in Excel, as long as you can write in VB!! There are example scripts and example Excel documents you can download which will help you, such as: Example Code for sending mail from Excel

^ If I had time to write this one from scratch for you I would, but these kind of mailers can become quite complex unless you are mailing direct from a mail server, or if you have a relay mail server that doesn't require authentification. The example script available here looks quite good, but have not tested it personally.

As for the "Save As" button, this one is quite unique, so you probably won't find any scripts on the net. However, it's quite a simple request, so I'll take you through creating this one from scratch...

- First of all make sure that the Forms tools bar is ticked in Excel, by clicking on View > Toolbars
- Use the Forms toolbar to create a button on the desired Excel form, and an "Assign Macro" box will appear.. give it an appropriate name and then click on New.
- Copy and Paste the following code to where the cursor stats flashing in the current module window (between "Sub.." and "End Sub").

Range("C6").Select
ActiveWorkbook.SaveAs Filename:= ActiveCell.FormulaR1C1, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Range("A1").Select

- Where I have highlighted C6 in red, please change this to the cell where the full path and file you would like the document to be saved to.
- Finally, close Microsoft VB, and test.

Thanks Madmonkey, I'll post back with my trial results. I gotta Learn VB if it's going to make using excel so much easier. I've got to also find a way of clearing the contents of a group of cells without having to do it cell by cell using the delete button. I'll get there eventually

Hey Ian, you can also do that within a Macro. If it's something you do on a regular basis, just go to Tools, Macro and Record New Macro. Perform the task manually and then press the stop button. It will create the VB for you in the background, and then all you have to do is Tools, Macro, Macros... you can even assign shortcut keys to your Macro.

Got the macro written for clearing the specified cell contents.
Got the Mailer button to work..so far so good, but struggling with the saving,I may not have explained my requirements that clearly so I'll try it again.....
I want to be able to get the Save As dialogue box to open,(so I can manually select the files' destination) but have the file named with the contents of a specified cell automatically.
I hope that's a little clearer as to what I'm trying to achieve

Oh, in that case, use this code instead:

Range("C6").Select
Dim sFolderName As String, fDialog As FileDialog, rt As Long
Set fDialog = Application.FileDialog(msoFileDialogSaveAs)
fDialog.InitialFileName = ActiveCell.FormulaR1C1
rt = fDialog.Show
If rt <> 0 Then sFolderName = fDialog.SelectedItems(1) & Application.PathSeparator
Set fDialog = Nothing
Range("A1").Select

Mark as answered Ian?