如何通过Excel VBA将表格区域以图片形式发送至Amazon Chime？

核心思路 #

Amazon Chime的Incoming Webhook支持通过multipart/form-data类型的POST请求发送带附件的消息，替代原有的纯JSON文本请求。整体流程分为三步：导出Excel区域为图片、构造multipart请求体、发送请求。

具体实现代码 #

1. 导出Excel区域为临时图片

这个函数负责把指定的Excel区域转换成PNG图片并保存到临时路径：

Sub ExportRangeToImage(rng As Range, filePath As String)
    Dim chrt As ChartObject
    ' 创建与目标区域尺寸一致的图表对象
    Set chrt = rng.Parent.ChartObjects.Add(0, 0, rng.Width, rng.Height)
    chrt.Activate
    ' 复制区域为图片格式
    rng.CopyPicture Appearance:=xlScreen, Format:=xlPicture
    ' 将图片粘贴到图表并导出
    chrt.Chart.Paste
    chrt.Chart.Export Filename:=filePath, FilterName:="PNG"
    ' 删除临时图表对象
    chrt.Delete
End Sub

2. 发送图片到Chime的主函数

这个函数调用上面的导出函数，构造multipart请求并发送：

Sub SendImageToChime(WebHook As String, rng As Range, Optional messageText As String = "Excel区域截图")
    Dim tempPath As String
    tempPath = Environ("TEMP") & "\temp_chime_image.png"
    
    ' 第一步：导出区域为临时图片
    ExportRangeToImage rng, tempPath
    
    ' 第二步：构造multipart/form-data请求
    Dim oHttp As Object
    Dim boundary As String
    Dim requestBodyText As String
    Dim fileData() As Byte
    Dim fileNum As Integer
    Dim bodyArray() As Byte
    Dim footerArray() As Byte
    Dim i As Integer
    
    ' 生成唯一的请求边界符
    boundary = "----WebKitFormBoundary" & Replace(Format(Now, "YYYYMMDDHHMMSS"), ":", "")
    
    ' 读取图片的二进制数据
    fileNum = FreeFile
    Open tempPath For Binary Access Read As #fileNum
    ReDim fileData(LOF(fileNum) - 1)
    Get #fileNum, , fileData
    Close #fileNum
    
    ' 拼接请求体的文本部分
    requestBodyText = "--" & boundary & vbCrLf
    requestBodyText = requestBodyText & "Content-Disposition: form-data; name=""Content""" & vbCrLf & vbCrLf
    requestBodyText = requestBodyText & messageText & vbCrLf
    requestBodyText = requestBodyText & "--" & boundary & vbCrLf
    requestBodyText = requestBodyText & "Content-Disposition: form-data; name=""file""; filename=""excel_screenshot.png""" & vbCrLf
    requestBodyText = requestBodyText & "Content-Type: image/png" & vbCrLf & vbCrLf
    
    ' 转换文本为字节数组，合并图片二进制数据和结尾边界
    bodyArray = StrConv(requestBodyText, vbFromUnicode)
    ReDim Preserve bodyArray(UBound(bodyArray) + UBound(fileData))
    For i = 0 To UBound(fileData)
        bodyArray(UBound(bodyArray) - UBound(fileData) + i) = fileData(i)
    Next i
    
    Dim footerText As String
    footerText = vbCrLf & "--" & boundary & "--" & vbCrLf
    footerArray = StrConv(footerText, vbFromUnicode)
    ReDim Preserve bodyArray(UBound(bodyArray) + UBound(footerArray))
    For i = 0 To UBound(footerArray)
        bodyArray(UBound(bodyArray) - UBound(footerArray) + i) = footerArray(i)
    Next i
    
    ' 第三步：发送POST请求
    Set oHttp = CreateObject("WinHttp.WinHttpRequest.5.1")
    oHttp.Open "POST", WebHook, False
    oHttp.setRequestHeader "Content-Type", "multipart/form-data; boundary=" & boundary
    oHttp.send bodyArray
    
    ' 清理临时文件
    Kill tempPath
End Sub

使用示例 #

在Excel中调用这个函数，指定Chime Webhook地址和目标区域：

Sub TestSendScreenshot()
    Dim webHookUrl As String
    webHookUrl = "你的Amazon Chime Webhook地址"
    ' 发送Sheet1中A1:C10区域的截图
    SendImageToChime webHookUrl, Sheet1.Range("A1:C10"), "今日数据报表截图"
End Sub

注意事项 #

• 确保WinHttp.WinHttpRequest.5.1组件可用，若遇到权限问题，可替换为MSXML2.XMLHTTP对象（需调整发送二进制数据的方式）
• Amazon Chime对附件大小有限制，单张图片建议不超过10MB
• 测试时可先用Postman等工具构造multipart请求验证Webhook有效性，再调试VBA代码

内容的提问来源于stack exchange，提问作者Mike T