如何使用Excel VBA将本地存储图片发送到Telegram？运行报错如何解决

你当前代码触发Bad Request错误有两个核心原因：

• Telegram Bot的sendPhoto接口不支持直接传入本地文件路径作为photo参数，该参数需要接收二进制图片流、或已经上传到公网的图片URL
• 你使用application/x-www-form-urlencoded格式提交文件上传请求，该编码格式不支持传输二进制文件内容，会导致请求格式非法

修正方案 #

运行代码前，先打开VBA编辑器的「工具」-「引用」，勾选Microsoft ActiveX Data Objects 2.8 Library，再使用如下修改后的代码：

Private Sub CommandButton1_Click()
    Dim objRequest As Object
    Dim strChatId As String
    Dim strMessage As String
    Dim strPhoto As String
    Dim messageDate As Variant
    Dim strPostData As String
    Dim boundary As String
    Dim stream As ADODB.stream
    
    ' 替换为实际的群组ID、Bot Token
    strChatId = "替换为你的群组chat ID"
    Const BOT_TOKEN As String = "替换为你的Telegram Bot Token"
    strMessage = Range("A2").Value '读取A2单元格内容
    strPhoto = Range("A3").Value '读取A3单元格的本地图片完整路径
    messageDate = Now()
    
    If Not IsEmpty(Range("A1").Value) Then
        ' 第一部分：发送文本消息，增加参数编码避免特殊字符报错
        Set objRequest = CreateObject("MSXML2.XMLHTTP")
        strPostData = "chat_id=" & strChatId & "&text=" & WorksheetFunction.EncodeURL(messageDate & vbNewLine & strMessage)
        With objRequest
            .Open "POST", "https://api.telegram.org/bot" & BOT_TOKEN & "/sendMessage", False
            .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
            .Send strPostData
            MsgBox .responseText
        End With
        
        ' 第二部分：发送图片，改用multipart/form-data格式提交
        boundary = "----WebKitFormBoundary" & VBA.Format(VBA.Now(), "HHmmss")
        Set stream = New ADODB.stream
        stream.Mode = adModeReadWrite
        stream.Type = adTypeBinary
        stream.Open
        
        ' 构造请求参数部分
        stream.Write TextToBinary("--" & boundary & vbCrLf)
        stream.Write TextToBinary("Content-Disposition: form-data; name=""chat_id""" & vbCrLf & vbCrLf)
        stream.Write TextToBinary(strChatId & vbCrLf)
        
        stream.Write TextToBinary("--" & boundary & vbCrLf)
        stream.Write TextToBinary("Content-Disposition: form-data; name=""photo""; filename=""" & Dir(strPhoto) & """" & vbCrLf)
        stream.Write TextToBinary("Content-Type: image/jpeg" & vbCrLf & vbCrLf)
        
        ' 读取本地图片二进制内容写入请求流
        Dim imgStream As ADODB.stream
        Set imgStream = New ADODB.stream
        imgStream.Type = adTypeBinary
        imgStream.Open
        imgStream.LoadFromFile strPhoto
        stream.Write imgStream.Read
        imgStream.Close
        Set imgStream = Nothing
        
        ' 写入请求结束标记
        stream.Write TextToBinary(vbCrLf & "--" & boundary & "--")
        stream.Position = 0
        
        ' 发送图片请求
        With objRequest
            .Open "POST", "https://api.telegram.org/bot" & BOT_TOKEN & "/sendPhoto", False
            .setRequestHeader "Content-Type", "multipart/form-data; boundary=" & boundary
            .Send stream.Read
            MsgBox .responseText
        End With
        stream.Close
        Set stream = Nothing
        Set objRequest = Nothing
    End If
End Sub

' 辅助函数：字符串转二进制
Private Function TextToBinary(str As String) As Byte()
    Dim stream As ADODB.stream
    Set stream = New ADODB.stream
    stream.Charset = "utf-8"
    stream.Mode = adModeReadWrite
    stream.Type = adTypeText
    stream.Open
    stream.WriteText str
    stream.Position = 0
    stream.Type = adTypeBinary
    TextToBinary = stream.Read
    stream.Close
    Set stream = Nothing
End Function

注意事项 #

• 图片路径需要填写完整的绝对路径，比如C:\Users\xxx\Pictures\test.jpg
• 如果上传png格式图片，可把代码里的image/jpeg修改为image/png
• Telegram Bot对上传的单张图片大小限制为10MB，超出会触发报错

内容的提问来源于stack exchange，提问作者Steve Kush