How to send SMS through Excel VBA?

Sending SMS messages programmatically can be very useful for automating notifications and alerts based on data in Excel. Excel VBA provides several ways to integrate with SMS messaging services to send text messages directly from macros and VBA code.

In this article, we will explore a step-by-step process for setting up SMS messaging in Excel using VBA. We will cover:

• Choosing an SMS provider
• Getting an API key
• Adding a reference to the SMS provider’s library
• Sending a test SMS message
• Integrating real Excel data and triggers

With some simple VBA code, you can build robust SMS messaging capabilities into your Excel workbooks and spreadsheets. Let’s get started!

Choosing an SMS Provider

The first step is selecting an SMS provider that offers a REST API for programmatic access. There are many services to choose from, including:

• Twilio
• Clickatell
• Nexmo
• Plivo
• AWS SNS
• MessageBird

For this tutorial, we will use Twilio because they offer a generous free tier for testing and development. However, the general process will be similar for any SMS provider.

When comparing options, here are some factors to consider:

• Pricing structure (per message, monthly plan, etc.)
• Coverage for the countries/regions you need to send SMS to
• Reliability and uptime of service
• Ease of use for their API
• Supported features like two-factor auth, short codes, etc.

Make sure to review the details for your top choices to ensure they will meet your needs.

Getting an API Key

Once you have selected an SMS provider, you will need to signup for a developer account to access their API. This usually involves:

1. Going to the provider’s developer portal and creating a new account
2. Selecting the products/APIs you want to use (usually SMS and voice)
3. Verifying your identity with credit card and/or phone number
4. Navigating to the dashboard to get your Account SID and Auth Token

The SID and Auth Token act as your unique API credentials for accessing that provider. Treat them like sensitive passwords and do not share or expose them publicly.

For example, in Twilio these credentials are labeled:

• Account SID – Primary ID for your account
• Auth Token – Used for authentication when making API calls

Save these somewhere secure like a password manager. We will need them later for making API requests.

Adding a Reference to the SMS Library

Now we are ready to set up our Excel VBA project.

First, launch the VBA editor in Excel by going to Developer > Visual Basic. This opens the IDE where you can create modules, classes, etc.

Next, we need to add a reference to the library for the SMS provider’s API:

1. In the VBA editor go to Tools > References
2. Find and check the box next to the appropriate library
3. For Twilio this would be Twilio API
4. Click OK to add the reference

This makes all the objects and methods in that library available to call from VBA code.

Sending a Test SMS Message

Let’s start by sending a test SMS message to verify our setup is working properly.

Add a new VBA module and paste the following code:

Sub SendTestSMS()
  
  Const accountSID = "ACxxxxxxxxxxxxxxx" ' Your account SID
  Const authToken = "xxxxxxxxxxxxxxxxx" ' Your auth token
  
  Dim client As New TwilioApi.TwilioRestClient(accountSID, authToken)
  
  Dim message As New TwilioApi.TwilioSMSMessage

  message.FromNumber = "+15555555555" ' Your Twilio number
  message.ToNumber = "+1234567890" ' Who to send the message to 
  message.Body = "This is a test SMS from Excel VBA!"

  client.SendSms(message)
  
End Sub

Make sure to use your real Account SID, Auth Token, From/To phone numbers.

Then call the `SendTestSMS` subroutine. This will use the Twilio API client to send an SMS containing the text we specified.

Check your phone – you should receive the test message!

Integrating Real Excel Data

Now that we have SMS sending configured, we can start to integrate real Excel data and triggers.

For example, let’s set up a message when a cell changes:

Sub SendSMSOnCellChange()

  Const accountSID = "ACxxxxxxxxxxxxxxx"
  Const authToken = "xxxxxxxxxxxxxxxxx"

  Dim client As New TwilioApi.TwilioRestClient(accountSID, authToken)
  
  Dim message As New TwilioApi.TwilioSMSMessage
  
  message.FromNumber = "+15555555555" 
  message.ToNumber = "+1234567890"

  message.Body = "Cell A1 just changed to: " & Range("A1").Value

  client.SendSms(message)

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$A$1" Then
    Call SendSMSOnCellChange
  End If
