Making Business Easier with Email to SMS – Part 2

To continue the post from last week, we’ll be looking at how we can integrate email to SMS, also known as the SMTP API, with Windows for efficient (and automated) SMS messaging.

We ended last week with a mail merge between a database and Microsoft Word that pulled in values from the database into fields specified in Microsoft Word.

Here’s that process in a very brief format:

Define your Word document template that will be used for the mail merge. This document should be in the following format to ensure that email to SMS runs smoothly:

The field names above should correspond to those you have in your database. For the purpose of this post, a very simple access database was created:

Once you’ve got your template, it’s time to start the Mail Merge Wizard. Without spending too much time on it, here are the topics presented within the Wizard and the options selected:

• Select document type
  • Letters                  (…Next)

• Select starting document
  • Use the current document           (…Next)

• Select recipients
  • Use an existing list
    • Browse (…Next)

• Write your letter
  • More items (…Next)

• Preview your letters       (…Next)
• Complete the merge
  • Edit individual letters

(…Next)

(Map fields in your database to the corresponding fields in your template – you should end up with something like this: csv: «Debt_mnumber»|«Debt_name»|«Debt_amount»)    (…Next)

When all’s done you should end up with as many pages as there are contact, each page containing csv: «Debt_mnumber»|«Debt_name»|«Debt_amount» with all the values filled in. Here’s what my first page looked like: csv: 12345678|John|$323

Once that has been done, it’s time to save the file in an easily accessible folder. For the purposes of this post, our file was saved as Message.txt

Note: To ensure that your messages send correctly, it is important to save the file as a plain text file in the MS-DOS format. This is important because the SMTP API only access 7-bit messaging. Default Windows text files are 8 bits (or binary).

That being done, let’s open the directory where you saved your file. In that same folder, create another plain text file (we used notepad) containing all the necessary API details. Here’s what ours looks like:

api_id:xxxxxxx

user:username

password:secret

reply:reply@ddress.domain

text:Hi #field1#, your outstanding balance is #field2#. Please pay at the soonest convenience.

delimiter:|

One parameter that needs some explanation is the ‘reply’, which essentially specifies which email address any errors and/or message IDs should be sent to. The ‘text:’ parameter contains the message that will be sent using email to SMS, or part of that message anyway. As you can see #field1# and #field2# act as placeholders for the values from our mail merge.

Be careful: Where our mail merge produced «Debt_mnumber»|«Debt_name»|«Debt_amount», the email to SMS server will fill #field1# with «Debt_name» and #field2# with «Debt_amount». The leftmost field of your mail merge must always be the destination number.

All good?

Next up, save the file containing your email to SMS API details and close it. For the purpose of this post our file is called API.txt. Once that’s done, open notepad again and enter the following line into it: type Message.txt >> API.txt This file should be saved as Merge.bat (note the extention) and must be in the same folder as Message.txt and API.txt to avoid any errors.

At this point you can double-click Merge.bat and you’ll note that API.txt, when opened afterwards, contains both the API details and the content of the merged text file. The command specified in Merge.bat simply appends the content from Message.txt to API.txt, thereby making them one file.

Still with me? Almost done!

It’s time to create the final file. At this point it’s important to specify that, for the purposes of this post, we’re using a small third party application that allows you to send emails from the command line. The one we chose is called bmail.exe . The content of your new file will simply be the command needed to send API.txt to the messaging server. Here’s the content from our file:

bmail -s messaging.clickatell.com -h -t sms@messaging.clickatell.com -f your_address@your_domain.com -m API.txt –d

Here’s an explanation of all the switches above (from bmail.exe usage info):

-s    SMTP Server Name

-p    SMTP Port Number (optional, defaults to 25)

-t    To: Address

-f    From: Address

-b    Text Body of Message (optional)

-h    Generate Headers

-a    Subject (optional)

-m    Filename (optional) Use file as Body of Message

-c    Prefix above file with CR/LF to separate body from header

-d    Debug (Show all mail server communications)

Save this file as emailtoSMS.bat in the same directory as all the other files mentioned above.

At this stage you can just double-click emailtoSMS.bat and it will send email to SMS messages to all the recipients specified in API.txt. However the purpose of this post is to ensure that you only need to do the most essential tasks, and let windows take care of the rest. As such, open your windows task scheduler and define two new tasks – the first to run Merge.bat and the second to run emailtoSMS.bat about 5 to 10 minutes later (just to be on the safe side). These tasks can be scheduled to run on a periodical basis, so that all you have to do the Merge between Microsoft Word and your Database and save the file in the folder used above (always with the same file name).

Also, keep in mind that, should you ever wish to change the message sent to your customers, you’ll have to edit API.txt and change it there, keeping #field1#, #field2#, etc. in their respective places.

While the above might seem like a lot of steps, it’s actually pretty straight forward and makes email to SMS a breeze. Oh, and always test before using the above method to send email to SMS text messages to your clients.

For more information, simply visit our email to SMS page.

No related posts.

Related posts brought to you by Yet Another Related Posts Plugin.