Getting Started Knowledge Base Community User Manuals Ideas

News & Announcements User Community Developer Community

Welcome to the RingCentral Community

Make sure to review our Terms of Use and Community Guidelines.

Please note the community is currently under maintenance and is read-only.

Home » Developers

Sending SMS from excel VBA

Tags: sms

Feb 13, 2019 at 2:44pm • 12 replies • 0 likes

russ-boyd

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

12 Answers

Barney Stone answered on Jun 11, 2021 at 11:49am

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!

Barney Stone answered on May 18, 2021 at 1:53pm

Beautiful! Many thanks for a fast and helpful response.

Master Admin answered on May 18, 2021 at 11:00am

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

Master Admin answered on May 18, 2021 at 9:03am

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.

Phong Vu on May 18, 2021 at 9:16am • 0 likes

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

Barney Stone answered on May 18, 2021 at 10:41am

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.

Barney Stone answered on May 18, 2021 at 10:30am

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!

Barney Stone answered on May 18, 2021 at 8:46am

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

Barney Stone answered on May 18, 2021 at 8:45am

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.

orchard-medical-center answered on Feb 14, 2019 at 5:29am

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

russ-boyd answered on Feb 13, 2019 at 4:46pm

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.

orchard-medical-center answered on Feb 13, 2019 at 3:22pm

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

russ-boyd answered on Feb 13, 2019 at 3:15pm

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

A new Community is coming to RingCentral!

Posts are currently read-only as we transition into our new platform.

We thank you for your patience
during this downtime.

Try Workflow Builder

Did you know you can easily automate tasks like responding to SMS, team messages, and more? Plus it's included with RingCentral Video and RingEX plans!

Try RingCentral Workflow Builder

PRODUCTS
RingEX
Message
Video
Phone

OPEN ECOSYSTEM
Developer Platform
APIs
Integrated Apps
App Gallery
Developer support
Games and rewards

RESOURCES
Resource center
Blog
Product Releases
Accessibility

QUICK LINKS
App Download
RingCentral App login
Admin Portal Login
Contact Sales