Click a SUBMIT button to activate the coding

Blanchetdb

Board Regular
Joined
Jul 31, 2018
Messages
161
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I am looking for some help in regards to using a "SUBMIT" button to activate the coding

this is the coding I presently have done:

Code:
Dim xRg As Range

Sub Worksheet_Change(ByVal Target As Range)
    Set xRg = Intersect(Range("F12"), Target)
    
    If Not (xRg Is Nothing) Then
        If Target = "yes" Or Target = "oui" Then
            Call Mail_emsg1
        End If
    End If
    
    Set xRg = Intersect(Range("J17"), Target)
    
    If Not (xRg Is Nothing) Then
        If Target = "Granted" Or Target = "Acquise" Then
            Call Mail_emsg2
        End If
    End If
    
    Set xRg = Intersect(Range("J17"), Target)
    
    If Not (xRg Is Nothing) Then
         If Target = "Pending" Or Target = "En Attente" Then
            Call Mail_emsg3
         End If
    End If
    
    Set xRg = Intersect(Range("H4"), Target)
    
    If Not (xRg Is Nothing) Then
         If Target = TargetValue Then
            Call Mail_emsg4
         End If
    End If
    
End Sub
Sub Mail_emsg1()
    Dim pNum As String
    Dim iName As String
    Dim vacancies As String
    Dim nIncumbent As String
    Dim excluded As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    
    pNum = Worksheets("Sheet1").Cells(6, "C").Value
    iName = Worksheets("Sheet1").Cells(10, "E").Value
    vacancie = Worksheets("Sheet1").Cells(12, "K").Value
    nIncumbent = Worksheets("Sheet1").Cells(15, "A").Value
    excluded = Worksheets("Sheet1").Cells(15, "F").Value
    
    
    xMailBody = "Hi Christine" & vbNewLine & vbNewLine & _
                "A 3811 has been submitted for approval that involves a position that is excluded or a person that is presently excluded" & vbNewLine & vbNewLine & _
                "Position number: " & pNum & vbNewLine & _
                "Incumbent's name: " & iName & vbNewLine & vbNewLine & _
                "Is the position presently vacant?: " & vacancie & vbNewLine & _
                "Name of present incumbent: " & nIncumbent & vbNewLine & _
                "Identify incumbent that will be excluded: " & excluded
                On Error Resume Next
    With xOutMail
        .To = "xxxxxxx[EMAIL="xxxxxxx@outlook.ca"]@outlook.ca[/EMAIL]"
        .CC = ""
        .BCC = ""
        .Subject = "send by cell value test"
        .Body = xMailBody
        .Display
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

Sub Mail_emsg2()
    Dim pNum As String
    Dim iName As String
    Dim vacancies As String
    Dim nIncumbent As String
    Dim excluded As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    
    pNum = Worksheets("Sheet1").Cells(6, "C").Value
    iName = Worksheets("Sheet1").Cells(10, "E").Value
    StaffType = Worksheets("Sheet1").Cells(33, "C").Value
    Secreq = Worksheets("Sheet1").Cells(17, "D").Value
    PRI = Worksheets("Sheet1").Cells(10, "I").Value
    Location = Worksheets("Sheet1").Cells(10, "A").Value
    xMailBody = "Hi Security" & vbNewLine & vbNewLine & _
                "A staffing request has been submitted stating that security has ben obtained" & vbNewLine & _
                "Please reply with confirmation as to the status of the person's security clearance" & vbNewLine & vbNewLine & _
                "Position number: " & pNum & vbNewLine & _
                "Incumbent's name: " & iName & vbNewLine & _
                "PRI: " & PRI & vbNewLine & vbNewLine & _
                "Staffing Type: " & StaffType & vbNewLine & _
                "Security requirement of the position: " & Secreq & vbNewLine & _
                "Location: " & Location
                On Error Resume Next
    With xOutMail
        .To = "[EMAIL="xxxxxxx@outlook.ca"]xxxxxxx@outlook.ca[/EMAIL]"
        .CC = ""
        .BCC = ""
        .Subject = "Security clearance status - confirmation request "
        .Body = xMailBody
        .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

