Sending SMS from excel VBA

  • 1
  • Question
  • Updated 2 months ago
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


Photo of Russ Boyd

Russ Boyd

  • 90 Points 75 badge 2x thumb

Posted 2 months ago

  • 1
Photo of Russ Boyd

Russ Boyd

  • 90 Points 75 badge 2x thumb
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
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
(Edited)
Photo of Russ Boyd

Russ Boyd

  • 90 Points 75 badge 2x thumb
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. 
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