Click a SUBMIT button to activate the coding

[Blanchetdb]

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:

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

 

[Joe4]

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.

 

[Blanchetdb]

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

 

[Joe4]

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?

 

[Blanchetdb]

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

 

[Joe4]

OK, you should be able to replace this section of 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:

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.

 

[Blanchetdb]

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

 

[Joe4]

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:

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:

Sub Worksheet_Change(ByVal Target As Range)

 

[Blanchetdb]

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?

 

[Joe4]

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).