Vba to send sms to mobile number

Hi everyone.

I need help with little tricky problem.

I need a macro code to send sms to few numbers everytime as and when i press the "PAID" button in sheet "INVOICE"

SMS message can be "Hi you have made a sale to 'customer name' of 'amount'"

Customer name refers to cell "C3" and amount refers to cell "Q42"

I have attached my workbook.

Is there any solution to it?

Hey Jeff,

I registered on a website for sending sms.

Their website mentions this:

"The url to be used is: http://www.smsidea.co.in/sendsms.aspx
The variables to be used are:
mobile : Your mobile number
pass : Your Password/API Key
senderid : Alphanumeric/Number SenderID
to : Recipient Number
msg : Actual Message to be sent
msgtype : uc(Compulsary only for unicode messaging)


Are these details good enough for us to work upon?

Hey Jeff,

thanks for your help.

Is there any other way to send any sort of message to a cell phone?

Maybe through Whatsapp?

Because by that I would be notified that a sale has been made.

Any help?

That is not a tech spec to be able to write VBA. Can you refer me to the page where you got this information?

I think VBA will be able to do this, either by telnet or a browser, but I have not done that type of interface so I may not be the right person to help. But I'll do what I can do.

Hi jeff,

Sure.

I have created a account on " http://www.smsidea.biz/ "
They infact have an option to download the excel plugin.

Do you want me to share the login details as well?

Hey Jeff,

Were you able to glance through?

I don't need the login details. But you keep giving me generic information. I need specific technical specifications for how their interface works. I go to that site and can't find anything about an Excel plug-in.

After you enter that site, you need to login .

After that, towards the left side , under Downloads, you will get the option to download the plugin.


Here is the video which helped mu t understand how the site works.
https://www.youtube.com/watch?v=cph2mLICx2g&t=165s

The plug-in they provide takes care of the interface for you. Following their instructions you should be able to modify your data layout so you can use their plug-in without having to write custom VBA.

There is no information here about how to write your own in VBA. If the plug-in is password-protected then I can't analyze their code to customize it.

I guess that you are unable to find a complete technical specification that can be use to write custom code.

The problem is that I want to send a sms with a click of a button as soon as an invoice has been made.

Thats why i thought VBA would be a good option.

So basically through VBA,with a click of a button, I can send SMS.

Here is something which I found on internet

Private Sub Command1_Click()
    Dim DataToSend As String
    Dim objXML As Object
    Dim message As String
    Dim authKey As String
    Dim mobiles As String
    Dim sender As String
    Dim route As String
    Dim URL As String

'Set these variables
authKey = "Your auth key";

mobiles  = "9999999999";

'Sender ID,While using route4 sender id should be 6 characters long.
sender = "TESTIN"; 

' this url encode function may not work fully functional.

message = URLEncode(" Your message ")

'Define route
route = "default" 
' do not use https 
URL = "https://control.msg91.com/api/sendhttp.php?" 

Set objXML = CreateObject("Microsoft.XMLHTTP")
objXML.Open "POST", URL , False
objXML.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
   
objXML.send "authkey=" + authKey + "&mobiles=" + mobiles + "&message=" + message + "&sender=" + sender + "&route=" + route

 If Len(objXML.responseText) > 0 Then
        MsgBox objXML.responseText
       
 End If
     
End Sub

Function URLEncode(ByVal Text As String) As String
    Dim i As Integer
    Dim acode As Integer
    Dim char As String
   
    URLEncode = Text
   
    For i = Len(URLEncode) To 1 Step -1
        acode = Asc(Mid$(URLEncode, i, 1))
        Select Case acode
            Case 48 To 57, 65 To 90, 97 To 122
                ' don't touch alphanumeric chars
           Case 32
                ' replace space with "+"
               Mid$(URLEncode, i, 1) = "+"
            Case Else
                ' replace punctuation chars with "%hex"
               URLEncode = Left$(URLEncode, i - 1) & "%" & Hex$(acode) & Mid$ _
                    (URLEncode, i + 1)
        End Select
    Next
   
End Function

Also something else which I found on internet which might help:
https://stackoverflow.com/questions/...from-excel-vba

Here is the API which i found on the website to send sms:

sample HTTP API Call for Multiple Recipient would be: http://www.smsidea.co.in/sendsms.aspx?mobile=mobile number &pass=password&senderid=SMSBUZ&to=myrecipient1,myrecipient2,myrecipient3&msg=Your msg

Hey Jeff,