Sub Mail_emsg3()
    Dim iName As String
    Dim vacancies As String
    Dim nIncumbent As String
    Dim excluded As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    
    
    iName = Worksheets("Sheet1").Cells(10, "E").Value
    StaffType = Worksheets("Sheet1").Cells(33, "C").Value
    Secreq = Worksheets("Sheet1").Cells(17, "D").Value
    PRI = Worksheets("Sheet1").Cells(10, "I").Value
    Location = Worksheets("Sheet1").Cells(10, "A").Value
    xMailBody = "Hi," & vbNewLine & vbNewLine & _
                "A staffing request has been submitted stating that security is Pending" & vbNewLine & _
                "Please use the appropriate link to access the desired security clearance" & vbNewLine & vbNewLine & _
                "Incumbent's name: " & iName & vbNewLine & _
                "PRI: " & PRI & vbNewLine & vbNewLine & _
                "Staffing Type: " & StaffType & vbNewLine & _
                "Security requirement of the position: " & Secreq & vbNewLine & _
                "Location: " & Location & vbNewLine & vbNewLine & _
                "Security Level - Reliability: [URL]http://publiservice.tbs-sct.gc.ca/tbsf-fsct/330-23-eng.asp[/URL] " & vbNewLine & _
                "Security Level - Secret: [URL]http://publiservice.tbs-sct.gc.ca/tbsf-fsct/330-60-eng.asp[/URL]"
                On Error Resume Next
    With xOutMail
        .To = "[EMAIL="xxxxxxx@outlook.ca"]xxxxxxx@outlook.ca[/EMAIL]"
        .CC = ""
        .BCC = ""
        .Subject = "Security Clearance Required - link to forms "
        .Body = xMailBody
        .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

Sub Mail_emsg4()
    Dim EffStart As String
    Dim EffEnd As String
    Dim ClassReq As String
    Dim iName As String
    Dim pNum As String
    Dim Posrep As String
    Dim Bran As String
    Dim Sect As String
    Dim Reg As String
    Dim nIncumbent As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    
    
    EffStart = Worksheets("Sheet1").Cells(26, "K").Value
    EffEnd = Worksheets("Sheet1").Cells(27, "K").Value
    ClassReq = Worksheets("Sheet1").Cells(27, "D").Value
    iName = Worksheets("Sheet1").Cells(10, "E").Value
    pNum = Worksheets("Sheet1").Cells(6, "C").Value
    Posrep = Worksheets("Sheet1").Cells(26, "G").Value
    Bran = Worksheets("Sheet1").Cells(8, "A").Value
    Sect = Worksheets("Sheet1").Cells(8, "E").Value
    Reg = Worksheets("Sheet1").Cells(8, "I").Value
    Location = Worksheets("Sheet1").Cells(10, "A").Value
    
    xMailBody = "To Org. & Class.," & vbNewLine & vbNewLine & _
                "A Classification request has been submitted" & vbNewLine & _
                "Please refer to the information provided below" & vbNewLine & vbNewLine & _
                "Classification action requested: " & ClassReq & vbNewLine & vbNewLine & _
                "Incumbent's name (if applicable): " & iName & vbNewLine & _
                "Position number (if applicable): " & pNum & vbNewLine & _
                "Position number reports to: " & Posrep & vbNewLine & vbNewLine & _
                "Effective Start Date: " & EffStart & vbNewLine & _
                "End Date: " & EffEnd & vbNewLine & vbNewLine & _
                "Branch: " & Bran & vbNewLine & _
                "Section: " & Sect & vbNewLine & _
                "Region: " & Reg & vbNewLine & _
                "Location: " & Location
                On Error Resume Next
    With xOutMail
        .To = "xxxxxx[EMAIL="xxxxxx@outlook.ca"]@outlook.ca[/EMAIL]"
        .CC = ""
        .BCC = ""
        .Subject = "Classification Request"
        .Body = xMailBody
        .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

What I would like to happen is that once the client has completed the form, they click the SUBMIT button at the bottom of the form and it runs the coding on the sheet
 
Last edited by a moderator:

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
The code you currently have is event procedure code, which is automatically fired upon certain cells being updated.

If you would like to change it to code that runs manually, you can insert a button and attach code to it.
Note that the code you have above will need to be edited in order to work, as it uses the "Target" range variable, which in event procedure code is the range that was just updated.
Manual VBA code does not use "Target", so you will need to either hard-code the range you want to run it against, or add code that figures out what range you want it to run against.
 
Upvote 0
thank you for the response,

I wish I could tell you that I understand what you mean...

