Sending SMS from excel VBA

Question

I have an a excel file with VBA in it already. What I want to do now is using VBA and integrating with what I have already got is to send SMS.I have contacted them where I got the sample code from but I didn't expect much help from them because they didn't know what VBA is. Here is their sample code:

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"
     }  
   ]
}

From within VBA of course I want to be able to programmaticly change the destination number and the content. At this point I really don't know where to start.

[Check this](https://www.google.com/search?q=site:stackoverflow.com+vba+msxml2.xmlhttp+post) — omegastripes, Mar 07 '19 at 07:55

score 2 · Answer 1 · answered Mar 07 '19 at 08:15

Here is the basic sample, which should make an HTTP POST request with the specified parameters:

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

Awesome thanks for that. Only need to get the authorization to work. — Phil A, Mar 08 '19 at 21:53
@PhilA Modify Authorization header setting as shown [in this answer](https://stackoverflow.com/a/44091919/2165759). — omegastripes, Mar 09 '19 at 09:47

Sending SMS from excel VBA

1 Answers1