I got this document from the company.
It contains information on how to do coding,
I have attached the file.
please do have a look

Sure,

Thank you so much.

I will try gathering few more information on it and get back to you

I can't offer help with the actual coding but maybe this will help.

You can send an email via VBA and as long as you know the provider of the cell phone, you can send an SMS via email.

Let's say that my phone # is 123-456-7890 and that my carrier is AT&T. Sending an email to 1234567890@txt.att.net will then send that SMS to my phone.

List of gateway domains by carrier here:
https://en.wikipedia.org/wiki/SMS_gateway

Sure.

Thank you so much for your response.

I will try researching on internet and try coming up with a solution

hello i have a similar issue. i have spreadsheet that i use to send email via outlook based on certain criteria in a cell. i want to be able to configure it to also use the API supplied by my bulk sms provider to send sms. The SMS provider has sent me the API now i need to make it to utilize these conditions.

Hey CK,

Thank you for your reply.
The vendor allows me to send message to multiple users.
They have provided sample coding as well.

Thank you.

Hopefully there is a solution

The vendor allows me to send message to multiple users.

But what is the limit? Can you send to 100's, 1000's? What is your likely upper limit?

Hey CK76,

I am unable to fetch that information on their website.
However in my case, I need to send sms to only 10 numbers at once.
I will try getting the upper limit asap

Here's sample code construct. You'll need to adjust with actual headers etc required by the vendor.

Sub Sample()
    Const sUser As String = "myusername"
    Const sPw As String = "mypassword"
    Const sId As String = "mysenderID"
    Const baseUrl As String = "http://smsidea.co.in/sendsms.aspx"
    Dim sReq As String, myMsg As String, dest As String
    Dim resp As String

    myMsg = "Test message"
    myMsg = Application.EncodeURL(myMsg)
    dest = "999999999"
    sReq = "?mobile=" & sUser & "&pass=" & sPw & "&senderid=" & sId & "&to=" & dest & "&msg=" & myMsg
    With CreateObject("MSXML2.xmlHttp") 'May need to change this to MSXML2.ServerXMLHTTP
        .Open "POST", baseUrl, False
        .setRequestHeader "Content-type", "application/x-www-form-urlencoded" 'You will need to find from vendor what headers are needed
        .Send sReq
        resp = .responseText
    End With
    MsgBox resp
End Sub

At which line do you get that error?

At this line I get error,

myMsg = Application.EncodeURL(myMsg)

Do you want me to upload sample file for your convenience?

Ah, right. I forgot that function is only available from Excel 2013 or later.

Add code found in link below, and replace Application.EncodeURL(myMsg) with... encodeURL(myMsg)
http://cwestblog.com/2012/10/16/vba-...arts-in-excel/

Ummm,

Do you mean this ?

I am really sorry for asking such basic questions...

Public Function encodeURL(ByVal queryPart As String)
    Dim c As String
    While Len(queryPart) > 0
        c = Left(queryPart, 1)
        queryPart = Mid(queryPart, 2, Len(queryPart) - 1)
        If c Like "[A-Za-z0-9._~-]" Then
            encodeURL = encodeURL & c
        ElseIf c = " " Then
            encodeURL = encodeURL & "+"
        Else
            encodeURL = encodeURL & "%" & Right("0" & Hex(Asc(c)), 2)
        End If
    Wend
End Function
Sub Sample()
    Const sUser As String = "USER NAME"
    Const sPw As String = "PASSWORD"
    Const sId As String = "SMSBUZ"
    Const baseUrl As String = "http://smsidea.co.in/sendsms.aspx"
    Dim sReq As String, myMsg As String, dest As String
    Dim resp As String

    myMsg = "Test message"
    myMsg = encodeURL(myMsg)
    dest = "***"
    sReq = "?mobile=" & sUser & "&pass=" & sPw & "&senderid=" & sId & "&to=" & dest & "&msg=" & myMsg
    With CreateObject("MSXML2.xmlHttp") 'May need to change this to MSXML2.ServerXMLHTTP
        .Open "POST", baseUrl, False
        .setRequestHeader "Content-type", "application/x-www-form-urlencoded" 'You will need to find from vendor what headers are needed
        .Send sReq
        resp = .responseText
    End With
    MsgBox resp
End Sub

Yes. That looks correct.

You can check that message is URL encoded by using Debug.Print above 'dest =' line and looking in immediate window.

    myMsg = encodeURL(myMsg)
    Debug.Print myMsg
    dest = "***"

Sure.
Thanks.

