pushbullet line break problem with VBA EXCEL

Question

i made a userform in VBA Excel to send SMS via Pushbullet to my clients it works perfectly when i send a single line message , but when i try to send multiple line message it doesn't work,i searched in stackoverflow and google with no answer , i need some help the code i'm using is:

Private Sub smsfac_Click()
Dim lastrow As Integer
'Activate "Microsoft XML, v6.0" under Tools > References
If Me.sms_client.value = "" Or Me.sms_nmbr.value = "" Or Me.sms_txt.value = "" Then
Me.smsfac.ForeColor = vbRed
Me.smsfac.BorderColor = vbRed
Application.Wait (Now + TimeValue("0:00:3"))
Me.smsfac.ForeColor = &HFFFF00
Me.smsfac.BorderColor = &H80000006
Else
Dim item As ListItem
Dim ACCESS_TOKEN As String
Dim TARGET_DEVICE_IDEN As String

'Authentication & Target Device ID
ACCESS_TOKEN = calcule.Range("C7").value
TARGET_DEVICE_IDEN = """" & calcule.Range("C8").value & """"

'Get last row number

'Clear Status
     
    'Get receiver number & text message
    receiver_number = """" & Me.sms_nmbr.Text & """"
    text_message = """" & Me.sms_txt.Text & """"

    'Use XML HTTP
    Set Request = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    
    'Specify Target URL
    URL = "https://api.pushbullet.com/v2/texts"
    
    'Open Post Request
    
    Request.Open "Post", URL, False
    
    'Request Header
    Request.setRequestHeader "Access-Token", ACCESS_TOKEN
    Request.setRequestHeader "Content-Type", "application/json;charset=UTF-8"
    
    'Concatenate PostData
    postData = "{""data"":{""target_device_iden"":" & TARGET_DEVICE_IDEN & ",""addresses"":[" & receiver_number & "],""message"":" & text_message & "}}"
    
    'Send the Post Data
    Request.send postData

End If
End Sub

also if there's away to the a notification that the SMS was sent or not that will help me alot , I appreciate your help

Tim Williams · Answer 1 · 2021-09-03T17:18:18.440

0

String values in JSON need quotes - you do not have quotes around text_message

postData = "{""data"":{""target_device_iden"":" & TARGET_DEVICE_IDEN & _
    ",""addresses"":[" & receiver_number & "],""message"":""" & text_message & """}}"

EDIT: See here for how to handle line breaks in message text: Pushbullet pycurl with linefeed in message

postData = "{""data"":{""target_device_iden"":" & TARGET_DEVICE_IDEN & _
    ",""addresses"":[" & receiver_number & "]," & _
     """message"":""" & replace(text_message, vbLf, "\n") & """}}"

If you're still having problems, then try using Debug.Print to output postData and check it looks as you expect.

EDIT2: if the textbox uses vbCrLf as a line break then try

text_message = Replace(Me.sms_txt.Text, vbCrLf, vbLf)
text_message = Replace(text_message , vbLf, "\n")

postData = "{""data"":{""target_device_iden"":" & TARGET_DEVICE_IDEN & _
    ",""addresses"":[" & receiver_number & "]," & _
     """message"":""" & text_message & """}}"

Soory I missed you were adding the quotes when you read the value from the textbox - my bad not reading the whole code and just jumping to postData

edited Sep 03 '21 at 17:18

answered Sep 03 '21 at 15:49

Tim Williams

154,628
8
97
125

thanks for the replay Tim but when i tried this even a single lined sms won't be sent , the old one works but only with single line , if i used multiline textbox the client won't recieve any text – Youcef Merah Sep 03 '21 at 16:01
I don't know why you're having a problem, but string values in JSON do need to be quoted. That's part of the spec. – Tim Williams Sep 03 '21 at 16:12
yes you're right but i already put text_message = """" & Me.sms_txt.value & """" – Youcef Merah Sep 03 '21 at 16:21
debug.print postdata it gives me {"data":{"target_device_iden":"ujCxgKp8qDQsjA2NhFOs2u","addresses":["0555558711"],"message":"Introduction \n \nSecond Line of Text \n Third Line of Text"}} – Youcef Merah Sep 03 '21 at 16:38
What response do you get from the API when you send that? – Tim Williams Sep 03 '21 at 16:50
when its one line i get : Status Code: 200 – Youcef Merah Sep 03 '21 at 17:04
when i try multiline it gives me: Status Code: 400 | Status Text: {"error":{"code":"invalid_request","type":"invalid_request","message":"Failed to decode JSON body.","cat":"(=^‥^=)"},"error_code":"invalid_request"} – Youcef Merah Sep 03 '21 at 17:05
when i tried your code , but the message in a sheet.cell it worked – Youcef Merah Sep 03 '21 at 17:11
i think the problem when i get it from a textbox – Youcef Merah Sep 03 '21 at 17:11
Maybe the textbox uses vbCrLf as a linebreak? – Tim Williams Sep 03 '21 at 17:15
thank you Tim , the vbcrlf helped me now my problem is solved – Youcef Merah Sep 03 '21 at 18:53
Good to hear we got there eventually. – Tim Williams Sep 03 '21 at 18:54

pushbullet line break problem with VBA EXCEL

1 Answers1