Explosive Email to SMS: Outlook, Excel and SMTP Part 2

How do you use the SMTP API to get in touch with a distribution list in Outlook? Seeing as sending individual SMS messages are not an option, you’ll have to find another way…

This is part 2 of the series that shows you how to export your distribution list from Outlook and convert it to a format ready for messaging with the SMTP (email to SMS) application programming interface (API). If you haven’t read part one yet, navigate to the Explosive Email to SMS: Outlook & Excel Part 1 post.

That being said, let’s continue…

Problem: So how do you match the column from your distribution list with those from the exported contact list to get it ready for the SMTP API?

8. Matching columns with matching content isn’t difficult, but requires a bit of technical savvy. Quite simply, follow the instructions contained in this Microsoft KB document. However, take care to pay attention to the following:

• Set CompareRange = Range(“C1:C5″) – Replace C1:C5 with the first cell and the last cell of the column containing your distribution list contacts. Similarly, when, according to the Microsoft KB document you have to “Select the range A1:A5”, select the newly merged column (‘D’ in this case). You’ll also have to insert a blank column right after the column containing your original contact’s name and surname (column ‘D’ above), because that’s where the comparison will take place before we can engage the SMTP API.

Tip: Be sure to select from the first to the last value. Selecting entire columns could result in your system doing much more work than it should

9. Now, when your comparison has run and you’ve matched your fields, simply sort all columns and rows according to the comparison field created in (8) of the previous post. This will separate matched records from the unmatched records.

10. When the sort is complete, delete the records you won’t be using. Also ensure that you’ve got no superfluous data hanging around, e.g. a ‘User’ column, an ‘Email’ column, etc.

11. You should only be left with those columns containing the information you want to send in your SMS message.

12. Almost done – the only thing left to do is to format the data that you’ve got in the spreadsheet in front of you into a single column and into the right format for the SMTP API: csv:number|X0|X1|X2|X… (with Xn representing the variables in your email to SMS message as they occur in sequence.)

• To achieve the above, we’ll be using the same process as indicated in (7.2) of the previous post. Note, however, that according to the above format, we need to add ‘csv:’ to the line in order for the SMTP API to interpret the email successfully. To accomplish this, create a new column and populate all the necessary cells with ‘csv:’ (simply select all necessary cells, and paste).

13. Finally, create a new column where all the merged fields will be entered into. If you look at the line above, we’re using ‘|’ as a delimiter. That being said, we’ll simply replace the space used in (7.2) of the previous post with the ‘|’…

=B2&”|”&C2″|”&D2″|”&F2

api_id:1234

user:xxxxxxxxx

password:xxxxxxxxxxx

text:Hi #field1#, your balance is #field2#, please come to the office on #field3#

delimiter:|

csv:1234567890|Fred|€15.50|Monday

csv:1234567890|Jane|€299.45|Tuesday

Here’s what to pay attention to:

• api_id, user and password are all acquired at signup.
• The content of #field1#, #field2#, etc. is specified in the csv… line. Examining the first line above (csv:1234567890|Fred|€15.50|Monday), we note that the ‘Fred’ constitutes #field1#, ‘€15.50’ constitutes #field2# and so on – all separated by the delimiter we specified (delimiter:|)

And that’s all there is to it – once you’ve composed your plain text email with all the necessary data, all you need to do is put in the to address and send it. It will be picked up by the SMTP API and deliver all your text messages accordingly.

If you want more information, follow the link to the SMTP API page.

No related posts.

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

Tags: bulk sms, email to SMS, excel SMS, outlook SMS, SMTP API