If you’ve used Windows like I have for the last 20 years, likely some of the most familiar pieces of software for you are Excel and Access. And even though these two pieces of software have thousands of features, to date apparently no one at Microsoft has ever thought to put a phone in them. Well today that changes.
For better or worse, Microsoft Excel and Access are the backbone that power many organizational processes in companies big and small. Excel alone has been know to power the financial models of many businesses, and even entire countries. The applications have won this honored place in the hearts and minds of business workers in part because both applications are highly customizable through multiple means, one of which is by creating custom logic using Visual Basic for Applications (VBA), an implementation of the venerable Visual Basic language. Extending Office using VBA is popular because it is approachable by non-developers, easy to learn, and offers a way to quickly to add custom logic to Office applications.
Send SMS Messages from Excel
One of the most common uses for Excel as a simple flat database. Its trivial to create a new spreadsheet, type in your column headers and start making lists of stuff, so let’s use that scenario to demonstrate how easy it is to use VBA to interact with the Twilio API.
Let’s pretend for a moment that I am the coach for a youth soccer team. To keep track of the kids on the team, their parents and their contact information, I’ve created a new spreadsheet in Excel.
Since the spreadsheet already contains all of the contact info for the players’ parents, it would be great if I could just send SMS messages to the parents for things like canceled practices and game reminders right from within Excel. To do that I need to extend the spreadsheet.
I’ll start by creating some UI that lets me initiate the process of sending a text message. Excel 2007 and later let me add new elements to the ribbon like tabs, groups and buttons, and then listen for events from those elements in my VBA code. So I’ll add a new button that lets me send text messages.
The easiest way to add elements to the ribbon is by using the Office Custom UI Editor, which I downloaded here.
This application lets me define new elements using the ribbons UI XML schema. Below is the XML I created to add a new tab, group and button to the ribbon:
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui"> <ribbon> <tabs> <tab id="customTab" label="TWILIOSO" insertAfterMso="TabHome"> <group id="customGroup" label="Messages"> <button id="customButton1" label="Send SMS" size="large" onAction="conSendSms" imageMso="twiliologo" /> </group> </tab> </tabs> </ribbon> </customUI>
The most important attribute to point out in the XML is the onAction attribute located on the button element. This attribute is how you tell Excel which VBA subroutine to call when the button is clicked. In my case I’ve told Excel that when the button is clicked I want to execute a subroutine named conSendSms, which I’ll write in just a bit.
When I save the spreadsheet in the custom UI editor it saves the XML directly in the file, so when I reopen the spreadsheet in Excel I now see that there is a new tab named TWILIOSO that contains a single group, which itself contains a single button.
Now I need write the code to that will actually send my SMS messages.
To open the VBA editor in Excel, press Alt+F11, or open the DEVELOPER tab and click the Visual Basic button, which you can do by customizing the ribbon you can make the tab visible:
I’ll start coding in the editor by adding a new module to the project and then adding the subroutine that will handle my new ribbon button being clicked:
Public Sub conSendSms(control As IRibbonControl) MsgBox "Hello World" End Sub
To test this I can go back to Excel and click my Send SMS button. The message box will appear. Pretty simple, huh?
Next, I want to create a custom dialog that will let me specify the message I want to send. To do this I’ll create a new User Form in my project and then added a textbox and two buttons: Send and Cancel.
When I click the Cancel button I want to close the dialog (obviously) and when I click the Send button I want to send SMS messages.
In order to tell my VBA code who to send messages to, I’ll create a Selection by selecting a set of cells in my spreadsheet. Now in the Send buttons click event, I can loop over each of the selected rows in the Selection:
Private Sub btnSend_Click() Dim i As Integer For i = 1 To Selection.Rows.Count ' Send a text message here Next i Me.Hide End Sub
Finally, I need to have my VBA code talk to the Twilio REST API to tell it to send the SMS messages. Using the Twilio API means making an HTTP request to the proper Twilio URL and providing the required parameters: a from phone number, a to phone number and a message.
To make the HTTP request, I’m going to use the MSXML2.XMLHTTP60 object which is included in the Microsoft XML 6.0 library, so I need to reference it in the project:
Once the XML library referenced, I’ll use the libraries APIs to create and send an HTTP request to Twilio inside of a function named SendSms. When I send the request I need to make sure I configuring it to authenticate with my Twilio credentials (which I can get from my Twilio account dashboard) and to send the require parameters in the request body:
Function SendSMS(fromNumber As String, toNumber As String, body As String) Dim SmsUrl As String On Error GoTo Error_Handler ' setup the URL SmsUrl = BASEURL & "/2010-04-01/Accounts/" & ACCOUNTSID & "/SMS/Messages" ' setup the request and authorization Dim http As MSXML2.XMLHTTP60 Set http = New MSXML2.XMLHTTP60 http.Open "POST", SmsUrl, False, ACCOUNTSID, AUTHTOKEN http.setRequestHeader "Content-Type", "application/x-www-form-urlencoded" Dim postData As String postData = "From=" & fromNumber _ & "&To=" & toNumber _ & "&Body=" & body ' send the POST data http.send postData ' optionally write out the response if you need to check if it worked Debug.Print http.responseText If http.Status = 201 Then ElseIf http.Status = 400 Then MsgBox "Failed with error# " & _ http.Status & _ " " & http.statusText & vbCrLf & vbCrLf ElseIf http.Status = 401 Then MsgBox "Failed with error# " & http.Status & _ " " & http.statusText & vbCrLf & vbCrLf Else MsgBox "Failed with error# " & http.Status & _ " " & http.statusText End If Exit_Procedure: On Error Resume Next ' clean up Set http = Nothing Exit Function Error_Handler: Select Case Err.Number Case NOINTERNETAVAILABLE MsgBox "Connection to the internet cannot be made or " & _ "Twilio website address is wrong" Case Else MsgBox "Error: " & Err.Number & "; Description: " & Err.Description Resume Exit_Procedure Resume End Select End Function
Once the request is sent I can check the response sent from Twilio and provide the appropriate user feedback in case an error occurred.
Now all I need to do is update my loop to call the SendSms function, passing in the from, to and message parameters:
For i = 1 To Selection.Rows.Count SendSMS "+17045943984", Selection.Cells(i, CONTACTNUMBERCOLUMNINDEX), Me.txtMessage Next i
That’s all there is to it. A few lines of VBA code and I’m sending text messages right from Excel. Of course you could certainly take the time to make this more robust and generic, extending the dialog to allow you to specify the column containing the phone number (I simply hard coded it in my sample), or adding code that allows you to merge other cells from the spreadsheet dynamically into your message.
Feel free to download the spreadsheet I created for this post and play with the code yourself.
Sending SMS Messages Using Access
Sending text messages from Access is a bit different, mostly because Access has, for many versions, had the concept of Forms built right into it. Access Forms allow you to develop fairly complex applications right within Access and and make it trivial to access the data in the underlying database. I’ll show you how you can send SMS messages right from Access by using the same soccer coach scenario described in the previous section.
To start I’ve created a simple database containing two tables: Teams and Players and then dropped in the same data I had in my Excel spreadsheet. I created a relationship between Teams and Players that will allow me to associate a set of players with a specific team:
Next, I created a new query named Roster that returns an INNER JOIN between the Teams and Players tables, providing me with a single list which I will use as the data for my form.
Now that I’ve got the basic database setup, I can create the form that I will use to send the messages. Unlike Excel where I had to add a button to the Ribbon in order to show my dialog, Access already had Forms built into it so I can add a new Dialog form and add some controls to it.
Like in my Excel dialog, I added a text area where I can type my message and two buttons to close the dialog, or send the messages. And also similar to my form in Excel, when I click the OK button on my Access form, it loops over a set of records and sends an SMS for each.
In Access however, since the underlying data is a traditional SQL database, instead of needing to select a set of cells to loop over, I can simply run the Roster query I created earlier and loop over the records returned from the query.
Private Sub btnOK_Click() Dim msg As String msg = Me.txtMessage.Value Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQL As String Set db = CurrentDb Dim qdf As QueryDef Set qdf = CurrentDb.QueryDefs("Roster") Set roster = qdf.OpenRecordset() roster.MoveFirst Do While roster.EOF = False SendSMS "+17045943984", roster![ContactNumber], msg roster.MoveNext Loop End Sub
I can access the fields in each record by specifying the query name and then the field name:
Finally, I’ll add the reference to the Microsoft XML 6.0 library and then drop into my form the same SendSms subroutine that I created for the Excel sample, and now I’m sending SMS messages from Access.
And like with the previous example, I highly encourage you to download and check out the sample Access database I created for this post.
Making Phone Calls From Access
So now that I’ve shown you how easy it is to send text messages from Excel and Access, lets take a look at how I can change the code to have Twilio initiate an outbound phone call to each of my players and let them know that practice will be canceled because of bad weather.
To do this I have to make two changes to my code. First, I need to change the Twilio URL that I’m making my HTTP request to. The URL I use to tell Twilio initiate phone calls is different than the URL I have been using to tell Twilio to send SMS messages.
CallUrl = BASEURL & "/2010-04-01/Accounts/" & ACCOUNTSID & "/Calls"
This new URL also requires a different set of parameters be sent with it. Where sending an SMS requires the from, to, and message parameters, initiating an outbound call requires the from, to and url parameters.
This takes me to the second change I need to make. I need to create a URL that Twilio can request to get instructions on how to proceed when the call is answered. We call those instructions TwiML. To make it super simple to give the those instructions to Twilio, I’m going to use the Simple Message Twimlet service to generate the TwiML for me.
The Simple Message Twimlet knows how to accept a string of text as a URL parameter and convert that string into TwiML that uses the verb. When the generated TwiML is returned to Twilio, Twilio will convert the text inside of the verb into a voice, which the person who answers the call can hear.
The URL below shows how to send the message I want the caller to hear to the Twimlet. You can see the TwiML the Twimlet generates by clicking the link.
<Response> <Say>Due to inclement weather, today's practice has been canceled.</Say> </Response>
I’ll take the Twimlet URL and pass that as the value of the url parameter in my HTTP request to Twilio.
Now when I execute my Dialog in Access, Twilio will make a call to each player’s phone number and tell them that tonight’s practice has been canceled due to weather.
And there you have it. Turning two of the most widely used pieces of software on the plane, Excel and Access, into phones is as simple as a bit of VBA code. Immensely powerful all by themselves, the ability to extend these applications with the Twilio API makes them even more so.
In this post I showed you how, using a little bit of VBA code in both Excel and Access along with the Twilio API, its trivial to both send SMS messages and initiate outbound phone calls directly from within them.