Example of embedding a "Submit" button in spreadsheet

Question

Example of embedding a "Submit" button in spreadsheet

Answer 1

Hi John, 

Thank you for the response. Based on your clarification, I would recommend trying this macro script to achieve your need:  

Sub SubmitPayroll() 

    Dim OutlookApp As Object 

    Dim OutlookMail As Object 

    Dim FilePath As String 

    ' Activate the Timesheet worksheet 

    ThisWorkbook.Sheets("Timesheet").Activate 

    ' Save the workbook with the Timesheet sheet active 

    ThisWorkbook.Save 

    FilePath = ThisWorkbook.FullName 

    ' Create Outlook email 

    Set OutlookApp = CreateObject("Outlook.Application") 

    Set OutlookMail = OutlookApp.CreateItem(0) 

    With OutlookMail 

        .To = "******@yourcompany.com" ' Replace with actual email 

        .Subject = "Payroll Submission" 

        .Body = "Attached is the latest payroll spreadsheet." 

        .Attachments.Add FilePath 

        .Display ' Use .Send to send immediately 

    End With 

    ' Clean up 

    Set OutlookMail = Nothing 

    Set OutlookApp = Nothing 

End Sub 

If you have any questions or concerns trying the steps above, please don’t hesitate to ask, I'm here to help. Looking forward to your response. 

Best regards, 

Jay-Tr– MSFT | Microsoft Community Support Specialist

Answer 2

Hi John, 

Good day to you! Thank you for reaching out to Microsoft Community. 

To better assist you and provide proper solutions, could you please share what you want to do with the submit button? For example: running a macro, open a url, open another spreadsheet... 

If you have determined what to do want the button, please provide me with the information and I'll give you steps to do so.  

Best regards, 

Jay-Tr– MSFT | Microsoft Community Support Specialist

Answer 3

I want the submit button to save the current spreadsheet, and email a copy to our central payroll processing person. It would be nice to be able to review the spreadsheet before sending but that's not an absolute requirement.

Answer 4

Hi John,

Good day to you! Thank you for the clarification.  

According to your description, I would recommend trying the following steps to achieve your need:  

1. Enable the Developer Tab

Go to File → Options → Customize Ribbon

Check the box for Developer and click OK

2. Insert a Button

Go to the Developer tab

Click Insert → choose Button (Form Control)

Draw the button on your sheet

3. Assign a Macro

When prompted, click New to create a macro. This opens the VBA editor.

4. Add VBA Code

Here’s a sample VBA script that:

Saves the workbook

Emails it as an attachment via Outlook Sub SubmitPayroll()

    Dim OutlookApp As Object

    Dim OutlookMail As Object

    Dim FilePath As String

    ' Save the workbook

    ThisWorkbook.Save

    FilePath = ThisWorkbook.FullName

    ' Create Outlook email

    Set OutlookApp = CreateObject("Outlook.Application")

    Set OutlookMail = OutlookApp.CreateItem(0)

    With OutlookMail

        .To = "******@yourcompany.com" ' Replace with actual email

        .Subject = "Payroll Submission"

        .Body = "Attached is the latest payroll spreadsheet."

        .Attachments.Add FilePath

        .Display ' Use .Send to send immediately

    End With

    ' Clean up

    Set OutlookMail = Nothing

    Set OutlookApp = Nothing

End Sub

If you have any issue trying the steps above, please don’t hesitate to ask, I'm here to help. Looking forward to your response.

Best regards,

Jay-Tr– MSFT | Microsoft Community Support Specialist

Answer 5

Hi John,

It has been a while and I am writing to see how things are going with this issue. Have you had a chance to check the replies provided? If my answer is helpful, please mark it as an answer, which will definitely help others in the community who have similar queries to find solutions to their problems faster.

Best regards, 

Jay-Tr - MSFT | Microsoft Community Support Specialist