I have added a button and assigned the macro.

After I click the button I get a error

API08: Please pass the required parameters.


What do i do now?

That means, that the code is working and sending request to the API, and got a response from the server.
However, you are missing some parameter(s) required by API.

If you are using Unicode messaging, add "&msgtype=uc" at end of sReq string.

Also looking at their sample, you may want to end sReq string construction by adding & "" at end.

However, since the response they returned isn't very specific, I'd suggest working with the vendor to fine tune it.

I have asked the vendor.
They provided me this:


The variables to be used are:
mobile : my mobile number
pass : Your Password
senderid : SMSBUZ
to : Recipient Number
msg : Actual Message to be sent
msgtype : uc(Compulsary only for unicode messaging)

The code has all that, except for msgtype. Add that in and see what's returned.

Use Debug.Print to check that the string generated is meeting requirement and match request pattern.

Don't think I can help you further as I don't have access to their API.

You have base code for using their API and how to construct request string. All you need to do is ensure all the correct info are sent.

I got this code from their PDF file.
I was wondering if we can use .net code in excel and assign it to a button?

Calling HTTP API Using .Net
Imports System.IO
Imports System.Net
Imports System.Data
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
Dim WebRequest As Net.WebRequest 'object for WebRequest
Dim WebResonse As Net.WebResponse 'object for WebResponse
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''
' DEFINE PARAMETERS USED IN URL
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''
'Username that is to be used for submission
'i.e. Dim UserName As String = "tester"
Dim UserName As String = ""
' password that is to be used along with username
'i.e. Dim Password As String = "password"
Dim Password As String = ""
'Message content that is to be transmitted
Dim Message As String = "Test SMS"
'Url Encode message
Message = HttpUtility.UrlEncode(Message)
'Sender Id to be used for submitting the message
'i.e. Dim SenderName As String = "test"
Dim SenderId As String = "Tester"
'Destinations to which message is to be sent For submitting more
than one
'destination at once destinations should be comma separated Like
'91999000123,91999000124
Dim Destination As String = ""
'''''''CODE COMPLETE TO DEFINE PARAMETER''''''''''''''''
Dim WebResponseString As String = ""
Dim URL As String = "http://
Smsidea.co.in/sendsms.aspx?mobile=" & UserName & "&pass=" & Password & "&senderid="
&SenderId & "
&to=" & Destination & "&msg=" & Message & ""
WebRequest = Net.HttpWebRequest.Create(URL) 'Hit URL Link
WebRequest.Timeout = 25000
WebResonse = WebRequest.GetResponse 'Get Response
Dim reader As IO.StreamReader = New
IO.StreamReader(WebResonse.GetResponseStream)
'Read Response and store in variable
WebResponseString = reader.ReadToEnd()
WebResonse.Close()
Response.Write(WebResponseString) 'Display Response.
Catch ex As Exception
WebResponseString = "Request Timeout" 'If any exception
occur.
Response.Write(WebResponseString)
End Try
End Sub
End Class

Thanks you very much for sharing this code. It helps a lot.

This part of the code should be this itself ?

sReq = "?mobile=" & sUser & "&pass=" & sPw & "&senderid=" & sId & "&to=" & dest & "&msg=" & myMsg

OR

sReq = "?mobile=9999999999" & sUser & "&pass=mypassword" & sPw & "&senderid=SMSBUZ" & sId & "&to=999999999" & dest & "&msg=my message" & myMsg

Hey CK.

THis code worked perfectly.
I was able to send SMS.

THANK YOU SOO SO SO MUCH.

This wasnt a easy task. THANK YOU SO MUCHHHHH

You are welcome and thanks for the rep

If this has solved your query. Please mark the thread as solved by using thread tool found at top of your initial post.

Hey CK ,

Is it possible to link 2 cells in the text message?

Example :
My test message is "Hi you made a sales to CUSTOMER with AMOUNT . "
Here I want to link CUSTOMER name to cell C3 and amount to cell Q42 .

IS it possible?

Yes...

Something like

Dim myMsg As String
With Sheets("YourSheetName")
    myMsg = "Hi you made a sales to " & .Range("C3").Text & " with " & .Range("Q42").Text & "."
End With

Ummm,

Thanks for your response, However

I am getting an error
"Compile error
Duplication declaration in current scope"

Getting error at

Dim myMsg As String

Here is my full code:

Sub Sample()
    Const sUser As String = "9999999999"
    Const sPw As String = "Password"
    Const sId As String = "SMSBUZ"
    Const baseUrl As String = "http://smsidea.co.in/sendsms.aspx"
    Dim sReq As String, myMsg As String, dest As String
    Dim resp As String
    Dim myMsg As String
With Sheets("Sheet1")
    myMsg = "Hi you made a sales to " & .Range("C3").Text & " with " & .Range("Q42").Text & "."
End With
    myMsg = encodeURL(myMsg)
    dest = "999999999"
    sReq = "?mobile=" & sUser & "&pass=" & sPw & "&senderid=" & sId & "&to=" & dest & "&msg=" & myMsg
    With CreateObject("MSXML2.xmlHttp") 'May need to change this to MSXML2.ServerXMLHTTP
        .Open "POST", baseUrl & sReq, False
        .setRequestHeader "Content-type", "application/x-www-form-urlencoded" 'You will need to find from vendor what headers are needed
        .Send
        resp = .responseText
    End With
End Sub

Hey CK,
Trust you are well

Were you able to glance through?

Right, so I added as demo. Since you have myMsg declared in the code already. You shouldn't declare it again.
Meaning, just remove "Dim myMsg as String" line.

Sure,

Thank you so much.
It is detecting the cell C3 which is customer name
However it isnt detecting Cell Q42 which is amount.

Is it because it is not in text format?
Its in numerical format

Hey CK,

were you able to glance through?

It was some basic problem.

Now I am able to detect the amount.

Its working perfect now.

Great.

THANK YOU SO MUCH

Thank you for helping me out for the past few days!

Well... if you are not connected to internet/wifi... there's no way to send SMS.

So you either have to store dest in some other sheet and retry it later, or test that you are connected to internet before you send SMS.

It's really up to you and what actual use case would be.

Hey CK ,
sure.
The problem is that at times there are no internet connection.
so is it not possible to remove the error message if there is no connection ?

Because it fails to send the sms, it stops performing other functins as well.
Currently I have made a command button which performs 4 macro functions in sequence.
Since it fails to send the sms, other functions are not carried out.

So is it not possible to simple ignore sending sms if in case there is no internet connection?

Thank you

Add error trap.

Ex:

On Error Goto ErrHandle:
'... bulk of your code
ErrHandle:
If Err.Number <> 0 Then
'Do something
End If
End Sub

You can read more about error handling at links below.
https://excelmacromastery.com/vba-error-handling/
http://www.cpearson.com/excel/errorhandling.htm

Hey CK,

Thank you for your reply:

Did you mean this?

Public Function encodeURL(ByVal queryPart As String)
    Dim c As String
    While Len(queryPart) > 0
        c = Left(queryPart, 1)
        queryPart = Mid(queryPart, 2, Len(queryPart) - 1)
        If c Like "[A-Za-z0-9._~-]" Then
            encodeURL = encodeURL & c
        ElseIf c = " " Then
            encodeURL = encodeURL & "+"
        Else
            encodeURL = encodeURL & "%" & Right("0" & Hex(Asc(c)), 2)
        End If
    Wend
End Function
Sub Sample()
    Const sUser As String = "***"
    Const sPw As String = "**"
    Const sId As String = "**"
    Const baseUrl As String = "****"
    Dim sReq As String, myMsg As String, dest As String
    Dim resp As String
With Sheets("INVOICE")
    myMsg = "Keep the kids happy this summer with free entry to the " & .Range("C3").Text & " Paintball Centre through out the season. Call " & .Range("AA2").Text & "."
End With
    myMsg = encodeURL(myMsg)
    dest = "***"
    sReq = "?mobile=" & sUser & "&pass=" & sPw & "&senderid=" & sId & "&to=" & dest & "&msg=" & myMsg
    With CreateObject("MSXML2.xmlHttp") 'May need to change this to MSXML2.ServerXMLHTTP
        .Open "POST", baseUrl & sReq, False
        .setRequestHeader "Content-type", "application/x-www-form-urlencoded" 'You will need to find from vendor what headers are needed
        .Send
        resp = .responseText
        On Error GoTo ErrHandle:
        Sub Sample()
    Const sUser As String = "***"
    Const sPw As String = "****"
    Const sId As String = "**"
    Const baseUrl As String = "***"
    Dim sReq As String, myMsg As String, dest As String
    Dim resp As String
With Sheets("INVOICE")
    myMsg = "Keep the kids happy this summer with free entry to the " & .Range("C3").Text & " Paintball Centre through out the season. Call " & .Range("AA2").Text & "."
End With
    myMsg = encodeURL(myMsg)
    dest = "***"
    sReq = "?mobile=" & sUser & "&pass=" & sPw & "&senderid=" & sId & "&to=" & dest & "&msg=" & myMsg
    With CreateObject("MSXML2.xmlHttp") 'May need to change this to MSXML2.ServerXMLHTTP
        .Open "POST", baseUrl & sReq, False
        .setRequestHeader "Content-type", "application/x-www-form-urlencoded" 'You will need to find from vendor what headers are needed
        .Send
        resp = .responseText
ErrHandle:
If Err.Number <> 0 Then
'Do something
End If
End Sub
    End With
End Sub

However it does not work.
Please help me

Hey CK,
i am getting the same error even after adding the code which you asked me to add:

Here is the code:

Public Function encodeURL(ByVal queryPart As String)
    Dim c As String
    While Len(queryPart) > 0
        c = Left(queryPart, 1)
        queryPart = Mid(queryPart, 2, Len(queryPart) - 1)
        If c Like "[A-Za-z0-9._~-]" Then
            encodeURL = encodeURL & c
        ElseIf c = " " Then
            encodeURL = encodeURL & "+"
        Else
            encodeURL = encodeURL & "%" & Right("0" & Hex(Asc(c)), 2)
        End If
    Wend
End Function
Sub Sample()
    Const sUser As String = "***"
    Const sPw As String = "****"
    Const sId As String = "**"
    Const baseUrl As String = "**"
    Dim sReq As String, myMsg As String, dest As String
    Dim resp As String
With Sheets("INVOICE")
    myMsg = "Keep the kids happy this summer with free entry to the " & .Range("C3").Text & " Paintball Centre through out the season. Call " & .Range("AA2").Text & "."
End With
    myMsg = encodeURL(myMsg)
    dest = "***"
    sReq = "?mobile=" & sUser & "&pass=" & sPw & "&senderid=" & sId & "&to=" & dest & "&msg=" & myMsg
    With CreateObject("MSXML2.xmlHttp") 'May need to change this to MSXML2.ServerXMLHTTP
        .Open "POST", baseUrl & sReq, False
        .setRequestHeader "Content-type", "application/x-www-form-urlencoded" 'You will need to find from vendor what headers are needed
        .Send
        resp = .responseText
        On Error GoTo ErrHandle:
'... bulk of your code
ErrHandle:
If Err.Number <> 0 Then
'Do something
End If
    End With
End Sub

This part is creating problem

.Send

Hey CK,

Thanks for your promt reply.

However I am facing the same issue:

Public Function encodeURL(ByVal queryPart As String)
    Dim c As String
    While Len(queryPart) > 0
        c = Left(queryPart, 1)
        queryPart = Mid(queryPart, 2, Len(queryPart) - 1)
        If c Like "[A-Za-z0-9._~-]" Then
            encodeURL = encodeURL & c
        ElseIf c = " " Then
            encodeURL = encodeURL & "+"
        Else
            encodeURL = encodeURL & "%" & Right("0" & Hex(Asc(c)), 2)
        End If
    Wend
End Function
Sub Sample()
    On Error GoTo ErrHandle:
    Const sUser As String = "***"
    Const sPw As String = "***"
    Const sId As String = "**"
    Const baseUrl As String = "**"
    Dim sReq As String, myMsg As String, dest As String
    Dim resp As String
With Sheets("INVOICE")
    myMsg = "Keep the kids happy this summer with free entry to the " & .Range("C3").Text & " Paintball Centre through out the season. Call " & .Range("AA2").Text & "."
End With
    myMsg = encodeURL(myMsg)
    dest = "***"
    sReq = "?mobile=" & sUser & "&pass=" & sPw & "&senderid=" & sId & "&to=" & dest & "&msg=" & myMsg
    With CreateObject("MSXML2.xmlHttp") 'May need to change this to MSXML2.ServerXMLHTTP
        .Open "POST", baseUrl & sReq, False
        .setRequestHeader "Content-type", "application/x-www-form-urlencoded" 'You will need to find from vendor what headers are needed
ErrHandle:
If Err.Number <> 0 Then
'Do something
End If
        .Send
        resp = .responseText
    End With
End Sub

Please help me

Hey CK,


THANK YOU SO MUCHHHHHH
WORKS GREAT !

THANK YOU

Hello CK,
I am trying to customize this code to send monthly payroll payslip by SMS to each employee mobile number and I have my carrier API guide I can send it to you
Do you have any suggestion how this could be done?

Thanks in Advance