Getting "method not valid without suitable object" error when trying to make a HTTP request in VBA?
You probably haven't added a reference to Microsoft XML
(any version) for Dim objHTTP As New MSXML2.XMLHTTP
in the VBA window's Tools/References... dialog.
Also, it's a good idea to avoid using late binding (CreateObject
...); better to use early binding (Dim objHTTP As New MSXML2.XMLHTTP
), as early binding allows you to use Intellisense to list the members and do all sorts of design-time validation.
I had to use Debug.print
instead of Print
, which works in the Immediate window.
Sub SendEmail() 'Dim objHTTP As New MSXML2.XMLHTTP 'Set objHTTP = New MSXML2.XMLHTTP60 'Dim objHTTP As New MSXML2.XMLHTTP60 Dim objHTTP As New WinHttp.WinHttpRequest 'Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1") 'Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") URL = "http://localhost:8888/rest/mail/send" objHTTP.Open "POST", URL, False objHTTP.setRequestHeader "Content-Type", "application/json" objHTTP.send ("{""key"":null,""from"":""me@me.com"",""to"":null,""cc"":null,""bcc"":null,""date"":null,""subject"":""My Subject"",""body"":null,""attachments"":null}") Debug.Print objHTTP.Status Debug.Print objHTTP.ResponseTextEnd Sub
Check out this one:
https://github.com/VBA-tools/VBA-Web
It's a high level library for dealing with REST. It's OOP, works with JSON, but also works with any other format.