将WhatsApp API的CURL命令转换为VBA时遇参数错误求助

问题背景 #

出于兴趣及后续使用需求，尝试编写VBA代码实现特定事件触发时发送WhatsApp消息，在转换WhatsApp提供的API CURL命令时遇到参数错误。

原CURL命令 #

curl -X POST \
'https://graph.facebook.com/v18.0/FROM_PHONE_NUMBER_ID/messages' \
-H 'Authorization: Bearer ACCESS_TOKEN' \
-H 'Content-Type: application/json' \
-d '
    {
      "messaging_product": "whatsapp",
      "recipient_type": "individual",
      "to": "PHONE_NUMBER",
      "type": "text",
      "text": { // the text object
        "preview_url": false,
        "body": "MESSAGE_CONTENT"
        }
    }'

自行编写的VBA代码 #

Private Sub CommandButton4_Click()
    Dim INSTANCE_ID, URL As String
    Dim REQUEST As Object
    
    INSTANCE_ID = "EAA..."
    URL = "https://graph.facebook.com/v17.0/196.../messages"
    
    Set REQUEST = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    
    With REQUEST
        .Open "POST", URL
        .setRequestHeader "Authorization", "Bearer " & INSTANCE_ID
        .setRequestHeader "Content-Type", "application/json"
        .send "messaging_product=whatsapp&recipient_type=individual&to=5511...&type=text&text:{body:TEST}"
    End With
    
    Application.Wait Now() + TimeSerial(0, 0, 1)
    Debug.Print "Status: " & REQUEST.Status & " - Text: " & REQUEST.responseText
    Set REQUEST = Nothing
End Sub

执行错误返回 #

Status: 400 - Text: {"error":{"message":"(#100) Invalid parameter","type":"OAuthException","code":100,"error_data":{"messaging_product":"whatsapp","details":"Parameter 'text' is mandatory for type 'text'"},"fbtrace_id":"AWr..."}}

错误原因分析 #

1. 请求格式不匹配：代码中设置了Content-Type: application/json，但发送的是表单格式数据（用&连接键值对），API无法解析为合法的JSON结构，导致识别不到text参数。
2. text参数格式错误：text:{body:TEST}不是合法的JSON对象，正确的JSON需要用双引号包裹键和字符串值。
3. 变量声明不严谨：Dim INSTANCE_ID, URL As String仅将URL声明为String类型，INSTANCE_ID实际是Variant类型，建议显式声明为String。

修正后的VBA代码 #

Private Sub CommandButton4_Click()
    Dim INSTANCE_ID As String, URL As String
    Dim REQUEST As Object
    Dim jsonPayload As String
    
    ' 替换为你的实际令牌和接口URL
    INSTANCE_ID = "EAA..."
    URL = "https://graph.facebook.com/v18.0/196.../messages" ' 建议与CURL版本保持一致（v18.0）
    
    ' 构造合法的JSON请求体，用Chr(34)处理双引号转义
    jsonPayload = "{" & _
        Chr(34) & "messaging_product" & Chr(34) & ":" & Chr(34) & "whatsapp" & Chr(34) & "," & _
        Chr(34) & "recipient_type" & Chr(34) & ":" & Chr(34) & "individual" & Chr(34) & "," & _
        Chr(34) & "to" & Chr(34) & ":" & Chr(34) & "5511..." & Chr(34) & "," & _
        Chr(34) & "type" & Chr(34) & ":" & Chr(34) & "text" & Chr(34) & "," & _
        Chr(34) & "text" & Chr(34) & ":{" & _
            Chr(34) & "preview_url" & Chr(34) & ":false," & _
            Chr(34) & "body" & Chr(34) & ":" & Chr(34) & "TEST" & Chr(34) & _
        "}" & _
    "}"
    
    Set REQUEST = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    
    With REQUEST
        .Open "POST", URL, False ' 同步请求无需额外等待
        .setRequestHeader "Authorization", "Bearer " & INSTANCE_ID
        .setRequestHeader "Content-Type", "application/json"
        .send jsonPayload
    End With
    
    Debug.Print "Status: " & REQUEST.Status & " - Text: " & REQUEST.responseText
    Set REQUEST = Nothing
End Sub

补充说明 #

• JSON格式校验：构造复杂JSON时，可先通过在线JSON校验工具验证格式正确性，避免语法错误。
• 同步/异步请求：代码使用同步请求（.Open第三个参数为False），无需Application.Wait等待响应。
• API版本一致性：尽量保持CURL与VBA代码的API版本一致，避免版本差异导致的兼容性问题。

内容的提问来源于stack exchange，提问作者Rafael Goulart