The First Step

Question

Sending SMS from excel VBA

Forum|Forum|7 years ago
February 13, 2019
13 replies
4677 views

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

R

russ-boyd

Author

The First Step

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

O

orchard-medical-center

The First Step

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

R

russ-boyd

Author

The First Step

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.

O

orchard-medical-center

The First Step

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

B

barneystone26

The First Step

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.

B

barneystone26

The First Step

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

M

mike-2

New Participant

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

PhongVu

Community Manager

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")

B

barneystone26

The First Step

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!

B

barneystone26

The First Step

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.

Show more replies

Sign up

Login with SSO

Login to the community

Login with SSO