I've got this macro I pulled from some other site and it's not working quite like it should. In my eyes at least...

OK, first, a little back story...
At work we used to have a custom Outlook form that was used for submitting office supply order requests.
The form was stored on our public drive for all to access it when needed.
Well, the form broke somehow and no one knows how to fix it.
We need something to replace it.
Keep in mind that we have no access to the Internet, nor can we send/receive emails outside of the company.

So, I created a simple form in Excel with a drop-down list of all the items in the supply cage, a form field for the senders name, etc...

What the macro does is, it copies the active worksheet the form that just got filled out) to a new workbook (dropdown data is on other sheets in the original) and then opens Outlook (2003) to send it as an attachment.

The only catch is that when the email arrives the only thing filled out is the form fields, NOT the drop downs...

The ONLY way I can get the drop downs to send with data in them is to leave the original open.

Oh, and for some reason every time I sent this while testing, it would name the attachment Book1, Book 2, Book3, and so on. I can't have that...

Here's the code I'm currently using:
Sub SendOrderForm1()

'Create a new Workbook Containing 1 Sheet (left most) _
and sends as attachment.

    ThisWorkbook.Sheets(1).Copy

    With ActiveWorkbook
         .SendMail Recipients:="email@address.com", _
          Subject:="Office Supply Order Form " & Format(Date, "dd/mmm/yyyy")
         .Close SaveChanges:=False
    End With

End Sub

Here's what I'm ultimately needing from this macro:

User opens form and fills it out.
User clicks the button with the macro assigned to it.
Macro copies the active worksheet to a new workbook with a specified name. ("Weekday Supply Order" for example) and SAVES it.
Macro sends the the copy via Outlook to designated recipients.
Macro then deletes the copy and exits the (original) workbook/form without saving any changes.


The people who will be using this form the most have almost no computer experience at all, so that's why it need to be as automated as possible.


Hopefully this makes sense to everyone and we can resolve this one quickly.
My supply cage clerk is getting sick of the phone calls.