Text Message Notifications with Excel VBA
This is a relatively simple thing to do in Excel, even though the value-added is huge and it sounds like something that would generally be difficult.
- Send re-occuring updates of key metrics from spreadsheets, via SMS at click of a button
- Implement “IF” conditional statements to show certain data if they breach a threshold or target value
- Easy to build out natural langauge sentences by concatenating cell values into a pre-determined script
Most people are unaware that for all cell phone carriers in North America, the text messaging interface actually ties directly to email protocols; such that you can send an email as a text via the phone number at a carrier domain. For instance, you can email firstname.lastname@example.org and it will appear as a text message on their phone. The only difficulty this presents is that you need to know your target audiences cell phone number and the carrier they are with.
Generally within an organization this can be managed via a SharePoint list; setup a list and have your stakeholders self-enroll in notifications and provide their phone number and select from a drop down the carrier they are with. There are some services that can actually determine the carrier from a cell number, but it becomes a little too complex - especially as these days, a cell number can be transferred from one provider to another at any time. So I suggest to leave it up to your users to keep their records updated.
Add a macro to your workbook by clicking on the Developer tab in the ribbon. If you do not have a Developer tab, you will need to enable it as per below.
Create a new macro in This Workbook and copy paste the below code into your coding window. Make the changes I outline below the code! Also be sure to save your workbook as macro enabled (.xlsm).
You will need to change this in the code to suit your needs:
- LINE 29: Update this to your email or mailbox; it actually won’t matter for text messages as recipients won’t be able to reply, but if you put normal emails in line 30, it matters.
- LINE 30: Provide the list of phone number with carrier extensions you want to issue the message to. You can issue to more than one person by separating the list with semicolons. Alternatively, you can make a list of numbers in a spreadsheet and loop through that to build out this string. You can of course put regular emails here as well (gmail, outlook, etc).
- LINE 33: It’s probably smart to BCC yourself, just so you have some assurance everything is working.
- LINE 36: Update this to be the actual message you want to send. In this case it’s just refering to a tab titled “Alert String” and sending what is in Cell(1,1) [Which is A1].
I’ve put the common list of Canadian phone provider extensions in the code, if you’re from the USA you can refer to the below list. It’s probably handy to put this right in the code as a comment so you don’t need to go searching everytime. Of course, if you setup a SharePoint list for people to tell you their carrier, you can VLOOKUP against these entries to get the proper carrier suffix!
You could technically automate this with AutoHotkey and a watchdog timer to send on a fixed schedule, rather than having to manually click a button. Look for an article on this soon…comments powered by Disqus