Export Form to Excel and Auto-Name

I've searched the threads for a couple of days, and while I found some that came close to answering my question I thought I'd finally just post it and either get some responses or links to what you think are relevant responses.

I have a form in Access that once complete I want the user to hit a button and it saves that form's data in Excel and automatically re-names the new Excel file based on a combination of fields in the form (ie. date, cust_nbr, etc). Then I want to email that form (excel file?) to several people. Then I'd like a message box in access to say something like "Form Successfully saved at (folder/file location) and emails sent to person1 & person 2".

I know that's a lot of different stuff going on there, so perhaps we just start with the first one - code to save as excel and auto-name the form (also once this works I want the form to be blanked-out and ready for a new entry).

I'm pretty new to VB so any help or at least direction would be appreciated. Thanks!

Are you doing this all in Access or in VB6? What version of Access and Excel?

Whoops. Sorry about that. Both are MS Office 2003, SP1. I think I'd like to do this in VB6, though honestly I'm not sure which is the best way to proceed, as I'm new to both of them.

But your form in in Access correct? If it is then we can do it in Access' VBA IDE.

Yes, my form is in Access.

Then we will write the code for this behind the form in its class file as long as this is going to be just for this one form. If you want to use something like this on other forms then we can place it in a module.

Moved. :)