从excel VBA发送短信

Question

我已经有一个包含VBA的excel文件了。我现在想做的是使用VBA，并集成我已经得到的是发送短信，我已经联系了他们，我从哪里得到的样本代码，但我没有期望他们太多的帮助，因为他们不知道VBA是什么。下面是他们的示例代码：

POST /v1/messages HTTP/1.1
Host: api.messagemedia.com
Accept: application/json
Content-Type: application/json
Authorization: Basic dGhpc2lzYWtleTp0aGlzaXNhc2VjcmV0Zm9ybW1iYXNpY2F1dGhyZXN0YXBp
{
"messages": [


{
  "content": "Hello World",
  "destination_number": "+61491570156",
  "format": "SMS"
     }  
   ]
}

当然，在VBA中，我希望能够以编程方式更改目标编号和内容。在这一点上，我真的不知道从哪里开始。

Answer 1

以下是基本示例，它应该使用指定的参数发出HTTP POST请求：

Sub Test()

    Dim oXHR As Object
    Dim sPayload As String

    sPayload = _
        "{" & vbCrLf & _
        """messages"": [" & vbCrLf & vbCrLf & vbCrLf & _
        "{" & vbCrLf & _
        "  ""content"": ""Hello World""," & vbCrLf & _
        "  ""destination_number"": ""+61491570156""," & vbCrLf & _
        "  ""format"": ""SMS""" & vbCrLf & _
        "     }  " & vbCrLf & _
        "   ]" & vbCrLf & _
        "}"

    Set oXHR = CreateObject("MSXML2.XMLHTTP")
    With oXHR
        .Open "POST", "https://api.messagemedia.com/v1/messages", False
        .SetRequestHeader "Host", "api.messagemedia.com"
        .SetRequestHeader "Accept", "application/json"
        .SetRequestHeader "Content-Type", "application/json"
        .SetRequestHeader "Authorization", "Basic dGhpc2lzYWtleTp0aGlzaXNhc2VjcmV0Zm9ybW1iYXNpY2F1dGhyZXN0YXBp"
        .Send (sPayload)
        Debug.Print .ResponseText
    End With

End Sub