Given the huge interest in the recent Google Spreadsheet TXTmerge example I thought a less intensive Microsoft Excel example might interest some people.  Excel is the industry standard for desktop spreadsheets and is so powerful many people do not use 10% of what it can really do.  This example uses very basic VBA to iterate through a range of data sending a TXT message for each line with the message content/template and user credentials configured on another page.  It is not meant to be a full working example and has room for making it more dynamic and easy to use.  It is meant to show you how simple (relatively) it is to use Excel as middle-ware to send thousands of customised TXT messages via our simple Bulletin TXT API.

Before you start you should have a basic understanding of VBA and Excel macros.  Obviously you need to have excel and have macros enabled in the spreadsheet you are using.  If I have lost you already or you do not know what a macro is then check out this video of a raven snow boarding.  Everyone else can carry on.

The VBA code is below and can be copied and saved in a macro module of your workbook.

[cc lang="vb" lines="30" escaped="true" width="700px" line_numbers="on"]

Sub sendTXT()
‘ get settings
strMessageTemplate = Worksheets(“settings”).range(“B1″).Value
strUserId = Worksheets(“settings”).range(“B2″).Value
strUserPassword = Worksheets(“settings”).range(“B3″).Value

‘ get data
Worksheets(“data”).Activate

Worksheets(“data”).range(“A2″).Select
‘ Set Do loop to stop when an empty cell is reached.
Do Until IsEmpty(ActiveCell)

strMobileNumber = ActiveCell.Text
strName = ActiveCell.Offset(0, 1).Text

‘ Replace upto 6 merge fields in template
messageContent = strMessageTemplate
messageContent = Replace(messageContent, “{{field0}}”, ActiveCell.Offset(0, 0).Text)
messageContent = Replace(messageContent, “{{field1}}”, ActiveCell.Offset(0, 1).Text)
messageContent = Replace(messageContent, “{{field2}}”, ActiveCell.Offset(0, 2).Text)
messageContent = Replace(messageContent, “{{field3}}”, ActiveCell.Offset(0, 3).Text)
messageContent = Replace(messageContent, “{{field4}}”, ActiveCell.Offset(0, 4).Text)
messageContent = Replace(messageContent, “{{field5}}”, ActiveCell.Offset(0, 5).Text)
messageContent = Replace(messageContent, “{{field6}}”, ActiveCell.Offset(0, 6).Text)

‘ Concatenate the URL together
strURL = “https://www.bulletinmessenger.net/api/3/sms/out?” _
& “userId=” + strUserId _
& “&password=” + strUserPassword _
& “&to=” + strMobileNumber _
& “&body=” + messageContent
‘ MsgBox strURL
Set Service = CreateObject(“msxml2.xmlhttp”)

‘create HTTP request to query URL – make sure to have
‘that last “False” there for synchronous operation
Service.Open “GET”, strURL, False
Service.send

‘ Get the API Status
strResponseCode = Service.Status
If strResponseCode = 200 Then
strResponseCode = “OK”
End If

‘ Update Data with status code
ActiveCell.Offset(0, 6) = strResponseCode
Service = False

‘ Step down 1 row from present location.
ActiveCell.Offset(1, 0).Select
Loop
MsgBox “Done”
End Sub

[/cc]

The key parts to remember are

  1. The workbook must contain two(2) sheets, called
    1. Data – where your data is stored

        1. The first row of the data must be row 2 of the Datapage.
          1. The data can contain a header row in row 1
          2. The data can contain upto 6 columns
          3. The mobile number must be in column A

       

    2. Settings – where your message and credentials are stored

      1. The message content is stored in cell B1 of the Settings page
      2. Messenger ID is in cell B2 of the Settings page
      3. Messenger Password is in cell B3 of the Settings page.

The VBA code itself can be added via the Macro Menu. Open, copy the code above and paste into the window.  Simple.

I will leave it to the developer in you to overcomplicate things but this simple example will allow you to quickly merge your CRM data into an SMS message using Bulletin Messenger.