question

russ-boyd14089 avatar image
russ-boyd14089 asked Barney Stone answered

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


sms and text messaging
1 |3000

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

russ-boyd14089 avatar image
russ-boyd14089 answered Andy Miscuk commented
Nothing like answering you own question.  I was able to find the syntax error.  Here is code that works in case it is helpful to anyone else.  I would still very much appreciate any help on code that will establish authorization without having to copy in the bearer code from my sandbox.  Happy to use a username and password approach.

Here is the working code to POST sms text from excel VBA

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 here from sandbox"
  sMsg = "Hello from excel VBA code"
  sToPhone = "123456789"
  sFromPHone = "12055178260"
  
' use late binding
  With CreateObject("Microsoft.XMLHTTP")
    .Open "POST", webServiceURL, False
    .SetRequestHeader actionType, targetWord
    .SetRequestHeader actionType2, targetWord2
    .SetRequestHeader "Content-Type", "application/json"
    .Send _
"{" & _
        """from"": {""phoneNumber"": ""12055178260""}," & _
        """to"": [{""phoneNumber"": """ & sToPhone & """}]," & _
        """text"": """ & sMsg & """" & _
        "}"
        
    If .Status = 200 Then
      Debug.Print .responseText
      MsgBox .GetAllResponseHeaders
    Else
      MsgBox .Status & ": " & .StatusText & .responseText
    End If
  End With
1 comment
1 |3000

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

Andy Miscuk avatar image Andy Miscuk commented ·

targetWord2 - Where do you get this?

0 Likes 0 ·
orchard-medical-center avatar image
orchard-medical-center answered Andy Miscuk commented
Here's how I do it in VBA:
after declaring variables I put everything in variables to make it easy to modify the program for other accounts.

Public Const RingCentralTokenURI = " https://platform.ringcentral.com/restapi/oauth/token "
Public Const RingCentralDevTokenURI = " https://platform.devtest.ringcentral.com/restapi/oauth/token "

Public Const RingCentralFaxURI = " https://platform.ringcentral.com/restapi/v1.0/account/~/extension/~/fax "
Public Const RingCentralDevFaxURI = " https://platform.devtest.ringcentral.com/restapi/v1.0/account/~/extension/~/fax "

Public Const RingCentralSMSURI = " https://platform.ringcentral.com/restapi/v1.0/account/~/extension/~/sms "
Public Const RingCentralDevSMSURI = " https://platform.devtest.ringcentral.com/restapi/v1.0/account/~/extension/~/sms "

Public Const RingCentralGetSMSURI = " https://platform.ringcentral.com/restapi/v1.0/account/~/extension/~/message-store "
Public Const RingCentralDevGetSMSURI = " https://platform.devtest.ringcentral.com/restapi/v1.0/account/~/extension/~/message-store "

Public Const AppKey = "xxxxxxxxxxxxxx"
Public Const AppSecret = "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy"
Public Const LoginUserName = "+1234567890"
Public Const LoginPassword = "mypassword"

Now 3 functions are needed
1- to encode the app key and secret in Base 64 (this is one I got online. Please keep the author information to give them credit)
2- to obtain the Access Token (the next two are mine so you are welcome to them)
3- to send the SMS
__________________________
Access Token:

Public Function http_GetAccessToken() As String

    Dim x As String
    Dim httpRequest As New MSXML2.XMLHTTP60
    Dim Payload As String
    Dim s64 As String
    s64 = "Basic " & Base64Encode(AppKey & ":" & AppSecret) & """"

    Payload = "grant_type=password&username=" & LoginUserName & "&extension=1&" & "password=" & LoginPassword
     httpRequest.Open "POST", RingCentralTokenURI, False
    httpRequest.setRequestHeader "Authorization", s64
    httpRequest.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    httpRequest.Send Payload
    AccessToken = "Bearer " & Mid(Mid(httpRequest.responseText, InStr(1, httpRequest.responseText, """Access_Token"" : """) + 18), _
        1, InStr(1, Mid(httpRequest.responseText, InStr(1, httpRequest.responseText, """Access_Token"" : """) + 18), """,") - 1)

End Function
________________________
Send SMS:

Public Function http_SendSMS(sToPhone As String, sMsg As String) As String

    httpRequest.Open "POST", RingCentralSMSURI, False
   
    Dim Payload As String
    Payload = "{" & _
        """to"": [{""phoneNumber"": """ & sToPhone & """}]," & _
        """from"": {""phoneNumber"": """ & LoginUserName & """}," & _
        """text"": """ & sMsg & """" & _
        "}"
  
    httpRequest.setRequestHeader "Authorization", AccessToken 'http_GetAccessToken()
    httpRequest.setRequestHeader "Accept", "application/json"
    httpRequest.setRequestHeader "Content-Length", "323"
    httpRequest.setRequestHeader "Content-Type", "application/json"
    httpRequest.Send Payload
    ConvID = Mid(httpRequest.responseText, InStr(1, httpRequest.responseText, """conversationid""") + 19, 19)
    MsgID = Mid(httpRequest.responseText, InStr(1, httpRequest.responseText, """id""") + 7, 12)

End Function
____________________________
Base 64 Encoder


Function Base64Encode(inData)

  'rfc1521
  '2001 Antonin Foller, Motobit Software, http://Motobit.cz
  Const Base64 = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/"
  Dim cOut, sOut, I
 
  'For each group of 3 bytes
  For I = 1 To Len(inData) Step 3
    Dim nGroup, pOut, sGroup
   
    'Create one long from this 3 bytes.
    nGroup = &H10000 * Asc(Mid(inData, I, 1)) + _
      &H100 * MyASC(Mid(inData, I + 1, 1)) + MyASC(Mid(inData, I + 2, 1))
   
    'Oct splits the long To 8 groups with 3 bits
    nGroup = Oct(nGroup)
   
    'Add leading zeros
    nGroup = String(8 - Len(nGroup), "0") & nGroup
   
    'Convert To base64
    pOut = Mid(Base64, CLng("&o" & Mid(nGroup, 1, 2)) + 1, 1) + _
      Mid(Base64, CLng("&o" & Mid(nGroup, 3, 2)) + 1, 1) + _
      Mid(Base64, CLng("&o" & Mid(nGroup, 5, 2)) + 1, 1) + _
      Mid(Base64, CLng("&o" & Mid(nGroup, 7, 2)) + 1, 1)
   
    'Add the part To OutPut string
    sOut = sOut + pOut
   
    'Add a new line For Each 76 chars In dest (76*3/4 = 57)
    'If (I + 2) Mod 57 = 0 Then sOut = sOut + vbCrLf
  Next
  Select Case Len(inData) Mod 3
    Case 1: '8 bit final
      sOut = Left(sOut, Len(sOut) - 2) + "=="
    Case 2: '16 bit final
      sOut = Left(sOut, Len(sOut) - 1) + "="
  End Select
  Base64Encode = sOut
End Function
_________________
Hope this helps

Vick Jacobson
CIO
Center for Family Health & Education,
Priority Care Medical Group,
Orchard Medical Center
1 comment
1 |3000

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

Andy Miscuk avatar image Andy Miscuk commented ·

Run-time error '-2147024809 (80070057)':

The Parameter is incorrect.

on this line httpRequest.Send Payload


If I leave out Payload

httpRequest.Send

Then no error...



0 Likes 0 ·
russ-boyd14089 avatar image
russ-boyd14089 answered
Thank you very much for the detailed response.  I look forward to working through your suggestions.  A couple of questions pop to mind after reading your code and reviewing the OAuth documentation on ringcentral:  What type of Authorization flow does your code provide?  Specifically, as a basic OAuth does the code expire in an hour and if so do you execute a refresh.  Let's say I'm running a look with a 10 minute delay between SMS output, would I need to do a Auth refresh before the hour?  I would assume that if the authorization expires the loop would fail the next time it tried to send an SMS?  Also, I noticed you have    httpRequest.setRequestHeader "Content-Length", "323"  Is this line required?  If so is the 323 of significance or should this be the actual length of the payload you are sending.  I tried it with and without this line and if seemed to work both ways but I didn't know if this line prevented some other problem?  Thanks again for your help as I struggle along to better understand this architecture. 
1 |3000

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

orchard-medical-center avatar image
orchard-medical-center answered Andy Miscuk commented
Yes, the Access Token provided by the code is valid for 60 minutes so you would have to either renew the code (using the function provided) or refresh it.  I didn't bother with refresh. My need was to send out appointment reminders so they all went out in less than an hour. I did however, have to build in a 2 second delay so as to not exceed the maximum allowed per minute.

As to the content length, I felt it best to stick with the format of the examples the Ring Central API documentation provided since I come from the world of assembly language and this was my first attempt at REST calls and I had no time to experiment.

HTH,
Vick

1 comment
1 |3000

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

Andy Miscuk avatar image Andy Miscuk commented ·

Are you still out there?

0 Likes 0 ·
Barney Stone avatar image
Barney Stone answered

Has anybody been able to get this to work? I can't get the http_GetAccessToken function to work. No matter what I try, I get the error message "Client authentication is required". Any help greatly appreciated.

,

Has anybody been able to get this to work? I can't get past the http_GetAccessToken function. No matter what I do, I get this response text:

{

"error" : "invalid_client",

"errors" : [ {

"errorCode" : "OAU-123",

"message" : "Client authentication is required"

} ],

"error_description" : "Client authentication is required"

}

I've tried the LoginUserName phone number with leading +1, just 1, and just the phone number. I'm using the Client ID for the App Key (the confusion over terminology doesn't help any). Tried other variations, too. Can't get anything to work.

Any help would be really appreciated!

I would be glad to pay for some consulting time with anyone who can send and receive SMS via VBA.

1 |3000

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

Barney Stone avatar image
Barney Stone answered

(Sorry for the duplicate messages. I thought the first one did not go thru.)

1 |3000

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

Master Admin avatar image
Master Admin answered Phong Vu commented

Were you unable to use the code I posted in this thread? Here's a simplified version. Copy and paste it into your program. First make sure to first put the correct strings in AppKey, AppSecret, LoginUserName and LoginPassword, then call http_GetAccesstoken


Public Const RCTokenURI = "https://platform.ringcentral.com/restapi/oauth/token ""
Public Const RCSMSURI = "https://platform.ringcentral.com/restapi/v1.0/account/~/extension/~/sms "
Public Const RCGetSMSURI = "https://platform.ringcentral.com/restapi/v1.0/account/~/extension/~/message-store "
Public Const AppKey = "put your app key here"
Public Const AppSecret = "put your app secret here "
Public Const LoginUserName = "%2B18185551212" 'Change 8185551212 to your phone number
Public Const LoginPassword = "password"

Public Function http_GetAccessToken() As String
Dim httpRequest As New MSXML2.XMLHTTP60
Dim Payload As String
Dim sMyConv As String
Payload = "grant_type=password&username=" & LoginUserName & "&extension=52199&" & "password=" & LoginPassword
httpRequest.Open "POST", RCTokenURI, False
httpRequest.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
sMyConv = "Basic " & EncodeBase64(AppKey & ":" & AppSecret) & "=="
httpRequest.setRequestHeader "Authorization", sMyConv
httpRequest.send Payload
Access_Token = "Bearer " & Mid(Mid(httpRequest.responseText, InStr(1, httpRequest.responseText, """access_token"" : """) + 18), _
1, InStr(1, Mid(httpRequest.responseText, InStr(1, httpRequest.responseText, """access_token"" : """) + 18), """,") - 1)
Debug.Print Access_Token
End Function

Function EncodeBase64(text As String) As String
Dim arrData() As Byte
arrData = StrConv(text, vbFromUnicode)
Dim objXML As MSXML2.DOMDocument60
Dim objNode As MSXML2.IXMLDOMElement
Set objXML = New MSXML2.DOMDocument60
Set objNode = objXML.createElement("b64")
objNode.DataType = "bin.base64"
objNode.nodeTypedValue = arrData
EncodeBase64 = objNode.text
EncodeBase64 = Replace(EncodeBase64, vbLf, "")
Set objNode = Nothing
Set objXML = Nothing
End Function

,

Were you unable to use the code I posted in this thread? Here's a simplified version, just copy and paste it in your VBA code. First make sure to put the correct strings in AppKey, AppSecret, LoginUserName and LoginPassword then call the http_GetAccessToken function.


Public Const RCTokenURI = "https://platform.ringcentral.com/restapi/oauth/token ""
Public Const RCSMSURI = "https://platform.ringcentral.com/restapi/v1.0/account/~/extension/~/sms "
Public Const RCGetSMSURI = "https://platform.ringcentral.com/restapi/v1.0/account/~/extension/~/message-store "
Public Const AppKey = "put your app key here"
Public Const AppSecret = "put your app secret here "
Public Const LoginUserName = "%2B18185551212" 'Change 8185551212 to your phone number
Public Const LoginPassword = "password"

Public Function http_GetAccessToken() As String
Dim httpRequest As New MSXML2.XMLHTTP60
Dim Payload As String
Dim sMyConv As String
Payload = "grant_type=password&username=" & LoginUserName & "&extension=52199&" & "password=" & LoginPassword
httpRequest.Open "POST", RCTokenURI, False
httpRequest.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
sMyConv = "Basic " & EncodeBase64(AppKey & ":" & AppSecret) & "=="
httpRequest.setRequestHeader "Authorization", sMyConv
httpRequest.send Payload
Access_Token = "Bearer " & Mid(Mid(httpRequest.responseText, InStr(1, httpRequest.responseText, """access_token"" : """) + 18), _
1, InStr(1, Mid(httpRequest.responseText, InStr(1, httpRequest.responseText, """access_token"" : """) + 18), """,") - 1)
Debug.Print Access_Token
End Function

Function EncodeBase64(text As String) As String
Dim arrData() As Byte
arrData = StrConv(text, vbFromUnicode)
Dim objXML As MSXML2.DOMDocument60
Dim objNode As MSXML2.IXMLDOMElement
Set objXML = New MSXML2.DOMDocument60
Set objNode = objXML.createElement("b64")
objNode.DataType = "bin.base64"
objNode.nodeTypedValue = arrData
EncodeBase64 = objNode.text
EncodeBase64 = Replace(EncodeBase64, vbLf, "")
Set objNode = Nothing
Set objXML = Nothing
End Function

1 comment
1 |3000

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

Phong Vu avatar image Phong Vu ♦♦ commented ·

Thanks @Master Admin for sharing your experience with other developers!

@Barney Stone if your app is still in the sandbox, change the platform server to

Public Const RCTokenURI = "https://platform.devtest.ringcentral.com/restapi/oauth/token ""
Public Const RCSMSURI = "https://platform.devtest.ringcentral.com/restapi/v1.0/account/~/extension/~/sms "
Public Const RCGetSMSURI = "https://platform.devtest.ringcentral.com/restapi/v1.0/account/~/extension/~/message-store "


And make sure that your app authentication is "password flow" type (i.e. platform type "Server only - No UI")

0 Likes 0 ·
Barney Stone avatar image
Barney Stone answered

Took some additional tweaking, but I finally have this working. Thanks for your help! Now on to the next part of my project: receiving SMS messages. Fingers crossed!

1 |3000

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

Barney Stone avatar image
Barney Stone answered

Hmmm. I don't suppose you have any sample code for subscribing and pulling down incoming SMS messages, do you? I'm not a Web programmer, so it's hard for me to figure out what to do in VBA from your various examples.

1 |3000

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

Master Admin avatar image
Master Admin answered

This should help you get started. It dumps the responses into C:\Response.txt after which you can parse the parts you want to keep:

Dim sFileName As String
Dim sLogURI As String
Dim sMessageType As String
Dim sDateFrom As String
Dim sDateTo As String
Dim sDirection As String
Dim sPageNum As String
Dim sPerPage As String
Set httpRequest = CreateObject("MSXML2.ServerXMLHTTP")
' Prepare string needed to submit to Ring Central servers for downloading replies from the last 3 days
sMessageType = "SMS&"
sDateFrom = Format(Date - 3, "yyyy-mm-dd") & "T" & "00:00:00.000Z&"
sDateTo = Format(Date + 1, "yyyy-mm-dd") & "T" & Format(DateAdd("h", -7, Now), "hh:mm:ss") & ".000Z&"
sDirection = "direction=Inbound&conversationId=&"
sPageNum = "page=1&"
sPerPage = "perPage=500"
sLogURI = Trim(RCGetSMSURI) & "?availibility=Alive&messageType=" & sMessageType & "dateFrom=" & sDateFrom & sConversationID & sDirection & sPageNum & sPerPage
' Get Access Token to Ring Central server and get replies
Call http_GetAccessToken
httpRequest.Open "GET", sLogURI, False
httpRequest.setRequestHeader "Transfer-Encoding", "chunked"
httpRequest.setRequestHeader "Authorization", Access_Token
httpRequest.setRequestHeader "Content-Type", "application/json; charset=UTF-8"
httpRequest.send
' Now dump replies to a text file in the working directory in order to parse the desired pieces from the returned text string
sFileName = "C:\Response.txt"
' Dump incoming stream to sFileName
Open sFileName For Output As #1
Print #1, Replace(httpRequest.responseText, ",", Chr(13)), Tab
Close #1

Hope this help,

Vick

1 |3000

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

Developer sandbox tools

Using the RingCentral Phone for Desktop, you can dial or receive test calls, send and receive test SMS or Fax messages in your sandbox environment.

Download RingCentral Phone for Desktop:

Tip: switch to the "sandbox mode" before logging in the app:

  • On MacOS: press "fn + command + f2" keys
  • On Windows: press "Ctrl + F2" keys