VBA code to send sms from a website amendment needed

[kelly mort]

Sub SendSMS()

    Dim URL As String
    Dim ApiKey As String
    Dim httpRequest As Object
    Dim responseText As String
    Dim values As String
    
    URL = "https://sms.arkesel.com/api/v2/sms/send"
    ApiKey = "RlBrQ21ISXlhTnJuZXNmQnJFZVhEcmxFa0U"
    
    Set httpRequest = CreateObject("MSXML2.XMLHTTP")
    
    httpRequest.Open "POST", URL, False
    
    ' Set request headers
    httpRequest.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    httpRequest.setRequestHeader "api-key", ApiKey
    
    ' Set request body
    values = "sender=Thank You&message=Hello World from VB6&recipients[]=XXXXXXXXXX"
    httpRequest.send values
    
    ' Get response text
    responseText = httpRequest.responseText
    
    ' Print response text to immediate window
    Debug.Print responseText

End Sub

Hi everyone,
I have this code here for sending sms.

I used chatGpt to covert it from VB to VBA as seen above.

The thing is that on this line:

values = "sender=Thank You&message=Hello World from VB6&recipients[]=XXXXXXXXXX"

I want to store the message in a variable and call it later. Something like this:

text1 = “Hello world “
text2 = “Help me out”

myVar = text1 & text2

values = "sender=Thank You&message=myVar&recipients[]=XXXXXXXXXX"

But can’t do that because the syntax is not a familiar one for me.

Can someone help me fix it?

Thanks in advance

 

[Georgiboy]

Kelly beat me to it..

 

[Abren]

@Abren

You must enable "Microsoft Scripting Runtime"



To use the Scripting.Dictionary object in your code, you need to enable the "Microsoft Scripting Runtime" library in your project. This library provides the necessary components for using dictionaries in VBA.

Follow these steps:

1. Open the Visual Basic for Applications (VBA) editor by pressing Alt + F11 in Excel or Word (or any other Office application).
2. In the VBA editor, go to the "Tools" menu and select "References" from the drop-down menu.
3. In the "References" dialog box, scroll down the list and look for "Microsoft Scripting Runtime."
4. Tick the checkbox next to "Microsoft Scripting Runtime" to enable the library.
5. Click the "OK" button to close the dialog box.

Thank you @kelly mort
I followed the steps correctly, it has gotten me here.

 

[Abren]

@Abren

You must enable "Microsoft Scripting Runtime"



To use the Scripting.Dictionary object in your code, you need to enable the "Microsoft Scripting Runtime" library in your project. This library provides the necessary components for using dictionaries in VBA.

Follow these steps:

1. Open the Visual Basic for Applications (VBA) editor by pressing Alt + F11 in Excel or Word (or any other Office application).
2. In the VBA editor, go to the "Tools" menu and select "References" from the drop-down menu.
3. In the "References" dialog box, scroll down the list and look for "Microsoft Scripting Runtime."
4. Tick the checkbox next to "Microsoft Scripting Runtime" to enable the library.
5. Click the "OK" button to close the dialog box.

