Call Your Spreadsheet, Text Your Database – Using Twilio From Excel and Access

Time to read: 8 minutes

August 14, 2013
Written by
Devin Rader
Twilion

Devin Rader

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.

In this post I’ll show you how you can use VBA to extend both Excel and Access, letting you send SMS messages and make phone calls using the Twilio API right from inside those apps.

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.

1

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.

Office Custom Tool

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:

Visual Basic Editor

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.

Visual Basic Form

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:

vb

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:

6.png

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.

Screen Shot 2013-08-14 at 10.43.20 AM

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:

roster![ContactNumber]

 

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.

Conclusion

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.

You can download the Excel and Access files I used in the demos in this post, and of course if you have any questions or comments, please hit me up on twitter or email.

Related Posts

Related Resources

A newspaper article

Twilio Docs

From APIs to SDKs to sample apps

API reference documentation, SDKs, helper libraries, quickstarts, and tutorials for your language and platform.
An Open book

Resource Center

The latest ebooks, industry reports, and webinars

Learn from customer engagement experts to improve your own communication.
User group reactions

Ahoy

Twilio's developer community hub

Best practices, code samples, and inspiration to build communications and digital engagement experiences.