CODEWITHSUNDEEP
excelvba
shahab
shahab

Reputation: 3

How to retrieve a value from a cell into a URL

I am trying to send out SMS using an API http://bulksms.mysmsmantra.com:8080/WebSMS/SMSAPI.jsp?username=username&password=password&sendername=sender id&mobileno=919999999999&message=Hello

I want to get the mobileno from a cell in sheet7 of the workbook from "d" the cursor will be in the same row. just want the value from "d" to be picked up and used as mobile no.

I found a code which works well with hard coded number in the mobileno, but getting an error when I try to pull the value. Any help will be appreciated

Sub SendSms()    
    Dim URL As String
    Dim rng As Range

    URL = "http://bulksms.mysmsmantra.com:8080/WebSMS/SMSAPI.jsp?username=username&password=password&sendername=sender id&mobileno=& ThisWorkbook.Worksheets(7).rng.Row("d:d").Value & "&message=Hi test"

    Dim xml As Object
    Set xml = CreateObject("MSXML2.XMLHTTP")
    xml.Open "GET", URL, False
    xml.Send
End Sub

I require the code to pull the value from "d:d:" to be picked and used as mobileno and sms to be sent out.

ps; I am a VBA novice, so sorry if I haven't explained it correctly.

Upvotes: 0

Views: 82

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57753

First of all there is a quote missing right after id&mobileno= it must be:

Url = "http://bulksms.mysmsmantra.com:8080/WebSMS/SMSAPI.jsp?username=username&password=password&sendername=sender id&mobileno=" & ThisWorkbook.Worksheets(7).Rng.Row("d:d").Value & "&message=Hi test"

Secondly this is no valid address to a cell ThisWorkbook.Worksheets(7).Rng.Row("d:d").Value. Because d:d is a whole column but you can only put one single cell value into the URL but not a whole column.

So you either must use a concrete cell like 

ThisWorkbook.Worksheets(7).Range("D1").Value

Or you need to loop through column D to send a SMS for each number in column D.
Note: I won't explain that further because you will find a ton of tutorials how to loop through a column.

Also note that Worksheets(7) is the 7ᵗʰ worksheet in the tab list. If someone re-aranges the tabs then your code will fail. Might be better to use the tab name Worksheets("MySheetName").

Upvotes: 1

Related Questions