Connection http API to send SMS

JoePublic · January 13, 2022, 3:57pm

Hi, i would like to connect Libre Office CALC to an http API in order to send sms with my Calc sheet. I am a Newbie and a passionate of EXCEL and Calc Sheets and MACROS.

I have a service provider example.com to send this sms through his api via EXCEL.

But I need to do it with CALC and libre Office uses another Basic called OOoBasic and don’t know how are the Methods and objects here.

I attached my VBA code in EXCEL and if someone knows how to connect an API via http, URL, LET ME KNOW PLEASE.

The code that I am interesed is the following:

'**** Conect to the API via Htpp ****************
Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP.6.0") 'Create Object
oXMLHTTP.Open "GET", RouteHtml
oXMLHTTP.Send

Send_SMS = oXMLHTTP.responseText 'The sms provider give us a feedback to know if all went fine.

'** Clean the object *********
Set oXMLHTTP = Nothing

With EXCEL we conect with a library in EXCEL to create a new object, but with CALC, how do we reference this library?

I would appreciate any help. Thanks a lot. The whole code for EXCEL SPREADSHEET is this:

Function Send_SMS(sMobileNumbers As String)
'Send sms via Excel. For this you will have to activate Reference -> Microsoft XML v6.0 in menu VBA Tools/References/ (Activate library VBA Microsoft XML v6.0)
'You can send sms with excel. Send one o several sms. If you want to send same text to several telephones numbers, you must separate each telephone with a coma.
'For instance: sMobileNumbers = "49123456789,32123456789,34123456789"

'*** Este macro es para Excel ******************
Dim SmsOperator_Provider As String
Dim oXMLHTTP As Object
Dim Result As String
Dim RouteHtml As String

Dim sUrl As String
Dim sAPI_ID As String
Dim sPassword As String
Dim sUsername As String
Dim sTelefonFrom As String
Dim sSenderId As String
Dim Text_Sms As String
Dim sreq_feat As String

'***** Get tHe data from our EXCEL Sheet **************
sTelefonFrom = Worksheets("Sms").Range("B4").Value
Text_Sms = Worksheets("Sms").Range("C4").Value

SmsOperator_Provider = Worksheets("Sms").Range("C2").Value 'NAME from our Sms operator.


sUrl = Worksheets("Sms").Range("B10").Value
sUsername = Worksheets("Sms").Range("B11").Value
sPassword = Worksheets("Sms").Range("B12").Value
sAPI_ID = Worksheets("Sms").Range("B13").Value 'id de registro API en proveedor de sms
sSenderId = Worksheets("Sms").Range("B14").Value 'id del que envia el sms.
sreq_feat = Worksheets("Sms").Range("B15").Value 'Es el requisito que se pone al enlace para que salga el id sender en el sms.
   
'***** Concatenate the http API pattern  '*********
'https://www.smsdiscount.com/myaccount/sendsms.php?username=xxxxxxxxxx&password=xxxxxxxxxx&from=xxxxxxxxxx&to=xxxxxxxxxx&text=xxxxxxxxxx
'For several clients the variable must be like this: sMobileNumbers = "+34123456789,+34123456789,+34123456789"
RouteHtml = sUrl
RouteHtml = RouteHtml & "username=" & sUsername
RouteHtml = RouteHtml & "&password=" & sPassword
RouteHtml = RouteHtml & "&from=" & sTelefonFrom
RouteHtml = RouteHtml & "&to=" & sMobileNumbers
RouteHtml = RouteHtml & "&text=" & Text_Sms

'**** Conect to the API via Htpp ****************
Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP.6.0") 'Create Object
oXMLHTTP.Open "GET", RouteHtml
oXMLHTTP.Send

'*** Get the feedback from SMS_Provider **************
'Usually they provide some information to know whether sms was send successfully or not.

Send_SMS = oXMLHTTP.responseText 'The sms provider give us a feedback to know if all went fine.

'** Clean the object *********
Set oXMLHTTP = Nothing
End Function

mikekaganski · January 13, 2022, 4:19pm

For GET method, just use WEBSERVICE spreadsheet function.