I rectified the above error (If am correct) by making it a comment (adding ' before that End if), It did get me to "Are you sure about this"
after ok it just said "there is an error"

 

[kelly mort]

Turn off the error handler and run again and let's see which error is occurring.

 

[Abren]

Turn off the error handler and run again and let's see which error is occurring.

I wish I'd know how to.

 

[kelly mort]

OK
I will revise the code for you when I get home.

For now I think it's because you have blank contacts

Try eliminating the blanks from the db and run it again

Also there are other libraries that you must turn on.

I will check them out for you as well

 

[Abren]

OK
I will revise the code for you when I get home.

For now I think it's because you have blank contacts

Try eliminating the blanks from the db and run it again

Also there are other libraries that you must turn on.

I will check them out for you as well

I thought so myself, about blank contacts. I eliminated but code still gives the same result.
Sorry for all the disturbunce but please don't get tired of me.

 

[Abren]

OK
I will revise the code for you when I get home.

For now I think it's because you have blank contacts

Try eliminating the blanks from the db and run it again

Also there are other libraries that you must turn on.

I will check them out for you as well

Did you try to review it?

 

[kelly mort]

Make sure you have enabled
Microsoft WinHTTP Services, Version x.x

Try this and let's see how it goes.

Sub SendMultiSMS()
    Dim url As String
    Dim r As Range
    Dim db As Object
    Dim lr As Long
    Dim rng As Range
    Dim sName As String
    Dim sTell As String
    Dim sDebt As Double
    
    Dim content As String
    Dim key As Variant
    Dim PosRes As Long
    
    Dim apiKey As String
    Dim client As Object
    
    If MsgBox("Are you sure about this?", vbYesNo + vbExclamation + vbDefaultButton2, "XXXXXXXXX") <> vbYes Then
        
        Exit Sub
    End If
    
    apiKey = "YOUR APIKEY HERE"
    url = "https://sms.arkesel.com/api/v2/sms/send"
    Set client = CreateObject("WinHttp.WinHttpRequest.5.1")
    client.Open "POST", url, False
    client.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    client.SetRequestHeader "api-key", apiKey
    
    Dim values As New Scripting.Dictionary
    
    values("sender") = "DebtAlert"
    
    Set db = Sheets("Data")
    lr = db.Cells(Rows.Count, "B").End(xlUp).Row
    
    With Application
        .ScreenUpdating = False
        PosRes = 0
        If lr > 1 Then
            Set rng = db.Range("B2:B" & lr)
            For Each r In rng
                content = ""
                
                sName = r.Offset(0, 0) ' NAMES ARE IN COLUMN B
                sTell = r.Offset(0, 1) ' PHONE NUMBERS ARE IN COLUMN C
                sDebt = r.Offset(0, 2) ' DEBTS ARE IN COLUMN D
                
               If Len(.Trim(sTell)) Then
                   values("message") = "Hello" & sName & ", please be reminded of your outstanding balance of " & _
                   sDebt & " from last quarter. We are kindly asking you to pay before starting of the next term" & vbCrLf & _
                   "Thank you."

                   values("recipients[]") = sTell
                   For Each key In values.keys
                       content = content & key & "=" & values(key) & "&"
                   Next key
           
                   content = Left(content, Len(content) - 1)
                   client.Send content
                   
                  If client.Status = 200 Then PosRes = PosRes + 1
               End If
            Next r
            
            MsgBox PosRes & " messages were sent", vbInformation, "Delivery report"
        End If
        .ScreenUpdating = True
    End With
End Sub

 

[Abren]

Make sure you have enabled
Microsoft WinHTTP Services, Version x.x

Try this and let's see how it goes.

Sub SendMultiSMS()
    Dim url As String
    Dim r As Range
    Dim db As Object
    Dim lr As Long
    Dim rng As Range
    Dim sName As String
    Dim sTell As String
    Dim sDebt As Double
   
    Dim content As String
    Dim key As Variant
    Dim PosRes As Long
   
    Dim apiKey As String
    Dim client As Object
   
    If MsgBox("Are you sure about this?", vbYesNo + vbExclamation + vbDefaultButton2, "XXXXXXXXX") <> vbYes Then
       
        Exit Sub
    End If
   
    apiKey = "YOUR APIKEY HERE"
    url = "https://sms.arkesel.com/api/v2/sms/send"
    Set client = CreateObject("WinHttp.WinHttpRequest.5.1")
    client.Open "POST", url, False
    client.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    client.SetRequestHeader "api-key", apiKey
   
    Dim values As New Scripting.Dictionary
   
    values("sender") = "DebtAlert"
   
    Set db = Sheets("Data")
    lr = db.Cells(Rows.Count, "B").End(xlUp).Row
   
    With Application
        .ScreenUpdating = False
        PosRes = 0
        If lr > 1 Then
            Set rng = db.Range("B2:B" & lr)
            For Each r In rng
                content = ""
               
                sName = r.Offset(0, 0) ' NAMES ARE IN COLUMN B
                sTell = r.Offset(0, 1) ' PHONE NUMBERS ARE IN COLUMN C
                sDebt = r.Offset(0, 2) ' DEBTS ARE IN COLUMN D
               
               If Len(.Trim(sTell)) Then
                   values("message") = "Hello" & sName & ", please be reminded of your outstanding balance of " & _
                   sDebt & " from last quarter. We are kindly asking you to pay before starting of the next term" & vbCrLf & _
                   "Thank you."

                   values("recipients[]") = sTell
                   For Each key In values.keys
                       content = content & key & "=" & values(key) & "&"
                   Next key
          
                   content = Left(content, Len(content) - 1)
                   client.Send content
                  
                  If client.Status = 200 Then PosRes = PosRes + 1
               End If
            Next r
           
            MsgBox PosRes & " messages were sent", vbInformation, "Delivery report"
        End If
        .ScreenUpdating = True
    End With
End Sub

It runs to where it asks if am sure, after pressing okay it gives this