MS. Office excel 2007 Connect to NowSMS 2008

MS. Office excel 2007 Connect to NowSMS 2008 SearchSearch
Author Message
Budi Prasetyo
New member
Username: Bimosadewo

Post Number: 1
Registered: 10-2009
Posted on Tuesday, October 20, 2009 - 06:38 am:   

Dear All,
I Have some data in Ms Excel 2007 and i create link to NowSms web interface and the formula like this :
=IF(F2<>"",HYPERLINK("http://192.168.0.222:8800/?PhoneNumber="&L2&"&Text=Bapak/Ibu Orang Tua "&K2&" Terima kasih Pembayaran SPP Bulan "&D2&" "&B2&" Telah Kami terima Pada Tanggal "&O2&"","Kirim SMS"),"")
========
and yes the link can connect to address but the problem is when i click the cell NowSMS send the message 2 times.

Can any body tell me what's going on ?

Thank you.
Des - NowSMS Support
Board Administrator
Username: Desosms

Post Number: 1368
Registered: 08-2008
Posted on Tuesday, October 20, 2009 - 08:09 pm:   

Hi Budi,

I don't have any experience working with Excel macros and Hyperlinks, so this turned out to be a lot more challenging than I originally expected.

Here's what I found out ...

The "double send" occurs by design in Excel.

When you have an HTTP based Hyperlink URL in Excel, Excel issues the HTTP request itself once. It then evaluates the content type of the HTTP response. In this case, it sees that the response is HTML, so it launches the web browser to connect to the URL ... which causes the request to be sent to the server twice.

I searched all over the web, but couldn't find a way to get around this. There looked like a promising registry setting for Excel 2000 and 2003 ... but nothing for 2007.

The problem is that the Excel HYPERLINK command was designed for linking to other data, not for sending commands to an HTTP based server.

The alternative approach is to write a simple VBScript macro instead.

Today was my first attempt at writing one ... but I did manage to create a button in a spreadsheet, where when you click on the button, it reads data from the spreadsheet to send out an SMS.

Here's what I did ...

From the Developer menu in Excel, I added an Active X Command Button.

I then associated the following code with my command button:

Private Sub CommandButton1_Click()
Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
URL = "http://192.168.0.222:8800/"
objHTTP.Open "POST", URL, False
objHTTP.send ("&PhoneNumber=" + Range("a9").Text + "&text=" + Range("a10").Text)

End Sub

In this particular case, I am extracting the phone number from cell A9, and the text to send from A10.

Once I exit design mode, I can click on the button, and it triggers this code, which makes the HTTP submission to NowSMS.

I'm using HTTP POST instead of GET to avoid some URL encoding issues.

It's not as easy as using HYPERLINK, but hopefully you can adapt this to your scenario.

--
Des
NowSMS Support
Budi Prasetyo
New member
Username: Bimosadewo

Post Number: 2
Registered: 10-2009
Posted on Friday, October 23, 2009 - 02:04 pm:   

Hi, Des
Thank you for your Help, its working Now and there's no Double SMS send on single Click.
NowSMS is awesome.
Des - NowSMS Support
Board Administrator
Username: Desosms

Post Number: 1386
Registered: 08-2008
Posted on Monday, October 26, 2009 - 09:35 pm:   

One note ... to specify a user account to use when sending the message, edit the URL string to include "user=" and "password=" parameters:

URL = "http://192.168.0.222:8800/?user=username&password=password"