End Sub

Here we hook the Worksheet_Change event handler, check if cell A1 was changed, and if so call our SMS sending routine.

You can integrate SMS alerts for just about any Excel event or data condition this way.

Some other examples:

• When a certain worksheet value exceeds a threshold
• On edits to important cells
• When new data arrives from external sources
• Scheduled messages on a timeline

The possibilities are endless!

Two-Factor Authentication

Another useful application of SMS messaging in Excel is setting up two-factor authentication (2FA). This adds an extra layer of security before running sensitive macros.

Here is a simple example flow:

1. User starts macro execution that needs 2FA
2. VBA code generates random 6 digit number
3. This verification code is sent via SMS text message
4. Macro presents input box to user to enter code
5. If code matches, continue execution. Else halt.

To implement this:

Sub RunSecureMacro()

  Dim verificationCode As String
  verificationCode = Generate6DigitCode()
  
  'Send verification code SMS
  SendVerificationCodeSMS(verificationCode) 
  
  'Get user input
  Dim userInput As String
  userInput = InputBox("Enter verification code:")
  
  'Compare
  If userInput = verificationCode Then
    'Continue execution
  Else
    MsgBox "Invalid verification code. Halting macro."
    Exit Sub
  End If

  'Rest of macro logic  

End Sub

Function Generate6DigitCode() As String

  'Return 6 random digits
  Generate6DigitCode = VBA.Strings.Left(VBA.Format(VBA.rnd * 1000000, "0000000"), 6) 

End Function

Sub SendVerificationCodeSMS(code As String)
  
  'SMS logic to send code
  
End Sub

This kind of 2FA can prevent unauthorized persons from executing macros and provides an extra layer of auditability.

Error Handling

It is always a good idea to use proper error handling when integrating with external services like SMS messaging.

Some common issues to anticipate:

• Network communication failures
• Authorization errors (invalid credentials)
• Errors applying charges (prepaid account balance exceeded)
• SMS delivery failures

Wrap your API calls in Try/Catch blocks and handle different types of errors appropriately:

Sub SendSMS()

  Const accountSID = "ACxxxxxxxxxxxxxxx" 
  Const authToken = "xxxxxxxxxxxxxxxxx"

  On Error GoTo ERR_HANDLER

  Dim client As New TwilioApi.TwilioRestClient(accountSID, authToken)

  Dim message As New TwilioApi.TwilioSMSMessage

  message.FromNumber = "+15555555555"
  message.ToNumber = "+1234567890"
  message.Body = "Test SMS"

  Try
  
      client.SendSms(message)
  
  Catch ex As TwilioApi.TwilioRestException
  
      MsgBox("Error: " & ex.Message)
      Exit Sub

  End Try

  On Error GoTo 0
  Exit Sub

ERR_HANDLER:

  Select Case Err.Number

    Case xx ' List of error codes

      ' Handle specific error types

    Case Else

      MsgBox Err.Description
      Exit Sub 
      
  End Select

End Sub

This ensures that errors are displayed to the user cleanly versus showing a default VBA error screen.

Conclusion

By leveraging the power of VBA and SMS provider APIs, you can integrate robust SMS messaging capabilities directly into your Excel workbooks. Whether it is for alerts, notifications, two-factor authentication or more, the possibilities are endless.

The key steps we covered were:

1. Choosing an SMS provider
2. Getting an API key
3. Adding the library reference in VBA
4. Coding the logic to send messages
5. Integrating real Excel data triggers
6. Handling errors gracefully

With SMS messaging, you can take your Excel applications to the next level and open up many new possibilities for end-user functionality.

So get out there, be creative, and automate all the things with VBA and SMS!