How do I use a clickable button to submit a completed excel form?

[Yakillinmesmalls]

Hello, I am trying to create a public form within my company that users can fill out, then hit submit and it emails that completed form to all stakeholders. The problem that I am running into is that, with the VBA code that I'm using, when I click "submit", it fills out the email correctly, but the attachment is the blank excel form. How do I have the completed form attached to the email instead of the blank attachment? Here is the code that I'm running:

Private Sub CommandButton1_Click()
Dim xOutlookObj As Object
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
On Error Resume Next
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Anything you want say!" & vbNewLine & vbNewLine & _
"Here two!!" & vbNewLine & _
"Here third!!!"
On Error Resume Next
With xOutMail
.To = "Obviouslynotmyrealemail.com"
.CC = ""
.BCC = ""
.Subject = "Test. Test. Is this thing on?"
.Body = xMailBody
.Attachments.Add ActiveWorkbook.FullName
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

 

[fadee2]

hi Yakillinmesmalls,

Welcome to the board.

Did you save your workbook before attaching it to the email?

hth...

 

[ebea]

You can't send your Userform directly, through an email. There has been some works around, as Functions, but they where all for 32Bit Excel; and as you probably have different users running different systems, it will not work.
What you can do, is to take the Textboxes Text value, and use in the Body section, and then include this in an PDF attached file, made from the mail.

 

[Yakillinmesmalls]

hi Yakillinmesmalls,

Welcome to the board.

Did you save your workbook before attaching it to the email?

hth...

Yes, its saved as a macro enabled wb. I don't think you are understanding what I'm trying to achieve tho. I want the form to be public within my company so that if there is an incident, the employee can fill out the form, hit submit, and it send it to the safety team. Right now, the way that the code is written, when the submit button is clicked it sends a blank form as the attachment to the team

 

[Yakillinmesmalls]

You can't send your Userform directly, through an email. There has been some works around, as Functions, but they where all for 32Bit Excel; and as you probably have different users running different systems, it will not work.
What you can do, is to take the Textboxes Text value, and use in the Body section, and then include this in an PDF attached file, made from the mail.

Hey Ebea, It's an internal form for my company and everyone is using the same system. I want an employee to be able to fill out an incident form, click submit, and the completed form is sent to the safety team for review. Right now, as the code is written, the email just attaches a form that isn't filled out. I was thinking about finding something that captures the important fields after they are filled out and sends that to the team either in an attachment or in the body of the email

 

[Fluff]

Yes, its saved as a macro enabled wb.

Assuming that the Form is just a sheet within the workbook, you need to save the workbook after completing the form & before emailing it.

 

[Yakillinmesmalls]

Assuming that the Form is just a sheet within the workbook, you need to save the workbook after completing the form & before emailing it.

Yeah, I'm really bummed. I was hoping to streamline the process where everything is achieved with a single click of the submit button

 

[Fluff]

You can save the workbook at the start of the macro.

 

[Yakillinmesmalls]

I think that I'm going to just use a msg box to prompt employees to save the attach to their desktop and attach the completed form to the email