I would greatly appreciate if you could provide me with an example
 
Upvote 0
We need a little more information from you.
You automated code picks one of 4 different email procedures to run, depending on which cell was just updated (F12, H4, or J17).
By using this event procedure code, it is "smart" enough to figure out which cell just got updated.

However, if you want something manual to run (based on the click of a button), it will lose the ability to figure out which cell just got updated.
So, then how exactly do you want it to work? Do you want it to look at the values in ALL three of those cells (F12, H4, or J17), which means it may end up calling/sending more than one email code at a time?
Or do you envision it doing something else?
 
Upvote 0
I require the client to complete the form....once completed, they will press SUBMIT

What I need the form to do is recognize the answers entered in those specific cells and send out an email to the appropriate recipient(s). Depending on the responses on the form, there could be 1,2 or 3 emails sent out to various email addresses. I just don't want the emails to go out until they click the SUBMIT button at the end of the form

I really appreciate your help with this
 
Upvote 0
OK, you should be able to replace this section of code:
Code:
Dim xRg As Range

Sub Worksheet_Change(ByVal Target As Range)
    Set xRg = Intersect(Range("F12"), Target)
    
    If Not (xRg Is Nothing) Then
        If Target = "yes" Or Target = "oui" Then
            Call Mail_emsg1
        End If
    End If
    
    Set xRg = Intersect(Range("J17"), Target)
    
    If Not (xRg Is Nothing) Then
        If Target = "Granted" Or Target = "Acquise" Then
            Call Mail_emsg2
        End If
    End If
    
    Set xRg = Intersect(Range("J17"), Target)
    
    If Not (xRg Is Nothing) Then
         If Target = "Pending" Or Target = "En Attente" Then
            Call Mail_emsg3
         End If
    End If
    
    Set xRg = Intersect(Range("H4"), Target)
    
    If Not (xRg Is Nothing) Then
         If Target = TargetValue Then
            Call Mail_emsg4
         End If
    End If
    
End Sub
With this:
Code:
Sub SendEmails()

    If Range("F12") = "yes" Or Range("F12") = "oui" Then Call Mail_emsg1
    
    If Range("J17") = "Granted" Or Range("J17") = "Acquise" Then Call Mail_emsg2
    
    If Range("J17") = "Pending" Or Range("J17") = "En Attente" Then Call Mail_emsg3
    
    If Range("H4") = TargetValue Then Call Mail_emsg4

End Sub
and attach this code to your button ("Google attach VBA code to Excel button if you do not know how to do this").

The only thing about your code, is that for the last email, you seem to be comparing the value in H4 to a variable named "TargetValue", but you have not defined that value anywhere.
So I don't know where that is coming from.
 
Last edited:
Upvote 0
GREAT !! It worked.....

the last email is linked to a checkbox that if they check the box in Cell H4 it sends out an email......at this time, the email is sent right away instead of waiting for the SUBMIT button to be clicked and it also sends when you remove the check from the checkbox

I would like that function to work the same as the other 3...if possible
 
Upvote 0
the last email is linked to a checkbox that if they check the box in Cell H4 it sends out an email.
What kind of checkbox is it? Is it a Form control, ActiveX control, etc?
Is its value mapped to cell H4?

Try adding this code:
Code:
Sub Test()
    MsgBox Range("H4")
End Sub

Try checking the box, and running the code and let me know what the MsgBox returns.
Now, uncheck the box and run the code and let me know that MsgBox returns in that case, as well.

at this time, the email is sent right away instead of waiting for the SUBMIT button to be clicked
It sounds like you did not remove the initial Worksheet_Change code, like I said you need to do.
This code will be in the Sheet module. Make sure that you do not have any procedures on that sheet starting with:
Code:
Sub Worksheet_Change(ByVal Target As Range)
 
Upvote 0
the check box is a Form control in H4 and it is linked to emsg4

there are no mention of the Sub Worksheet_Change (ByVal Target As Range)..... anywhere in the coding

where am I suppose to add the coding mentioned above (Sub Test)? and do I link it to the checkbox in H4 if so, will that not bypass the SUBMIT button as well?
 
Upvote 0
Just add that code to a General module (from the VB Editor, right-click on the Workbook name in VBA Project Explorer and select "Insert" then "Module" and paste the code there.
Then simply manually check the check box, and manually run that VBA code (can do it from the Macro menu on the worksheet).
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top