Text Messages – Easy Peasy

We recently released a feature that allows our users receive a text message if a bus they are awaiting is running late. It’s all pretty simple, we take the output of this form and run a little excel macro to convert the entered data into email addresses. We then email the affected parties and they get the the alert via SMS. There’s a couple hard-coded values in this macro for simplicity, modify it to suit your needs:

'Transform cell phone numbers to email addresses for sending text messages
'Boris Masis 08/19/2009
 
Option Explicit
 
Sub ProcessTextMessaging()
Dim emailDomain
Range("L1").Value = "Email"
Range("C2").Select
 
    'Loop though until we run out of rows
    Do Until ActiveCell.Value = ""
 
        Select Case ActiveCell.Value
 
              Case "Alltel"
                  emailDomain = "@message.alltel.com"
 
              Case "AT&T"
                  emailDomain = "@txt.att.net"
 
              Case "Boost"
                  emailDomain = "@myboostmobile.com"
 
              Case "Cricket"
                  emailDomain = "@sms.mycricket.com"
 
              Case "MetroPCS"
                  emailDomain = "@mymetropcs.com"
 
              Case "Sprint"
                  emailDomain = "@messaging.sprintpcs.com"
 
              Case "Nextel (Sprint)"
                  emailDomain = "@page.nextel.com"
 
              Case "T-Mobile"
                  emailDomain = "@tmomail.net"
 
              Case "Tracfone"
                  emailDomain = "@mmst5.tracfone.com"
 
              Case "US Cellular"
                  emailDomain = "@email.uscc.net"
 
              Case "Verizon"
                  emailDomain = "@vtext.com"
 
              Case "Virgin"
                  emailDomain = "@vmobl.com"
 
        End Select
 
        'write the email to a new cell
        ActiveCell.Offset(0, 9).Value = ActiveCell.Offset(0, -1) & emailDomain & ","
 
        'select the next cell down
        ActiveCell.Offset(1, 0).Select
 
    Loop
 
End Sub

The providers above have over 1 million U.S. subscribers, and almost all providers have email to SMS gateways.

You can download my xls file that includes this macro, or the .xla file which is an Excel add-in that will add a “Process Text Messaging” section to your Tools Menu. I followed the template at http://www.cpearson.com/excel/CreateAddIn.aspx to create this add-in.