Macro needed for email/submit button

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.

Hello S3NTYN3L,

Do you have a sample workbook you can post?

Sincerely,
Leith Ross

It's at work, so no, I don't.

Just create a few drop down lists on the first page and the list it pulls from on the second.

Basically, it reads like this:

Name: (combobox for user input)
Department: (drop down list. data pulled from second page)
Shift worked: (same setup as Department combobox)

Item1 (drop down)  QTY: (combobox set for numbers only)  EA/BX: (This is a drop down list, same as the others. It's asking whether user wants a Box or and Each of the item)
Item2                   QTY:                                               EA/BX
Item3                   QTY:                                               EA/BX
Item4                   QTY:                                               EA/BX
Item5                   QTY:                                               EA/BX


" Submit Order " button with macro attached.

I'll try and smuggle a copy out today...

OK, I've attached a copy of the workbook.

Please, keep in mind that no one using this form is allowed to access the outside world or install anything (add-ins and such).


Thanks in advance for any help you can provide!

Hello S3NTYN3L,

I don't know what is causing the problem. I doesn't work on my system either. Outlook launches but I don't receive the email and there are no errors.

Sincerely,
Leith Ross

Did you edit the macros assigned to the buttons with your email address?

At work, the email is sent successfully but unless the original is open, the copy that gets sent is blank.

As I said, most of the people that will be using this thing aren't very knowledgeable when it come to computers, so...


Perhaps there is a workaround...
Is there a way I can get the data a user selects in the drop downs to be displayed in a normal cell?

If so, I've got plenty of code from this site that will send a range of cells in the body of the email or as an attachment.

Hello S3NTYN3L,

I did get it to work. I had to restart my system because of some weird Outlook failure. Turns out the controls create links back to the original workbook they were copied from. I have spent hours trying to find an easy way to break these links and reassign them to the workbook they are in. So far, no joy. Wish I had better news to report.

Sincerely,
Leith Ross

bump...

Hello S3NTYN3L,

This macro will remove the Save and Save As options from the menu. To restore them set the values to TRUE and run the macro.

Sub RemoveSave()

  Dim Ctrl As Object
  Dim CmdBar As Object
  
    Set CmdBar = Excel.CommandBars("Worksheet Menu Bar")
    Set Ctrl = CmdBar.Controls("&File")
    
      With Ctrl
        .Controls("&Save").Visible = False
        .Controls("Save &As...").Visible = False
      End With
      
End Sub

Sincerely,
Leith Ross