Send message from Excel to Telegram with VBA

Question

I am using below code to send message from my Excel file to Telegram. It works fine but the only problem is, I can send message only from a single cell, for example ("G9"). If I select a range like ("A1:B3"), then my code does not work.

Here's the code:

Sub Send_Message()
Dim objRequest As Object
Dim strChatID As String
Dim strMessage As String
Dim strPostData As String

strChatID = Sheets("Control").Range("C3").Value
strMessage = Range("G9").Value
strPostData = "chat_id=" & strChatID & "&text=" & strMessage
Set objRequest = CreateObject("MSXML2.XMLHTTP")

With objRequest
    .Open "POST", "https://api.telegram.org/bot:xxxxxxxxx/sendMessage?", False
    .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    .Send (strPostData)
End With

End Sub

Any suggestion what modification should I do to send an entire range? Thanks

Answer 1

You need to change this line:

strMessage = Range("G9").Value

To:

strMessage = Application.TextJoin(Chr(10), True, Range("A1", "B3"))

This will give you a cleaner string from the range of cells.