Question
Sending SMS from excel VBA
I am trying to create VBA code in excel send an SMS text to a short list of customers.
I have the following code and I can use a GET function and received responses from the API but when I try to POST an SMS I get an Error 400 Bad Request. Not sure if my formatting is wrong or what? I would also appreciate input on better VBA code to obtain authorization. I am currently copying the active authorization code from my Sandbox which is only temporary.
Ultimately I want to execute this in a loop and post the send/read status to a cell in my spreadsheet by the number texted
Thanks in advance for any help.
Sub cmdOAuth2_Click()
Dim webServiceURL As String
Dim actionType As String
Dim targetWord As String
Dim actionType2 As String
Dim targetWord2 As String
Dim sMsg As String
Dim sToPhone As String
Dim sFromPHone As String
webServiceURL = "https://platform.devtest.ringcentral.com/restapi/v1.0/account/~/extension/~/sms";
actionType = "Accept"
targetWord = "application/json"
actionType2 = "Authorization"
targetWord2 = "Bearer Code copied from sandbox (Is there a better way to obtain auth within the VBA code"
sMsg = "test from excel"
sToPhone = "+17313632593"
sFromPHone = "+12055178260"
' use late binding
With CreateObject("Microsoft.XMLHTTP")
.Open "POST", webServiceURL, False
.SetRequestHeader actionType, targetWord
.SetRequestHeader actionType2, targetWord2
.Send _
"{" & _
"""from"": [{""phoneNumber"": ""+12055178260""}]," & _
"""to"": {""phoneNumber"": """ & sToPhone & """}," & _
"""text"": """ & sMsg & """" & _
"}"
If .Status = 200 Then
Debug.Print .responseText
MsgBox .GetAllResponseHeaders
Else
MsgBox .Status & ": " & .StatusText
End If
End With
End Sub
I have the following code and I can use a GET function and received responses from the API but when I try to POST an SMS I get an Error 400 Bad Request. Not sure if my formatting is wrong or what? I would also appreciate input on better VBA code to obtain authorization. I am currently copying the active authorization code from my Sandbox which is only temporary.
Ultimately I want to execute this in a loop and post the send/read status to a cell in my spreadsheet by the number texted
Thanks in advance for any help.
Sub cmdOAuth2_Click()
Dim webServiceURL As String
Dim actionType As String
Dim targetWord As String
Dim actionType2 As String
Dim targetWord2 As String
Dim sMsg As String
Dim sToPhone As String
Dim sFromPHone As String
webServiceURL = "https://platform.devtest.ringcentral.com/restapi/v1.0/account/~/extension/~/sms";
actionType = "Accept"
targetWord = "application/json"
actionType2 = "Authorization"
targetWord2 = "Bearer Code copied from sandbox (Is there a better way to obtain auth within the VBA code"
sMsg = "test from excel"
sToPhone = "+17313632593"
sFromPHone = "+12055178260"
' use late binding
With CreateObject("Microsoft.XMLHTTP")
.Open "POST", webServiceURL, False
.SetRequestHeader actionType, targetWord
.SetRequestHeader actionType2, targetWord2
.Send _
"{" & _
"""from"": [{""phoneNumber"": ""+12055178260""}]," & _
"""to"": {""phoneNumber"": """ & sToPhone & """}," & _
"""text"": """ & sMsg & """" & _
"}"
If .Status = 200 Then
Debug.Print .responseText
MsgBox .GetAllResponseHeaders
Else
MsgBox .Status & ": " & .StatusText
End If
End With
End Sub