Send SMS from VBA

[spikepl]

I want to send SMS from my Access 2007 db.

Voipbuster.com (and the entire bunch of related sites, like eg. voipcheap.com tha I am using) allows sending of messages as follows:

Using HTML SMSlink

You can also send text messages (SMS) without using our software or accessing the website. Use the following link and fill in the desired data:

https://www.voipcheap.com/myaccount/sendsms.php?username=xxxxxxxxxx ​ &password=xxxxxxxxxx&from=xxxxxxxxxx&to=xxxxxxxxxx&text=xxxxxxxxxx


Explanation of the variables:

• username: your VoipCheap username
• password: your VoipCheap password
• from: your username or your verified phone number. Always use international format for the number starting with +, for instance +491701234567
• to: the number you wish to send the sms to. Always use international format starting with +, for instance +491701234567
• text: the message you want to send

After creating the link in a text editor you can copy it into your internet browser's address bar:

Than just hit the Enter button and you will get a notification if the text message (SMS) was sent.

How can I call the above from within VBA, so that I don't get to see a browser opening, and also can pick-up the notification f the success (or failure). The message it comes back with in the open browser (I tested it) is:

"1 1 success"

 

[spikepl]

Ok I googled and found this code:

Dim IE
Set IE = CreateObject("internetexplorer.application")
With IE
.Navigate "http://www.google.com"
Do Until IE.ReadyState = 4 'READYSTATE_COMPLETE
DoEvents
Loop

.ExecWB 6, 2, 2, 0 'OLECMDID_PRINT,OLECMDEXECOPT_DONTPROMPTUSER,PRINT_WAITFORCOMPLETION,0

.Quit
End With
Set IE = Nothing

Just tested it, and it works fine for sending the sms.

Now I just need to pick up the response, just in case, and have no clue how to do that.

 

[spikepl]

Tada: Just discovered the thing sent the response to my printer queue! I'd rather pick it up internally.

Also, it screws up umlaut-characters that I need. Whether URLencoded or not, they are represented as "?" in the SMS-message, which renders this set up unusable, unless I can fix that.

 

[vbaInet]

There's a thread about sending SMS in VBA on the forum or someone made a sample database. I think I came across this sometime ago. Have a search and also look in the Sample Databses and Code Repository sections of the forum.

 

[spikepl]

#4

I found some threads: it's dependent on the provider of text messages. One example works via URL, similar to voipbuster, but response is still not picked up. Then there are the SMS-gateway providers, where you can interface via some API er email or URL, but get fleeced in rates.

The search is still on ...

 

[darbid]

There's a thread about sending SMS in VBA on the forum or someone made a sample database. I think I came across this sometime ago. Have a search and also look in the Sample Databses and Code Repository sections of the forum.

Yeh I remember that too. I think skype was in the topic as well.

 

[spikepl]

#6 I found that one - it's easy, but the rates are not competitive with eg. voipbuster. But, at least it can handle my umlauted characters.

 

[smig]

as for the "?" issue:
some dll/tools/sites might have problems with non English character.

 

[darbid]

#6 I found that one - it's easy, but the rates are not competitive with eg. voipbuster. But, at least it can handle my umlauted characters.

Hi,

Ok first the VOIPCheap is a html link so you can navigate to it.

look here at how to navigate to a webpage, http://vba-corner.livejournal.com/4623.html

It is the first part. The URL will be the format you use as described in VOIPCHEAP.

Can you please send a SMS using your webbrowser and post the resulting HTML page - 1. for a success 2. for a fail.. Once it is sent (for a success and then a fail) right click on the webpage and view source. Save this file and upload it here. So you will upload 2 files.

Umlauts - You have to follow the HTML rules on them see here for example http://www.utexas.edu/learn/html/spchar.html

 

[spikepl]

On some German forum, I found following that works with voipcheap.com:

{ [ = [FONT=&quot]äÄ[/FONT]
| \ = [FONT=&quot]öÖ[/FONT]
~ ^ = [FONT=&quot]üÜ[/FONT]

I have no clue as to where this translation table came from, but I need more (Scandinavian characters like Æ and Å and Ø). I looked at ASCII codes, but cannot seem to make any sense out of it in this connection.

 

[spikepl]

The above apparently stems from a national version of ISO 646 - sadly, voipcheap.com uses the wrong version for my needs, so I cannot get my characters ...

 

[spikepl]

For enhusiasts, here something on ISO 646:

"The only languages that can comfortably be written with the repertoire of US-ASCII happen to be Latin, Swahili, Hawaiian and American English without most typographic frills. It is rumoured that there are more languages in the world."

http://czyborra.com/charsets/iso646.html

 

[darbid]

To write these letters in the URL you must use utf-8.

If you know what letters you need, then let google do it for you.

Write a word with the letter in it and click search. The Google will put the word into the url. You must copy the full URL and paste it into a text document and you will see the result.

For example

%C3%A5 = Å

If you past the other things I was talking about I might be able to help with understanding when it is successfully sent.

 

[darbid]

by the way I just tested this with a fake url / SMS

It returns an XML response

eg

<?phpxml version="1.0" encoding="utf-8"?> 
<SmsResponse>
    <version>1</version>
    <result>0</result> 
    <resultstring>failure</resultstring>
    <description>error</description>
    <endcause></endcause>
</SmsResponse>

here you go

Option Compare Database
Option Explicit


Private Function sendSMS(ByVal sms_username As String, _
                        ByVal sms_password As String, _
                        ByVal sms_from As String, _
                        ByVal sms_to As String, _
                        ByVal sms_msg As String)
                        
On Error GoTo Err_sendSMS

Dim strResult As String
Dim URL_base As String
Dim str_POST As String
Dim XMLHttpRequest As XMLHTTP

Set XMLHttpRequest = New MSXML2.XMLHTTP

'example
'https://www.voipcheap.com/myaccount/sendsms.php?username=xxxxxxxxxx &password=xxxxxxxxxx&from=xxxxxxxxxx&to=xxxxxxxxxx&text=xxxxxxxxxx

URL_base = "https://www.voipcheap.com/myaccount/sendsms.php?"

str_POST = URL_base & _
            "username=" & sms_username & _
            "&password=" & sms_password & _
            "&from=" & sms_from & _
            "&to=" & sms_to & _
            "&text=" & sms_msg

Debug.Print str_POST

XMLHttpRequest.Open "GET", str_POST, False

XMLHttpRequest.send

MsgBox XMLHttpRequest.responseText


Exit_sendSMS:
    Exit Function

Err_sendSMS:
    MsgBox "sendSMS - " & Err.Description & " " & Err.Number
    Resume Exit_sendSMS
End Function

Private Sub Command0_Click()


Call sendSMS("myusername", "mypassword", "SantaClause", "MichealJackson", "Missing you Already")


End Sub

You just need to now parse the xml response to get out the "RESULTSTRING"

This may also mean you do not have to parse your special characters.

 

[spikepl]

#13 Unfortunately this does not do the trick - I still get "?" in my sms. On German sites many ppl have discussed this and given up.

#14 Hey thanks for the code - I can use that for other providers that allow sending via http. When I have found one for my weirdo characters I'll post feed back.

 

[darbid]

have you tried the above code with special characters in them?

 

[spikepl]

I had to test the code of course - it runs perfectly!!!

Thank you.

 

[spikepl]

#16 yes. Same "?" in the sms. The provider site apparently doesn't eat those characters

 

[darbid]

As far as I know all these companies come from Switzerland so they should be ok.

I suggest in the meantime you set up something like this for the sms_msg string


replace(sms_msg,"ä","ae")

This is of course a german example. for the ä

 

[spikepl]

#19 My customer doesn't accept that. I just have to find a different provider, that's all. I have found some, but at higher rates, although still better than eg. Skype.