0

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

ram singh
  • 41
  • 8
  • 1
    You need to concatenate the cell values first (ex.: `strMessage = Range("G9").Value & Range("G10").Value & Range("G11").Value` ) – cybernetic.nomad Feb 03 '22 at 20:21
  • This works but my range is a table and I want the same formatting. Is there a way I can send the data as picture instead of text? – ram singh Feb 03 '22 at 20:28
  • Does Telegram even accept any kind of table? If yes, you'll probably have to populate it cell by cell. (or maybe convert the table into an image?) – cybernetic.nomad Feb 03 '22 at 20:37
  • Converting the table into an image would help but I'm sorry, I don't know how to do that. – ram singh Feb 03 '22 at 20:46
  • 2
    This question might help, as it shows now to copy a range as an image https://stackoverflow.com/questions/48399832/saving-range-as-picture-with-excel-2016 – Glenn G Feb 03 '22 at 20:54

1 Answers1

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.