[Solved] Connection http API send SMS

[JoePublic]

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 xxx.com to send this sms through his api via EXCEL. (This is like a gateaway)
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:

Code: Select all

'**** 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:

Code: Select all

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

Edit: Also posted at https://forum.openoffice.org/es/forum/v ... 50&t=15373

[ms777]

Hi,

to access the MSXML2.XMLHTTP object in OpenOffice use these lines

Good luck,

ms777

Code: Select all

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

[Zizi64]

Crossposted:
https://ask.libreoffice.org/t/connectio ... -sms/72732

[JoePublic]

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 !!

Code: Select all

Sub sendSMS
'This macro send sms to the people who is in the list.

Dim numberRow as Long
Dim oleService As Object
Dim xmlHttpReq As Object
Dim responseAPI as String

Dim sUrl as String
Dim sUsername as String
Dim sPassword as String
Dim sAPI_ID as String
Dim sSenderId as String
Dim sreq_feat as String
Dim sMobileNumbers as String
Dim Text_Sms as String


Dim oSheet as Object
Dim oCell as Object
Dim oRegion as Object

Dim ColName as String
dim indexColumn as Long
Dim finalRow as Long


'******* Get last row in table ***********************************
oCell = ThisComponent.Sheets.getByName("List").getCellRangeByName("A1")
oRegion = getCurrentRegion2(oCell)
finalRow = oRegion.RangeAddress.EndRow
'msgbox finalRow
'**********************************************
'numberRow = getNumberRow() 'if you activate or select a cell of any column, start from this row to the end of table from LIST sheet.
numberRow = 1 'Starts in row 2 until the end.
'**** Get values of sheet *********************
sUrl = ThisComponent.getSheets().getByName("Settings").getCellRangeByName("B5").getString()
sUsername = ThisComponent.getSheets().getByName("Settings").getCellRangeByName("B6").getString()
sPassword = ThisComponent.getSheets().getByName("Settings").getCellRangeByName("B7").getString()
sAPI_ID = ThisComponent.getSheets().getByName("Settings").getCellRangeByName("B8").getString()
sSenderId = ThisComponent.getSheets().getByName("Settings").getCellRangeByName("B9").getString()
sreq_feat = ThisComponent.getSheets().getByName("Settings").getCellRangeByName("B10").getString()

'******* Get the parameters of the Column where we set a ticket number value *****************
ColName = "F"
oSheet = ThisComponent.Sheets.getByName("List") 'Accede a la hoja por su indice. Hoja numero 1
'indice = GetColumnIndex(oSheet, ColName)
indexColumn = GetColIndex(ColName)
'Msgbox indexColumn


'**** Conect to the API via Htpp ****************
oleService = createUnoService("com.sun.star.bridge.OleObjectFactory")
xmlHttpReq = oleService.createInstance("MSXML2.XMLHTTP.6.0")



'****** We iterate over the whole list *************************
For i=1 to finalRow
numberRow = numberRow + 1
sMobileNumbers = ThisComponent.getSheets().getByName("List").getCellRangeByName("D" & numberRow).getString()
sMobileNumbers = "+" & sMobileNumbers
Text_Sms = ThisComponent.getSheets().getByName("List").getCellRangeByName("E" & numberRow).getString()

'***** Concatenate the http API pattern  '*********
'For several clients the variable must be like this: sMobileNumbers = "+34123456789,+34123456789,+34123456789"

        RouteHtml = sUrl
        RouteHtml = RouteHtml & "user=" & sUsername
        RouteHtml = RouteHtml & "&password=" & sPassword
        RouteHtml = RouteHtml & "&api_id=" & sAPI_ID
        
        RouteHtml = RouteHtml & "&to=" & sMobileNumbers
        RouteHtml = RouteHtml & "&text=" & Text_Sms
        
        RouteHtml = RouteHtml & "&from=" & sSenderId
        RouteHtml = RouteHtml & "&" & sreq_feat  
		
		MsgBox RouteHtml


	'******* Send sms to Provider ******************
	'xmlHttpReq.open("GET", "https://www.google.com", false)
	xmlHttpReq.open("GET", RouteHtml, false)
	xmlHttpReq.send()
	   
	'*** Get the feedback from SMS_Provider **************
	'Usually the sms service provide a ticket code justifying that sms was sent successfully.
	responseAPI = xmlHttpReq.responseText 'The sms provider give us a feedback to know if all went fine.
	'Msgbox responseAPI
	'***** We set the Shipping ticket incurrent Row and F Column of LIST sheet **********************************
	Call setValueStringInCell2("List", indexColumn, numberRow - 1, responseAPI)
	'*******************************************************
next i

'** Clean the object and Empty MEMORY *********
xmlHttpReq = Nothing
End Sub