=WEBSERVICE(Sms.B10 & "username=" & Sms.B11 & "&password=" & Sms.B12 & "&from=" & Sms.B4 & "&to=" & TEXTJOIN(",";1;Sms.X:X) & "&text=" & Sms.C4)

JoePublic · January 13, 2022, 4:49pm

But how do you implement in a MACRO win OOoBasic ?

mikekaganski · January 13, 2022, 4:55pm

Use the same WEBSERVICE spreadsheet function, and wrap it using FunctionAccess service.

JoePublic · January 13, 2022, 5:30pm

Sorry I don’t know how to use that webservice. I am a really newbie and dummy guy with macros with OOoBasic. Without any documentation or example where I can see how to implement it, I won’t be able to make a script. Thank you for your answer but i don’t understand what you mean.

If you know how to do the macro and wants to share your knowledgment with the comunity I would appreciate. Thanks.

Villeroy · January 13, 2022, 5:41pm

So you have to overcome this status before you can program anything. Any other solution would require that someone else programs for you.

JoePublic · January 13, 2022, 5:50pm

I only need to translate this VBA to OOoBasic for libre Office… If someone can help me, The whole Comunity and I would be grateful. Thank you.
This code form part of the code displayed above.

'**** Conect to the API via Htpp ****************
Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP.6.0") 'Create Object
oXMLHTTP.Open "GET", RouteHtml
oXMLHTTP.Send

Send_SMS = oXMLHTTP.responseText 'The sms provider give us a feedback to know if all went fine.

'** Clean the object *********
Set oXMLHTTP = Nothing

Villeroy · January 13, 2022, 7:32pm

This is impossible to translate. Your code is strictly limited to the MS WIndows platform and it has nothing to do with Excel by the way. This Office suite runs on multiple platforms and does not know any such object as “MSXML2.XMLHTTP.6.0”.
May be you find someone to rewrite something equivalent in Python. Python is one of the LibreOffice macro languages availlable on all platforms.

JoePublic · January 13, 2022, 8:27pm

I am sure there are people who knows a lot about MACROS in native basic and genuine OOoBasic that will do the same as the code I post it for EXCEL.
That’s why I am here. Asking for Help. Thank you for your comment, indeed. I know you can’t, But please, let the others can read and express what they know about it. Probably you get surprised. I will share the solution. I promise !!!

Villeroy · January 13, 2022, 10:17pm

The solution would be shared on this forum which is public with no restrictions. Searching the web for “send SMS Python” I find dozends of matches.
P.S. or Java or JavaScript. You need a program written in one of these languages and read the arguments from sheet cells which is trivial.

Villeroy · January 15, 2022, 2:19pm

Cross-posted and solved at Connection http API send SMS (View topic) • Apache OpenOffice Community Forum

JohnSUN · January 15, 2022, 2:28pm

@Villeroy I don’t think that in both posts the goal was to get a working solution. It seems to me that this is regular spam - the site indicated in the question is not an SMS sending service.

erAck · January 15, 2022, 3:01pm

I replaced the sex site with example.com, maybe the poster isn’t aware of what the xxx replacement instead of the original site links to and neither the purpose of example.* sites, or did it on purpose, shrug…

JoePublic · January 15, 2022, 6:26pm

Thank you. The site I posted it was an example. Anything else. !!!

Villeroy · January 16, 2022, 10:17am

I find all my SMS chat partners on that site.

JoePublic · February 11, 2022, 6:02pm

Thank you ms777,
Your code really works !!!

Here is the code. I attached a file with an example and the macro with functions. You can Download it and fill your new SETTINGS !!!

Here attached the main script but not the functions. Download the file to see all how it works.
You have to hire an sms service and via API you will send the sms. See Settings sheet !!

Send_SMS_LibreOffice.ods (17.2 KB)

Sub Main
   oleService = createUnoService("com.sun.star.bridge.OleObjectFactory")
   xmlHttpReq  = oleService.createInstance("MSXML2.XMLHTTP.6.0")
   
   xmlHttpReq.open("GET", "https://www.google.com", false)
   xmlHttpReq.send()
   Msgbox xmlHttpReq.responseText
End Sub