User Form automatically sends SMS Text Msg when user presses Submit Button

[BigHungarian]

Hi Team,

I'm back, seeking guidance from this Amazing Community. This is the most ambitious component to my project and I need HELP in a big way.

We have a program at work where a Team Member reports Safe and Unsafe working conditions. Currently this process is paper driven and the Management Team gets the information late in the day instead of right away.

The scope of the project is to have a User Form send a SMS text message when the Team Member presses the Submit Button but only if the condition being reported is "Unsafe". I do not want to rely on the TM in any way and the process of sending the SMS needs to be automatic and without their input and knowledge.

I don't want the collected data to be dumped in any random order but strung together to make an intelligent text communication. Please see the attached, condensed, version to gain insight into this project.

Once again Thank You to all the People out there contributing to the success of others!

Cheers

[Mumps1]

You can send an SMS message by sending an email if you know the person's cell phone service provider and SMS gateway domain of the service provider. For example, if you look at the line in red in the code below, I have used a dummy 10 digit phone number and the SMS gateway domain for Bell Canada. Have a look at this link for the SMS gateway domains of most service providers: https://en.wikipedia.org/wiki/SMS_gateway
Change the phone number and gateway domain to suit your needs. You can test the macro by inserting the information for your own cell number.

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Dim LastRow As Long, ws As Worksheet
    Dim OutApp As Object, OutMail As Object
    Set ws = Sheets("Data")
    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1 'Finds the last blank row
    ws.Range("A" & LastRow).Value = ComboBox1.Text 'Adds the TextBox3 into Col A & Last Blank Row
    ws.Range("B" & LastRow).Value = ComboBox2.Text
    ws.Range("C" & LastRow).Value = ComboBox3.Text
    ws.Range("D" & LastRow).Value = TextBox1.Text
    ws.Range("E" & LastRow).Value = TextBox2.Text
    If ComboBox3.Text = "Unsafe" Then
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .To = "1234567890@txt.bell.ca"
            .Subject = "Unsafe condition reported"
            .HTMLBody = ComboBox1.Text & " is reporting an unsafe condition." & vbNewLine & _
                "Location: " & ComboBox2.Text & vbNewLine & _
                "Details :" & TextBox1.Text & vbNewLine & _
                "Outcome: " & TextBox2.Text & vbNewLine
            .send
        End With
    End If
    Application.ScreenUpdating = True
End Sub

[BigHungarian]

Hi Mumps1. Thanks for your help with my project. When sending the SMS it needs to be send it to 4 recipients at once.

Your additional help would be appreciated.

Thanks

[Mumps1]

Simply put them all between the quotes separated by a semi-colon:

.To = "1234567890@txt.bell.ca;1234567890@txt.bell.ca;1234567890@txt.bell.ca;1234567890@txt.bell.ca"

[BigHungarian]

Hello Mumps1,

I need to circle back to this bit of code you've helped me out with.

If ComboBox3.Text = "Unsafe" Then. I need to add another category that would trigger the sending of the Text Msg. but I cant figure out how to stitch them together.

Example:

If ComboBox3.Text = "Unsafe" ; "Very Unsafe" Then

Ya, it's not as easy as that... I've tried many variations but have turned up unsuccessful. Help Please.

Thanks

[Mumps1]

Try:

If ComboBox3.Text = "Unsafe" Or ComboBox3.Text = "Very Unsafe" Then

[BigHungarian]

SOLVED*** AGAIN *** Most Excellent... WORKS Perfectly!

Thanks Mumps1

[Mumps1]

My pleasure.