How to Use a VBA Code for Submit Button in Excel (with Easy Steps)

We have a dataset that contains information about several Sales representatives of XYZ group. The Names of the sales representatives and their sales in several quarters are given in columns B, C, D, and E. From our dataset, we will use the VBA code to submit the button in Excel.

Step 1: Create a Dataset with Proper Parameters

Create a dataset using VBA code for the submit button in Excel. The dataset will contain information about several XYZ group sales representatives. We will use the VBA code for the submit button so the sales representatives can send their performance through email.

Step 2: Make a Submit Button Using the Controls Command

To make a submit button from your Developer ribbon,
Developer → Controls → Insert → Command Button (ActiveX Control)

You can make a submit button, which has been given in the screenshot below.

We will change the name of the created CommandButton1,
Press right-click.
As a result, a window appears in front of you.
From the window, select the Properties option.

A Properties dialog box pops up.
To change the name and color of the CommandButton1, do like the screenshot below.

You will be able to change the name of CommandButton1 to Submit.

Step 3: Run VBA Code for Submit Button in Excel

Press the right-click on that button.
As a result, a window appears in front of you.
From the window, select the View Code option.

A module named Submit Button.xlsm – sheet4 (Code) pops up.
Paste the below VBA code in that module and save the code using the Ctrl + S key.

Private Sub CommandButton1_Click()
Dim x As Object
    Dim y As Object
    Dim R As Object
    Dim M As String
    On Error Resume Next
    Set y = CreateObject("Outlook.Application")
    Set R = y.CreateItem(0)
    M = "Write down your email message here"
                  On Error Resume Next
    With R
        .To = "[email protected]"
        .CC = ""
        .BCC = ""
        .Subject = "Enter the Email Subject Here"
        .Body = M
        .Attachments.Add ActiveWorkbook.FullName
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set R = Nothing
    Set y = Nothing
End Sub