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
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
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
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
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.
(Sorry for the duplicate messages. I thought the first one did not go thru.)
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
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")
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!
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.
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), "hhss") & ".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
Beautiful! Many thanks for a fast and helpful response.
OK, back to this project. I can now send and receive SMS messages using my VBA code. That's working great. Next, how can I download a file (typically an image) that was attached to an incoming SMS message? I can see information about the attachment in the response when I download the message, including the uri, file type, etc. What do I have to do to download that file? Thanks in advance for any help!
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.