VBA code to send sms from a website amendment needed

kelly mort

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,169
Office Version
  1. 2016
Platform
  1. Windows
Code: 
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:
Code: 
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:
Code: 
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
Hi kelly.
It is not a solution that am providing but I think this, as it is (if it works) can be a solution to me. I have looked for something like this for three months.
As you can see that am new here, I only joined after seeing your question relating directly to my problem.

I am not a developer at all but am using Arkesel to send bulk sms. I needed the code to do it directly from excel. I didn't know how to write it combining with API.

There are few things though that I still need your help on. As you can see am new, I don't know how to start a conversation so I decided to write just here.
 
Upvote 0
My problem is this.
I need to be able to send messages to multiple recipints, capturing in a message, the name and outstanding balance from excel.
It shoul read like this
"Hello {NAME}", please be reminded of your outstanding balance of {AMOUNT} from last quarter. We are kindly asking you to pay before starting of the next term.

I will have a button assigned macro to send this message for one click. The macro part is the problem, the one that will call API.
 
Upvote 0
Abren said:
Hi kelly.
It is not a solution that am providing but I think this, as it is (if it works) can be a solution to me. I have looked for something like this for three months.
As you can see that am new here, I only joined after seeing your question relating directly to my problem.

I am not a developer at all but am using Arkesel to send bulk sms. I needed the code to do it directly from excel. I didn't know how to write it combining with API.

There are few things though that I still need your help on. As you can see am new, I don't know how to start a conversation so I decided to write just here.
Click to expand...
To achieve what you want, you will need to loop through your data while sending the message.

I am out of my pc at the moment. I will post the solution here later when I get back to my pc.

Regards
 
Upvote 0
Hello @Abren

Please have a look at this code and see if it does what you want to do.


Code: 
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
    
    On Error GoTo ErMsg:
    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
                End If
            Next r
            
            MsgBox PosRes & " messages were sent", vbInformation, "Delivery report"
        End If
        .ScreenUpdating = True
    End With
    Exit Sub
ErMsg:
    MsgBox "There is an error!", vbExclamation, "Error alert"
End Sub

You may have to adjust a few things to make it what you want it to be.

Remember to replace your API KEY .

regards
Kelly
 
Upvote 0
kelly mort said:
I want to store the message in a variable and call it later. Something like this:
Code: 
text1 = “Hello world “
text2 = “Help me out”

myVar = text1 & text2

values = "sender=Thank You&message=myVar&recipients[]=XXXXXXXXXX"
Click to expand...

Perhaps as below?
VBA Code: 
text1 = "Hello world "
text2 = "Help me out"
myVar = text1 & text2

values = "sender=Thank You&message=" & myVar & "&recipients[]=XXXXXXXXXX"
 
Upvote 0
kelly mort said:
Hello @Abren

Please have a look at this code and see if it does what you want to do.


Code: 
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
   
    On Error GoTo ErMsg:
    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
                End If
            Next r
           
            MsgBox PosRes & " messages were sent", vbInformation, "Delivery report"
        End If
        .ScreenUpdating = True
    End With
    Exit Sub
ErMsg:
    MsgBox "There is an error!", vbExclamation, "Error alert"
End Sub

You may have to adjust a few things to make it what you want it to be.

Remember to replace your API KEY .

regards
Kelly
Click to expand...
Hello @kelly mort
Thank you for the help but I have not been able to make it through. I don't think it's the code as I said before am still learning so it's probably my lack of knolwdge.
Here is my requst to you, I dont mind sharing with you my API KEY, if you can help me so you can fix it for me and probably do a little trial.
Am sorry if am asking for too much.
Thanks again.
 
Upvote 0
kelly mort said:
Hello @Abren

Please have a look at this code and see if it does what you want to do.


Code: 
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
   
    On Error GoTo ErMsg:
    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
                End If
            Next r
           
            MsgBox PosRes & " messages were sent", vbInformation, "Delivery report"
        End If
        .ScreenUpdating = True
    End With
    Exit Sub
ErMsg:
    MsgBox "There is an error!", vbExclamation, "Error alert"
End Sub

You may have to adjust a few things to make it what you want it to be.

Remember to replace your API KEY .

regards
Kelly
Click to expand...
Actually am receiving this error amd I dont know what to do.
Debugger points to this line Dim values As New Scripting.Dictionary
 
Upvote 0

Attachments

  • PrintScreen.png
    PrintScreen.png
    145.5 KB · Views: 16
Upvote 0
@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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

X
  • Question Question
vba get html table vale to excel
Replies
1
Views
722
Fluff
Fluff
A
  • Question Question
Vba code for data fetching from nseindia
Replies
0
Views
528
asp123
A
W
  • Question Question
Error In VBA Code
Replies
3
Views
4K
wella86
W
R
  • Question Question
Set sending address - Excel VBA
Replies
3
Views
600
DanteAmor
DanteAmor
A
  • Solved
Record the sent date after executing Excel send email VBA
Replies
5
Views
1K
amanphilip
A

Forum statistics

Threads
1,224,823
Messages
6,181,171
Members
453,021
Latest member
Justyna P

Share this page

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