Sending mail from Access VBA without CDO 1

[PeDa]

Looking for a (non-proprietary) way of sending mail from Microsoft Access 2016 that does not use CDO (or Outlook). We have been doing this for years using CDO:

Dim  MyMessage As Object
Set MyMessage = CreateObject("CDO.Message")
MyMessage.Subject = MySubject
MyMessage.To = MyRecipient
MyMessage.TextBody = MyText
MyMessage.CC = MyCC
MyMessage.From = MyFrom
With MyMessage
  .Configuration.Fields.Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")[/URL] = 2
  .Configuration.Fields.Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] = pubMailServer
  .Configuration.Fields.Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL] = 25
  .Configuration.Fields.Update
  On Error GoTo Err_mail
    .Send
End With

...but now my company is changing to a different IT-provider, that does not support CDO. We have been advised to change to Exchange Web Services. I use the following found here (third option, slightly modified), but get error message 500:

Sub SendMessage()

Dim Subject As String, Recipient As String, Body As String, User As String, Password As String
Dim sReq As String
Dim xmlMethod As String
Dim xmlreq As New MSXML2.XMLHTTP60
Dim EWSEndPoint As String
   
Subject = "Mijn onderwerp"
Recipient = "xxxx@xxxx.nl"
Body = "Hello!"
User = "xxxxx"
Password = "xxxxx"
EWSEndPoint = "[URL unfurl="true"]https://mail.xxxxx.nl/EWS/Exchange.asmx"[/URL]
   
sReq = "<?xml version=""1.0"" encoding=""UTF-8""?>" & vbCrLf
sReq = sReq & "<soap:Envelope xmlns:soap=""[URL unfurl="true"]http://schemas.xmlsoap.org/soap/envelope/""[/URL] xmlns:t=""[URL unfurl="true"]http://schemas.microsoft.com/exchange/services/2016/types"">"[/URL] & vbCrLf
sReq = sReq & "<soap:Header>" & vbCrLf
sReq = sReq & "<t:RequestServerVersion Version=""Exchange2016""/>" & vbCrLf
sReq = sReq & "</soap:Header>" & vbCrLf
sReq = sReq & "<soap:Body>" & vbCrLf
sReq = sReq & "<CreateItem MessageDisposition=""SendAndSaveCopy"" xmlns=""[URL unfurl="true"]http://schemas.microsoft.com/exchange/services/2016/messages"">"[/URL] & vbCrLf
sReq = sReq & "<SavedItemFolderId>" & vbCrLf
sReq = sReq & "<t:DistinguishedFolderId Id=""sentitems"" />" & vbCrLf
sReq = sReq & "</SavedItemFolderId>" & vbCrLf
sReq = sReq & "<Items>" & vbCrLf
sReq = sReq & "<t:Message>" & vbCrLf
sReq = sReq & "<t:ItemClass>IPM.Note</t:ItemClass>" & vbCrLf
sReq = sReq & "<t:Subject>" & Subject & "</t:Subject>" & vbCrLf
sReq = sReq & "<t:Body BodyType=""Text"">" & Body & "</t:Body>" & vbCrLf
sReq = sReq & "<t:ToRecipients>" & vbCrLf
sReq = sReq & "  <t:Mailbox>" & vbCrLf
sReq = sReq & "       <t:EmailAddress>" & Recipient & "</t:EmailAddress>" & vbCrLf
sReq = sReq & "  </t:Mailbox>" & vbCrLf
sReq = sReq & "</t:ToRecipients>" & vbCrLf
sReq = sReq & "</t:Message>" & vbCrLf
sReq = sReq & "</Items>" & vbCrLf
sReq = sReq & "</CreateItem>" & vbCrLf
sReq = sReq & "</soap:Body>" & vbCrLf
sReq = sReq & "</soap:Envelope>" & vbCrLf

xmlMethod = "POST"
xmlreq.Open xmlMethod, EWSEndPoint, False, User, Password
xmlreq.setRequestHeader "Content-Type", "text/xml; charset=""UTF-8"""
xmlreq.setRequestHeader "Translate", "F"
xmlreq.setRequestHeader "User-Agent", "VBAEWSSender"
xmlreq.Send sReq

If xmlreq.Status = 200 Then
     MsgBox "Gelukt!" ' Message Sent okay
Else
     MsgBox "Mislukt! " & xmlreq.Status ' Something went Wrong
End If
End Sub

The SOAP resolves to:

<?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:soap="[URL unfurl="true"]http://schemas.xmlsoap.org/soap/envelope/"[/URL] xmlns:t="[URL unfurl="true"]http://schemas.microsoft.com/exchange/services/2016/types">[/URL]
<soap:Header>
<t:RequestServerVersion Version="Exchange2016"/>
</soap:Header>
<soap:Body>
<CreateItem MessageDisposition="SendAndSaveCopy" xmlns="[URL unfurl="true"]http://schemas.microsoft.com/exchange/services/2016/messages">[/URL]
<SavedItemFolderId>
<t:DistinguishedFolderId Id="sentitems" />
</SavedItemFolderId>
<Items>
<t:Message>
<t:ItemClass>IPM.Note</t:ItemClass>
<t:Subject>Mijn onderwerp</t:Subject>
<t:Body BodyType="Text">Hello!</t:Body>
<t:ToRecipients>
  <t:Mailbox>
       <t:EmailAddress>xxxx@xxxx.nl</t:EmailAddress>
  </t:Mailbox>
</t:ToRecipients>
</t:Message>
</Items>
</CreateItem>
</soap:Body>
</soap:Envelope>

 

[strongm]

>that does not support CDO

Do you mean that they don't support SMTP?

 

[PeDa]

I gather they do support SMTP, but not using CDO over it.

They quote: The MAPI/CDO library has been replaced by Exchange Web Services (EWS), Exchange ActiveSync (EAS), and Representational State Transfer (REST)* APIs. If an application uses the MAPI/CDO library, it needs to move to EWS, EAS, or the REST APIs to communicate with Exchange 2016.

p.

 

[strongm]

Yes, if you were to have been using the MAPI/CDO 1.2.1 libraries that might be true. You are not, however. You are using Microsoft CDO for Windows 2000, a completely different beast and communicates with the 'outside world' (ie. a mail server)via SMTP (and, in NNTP if you have a demand for it). Hence my question. If they support SMTP then your code should continue working, you just need the address of their SMTP gateway. And if your new IT supplier doesn't know the significant differences between the CDO libraries (nor that there is any such thing as 'CDO over SMTP'), you might question why you are using them ...

 

[PeDa]

That sounds promising; thank you. I shall have to get back to them (and I must confess that ‘CDO over SMTP’ was my invention; I hadn’t realised that there are different CDO libraries).

 

[strongm]

>I hadn’t realised

Microsoft's fault for using the CDO name in differing guises ... here's their own overview:

The dynamic-link libraries (DLLs) for CDO (1.1, 1.2, 1.21) and CDO Rendering (1.1, 1.2) are MAPI client object libraries. As such, they require MAPI and service providers (for example, the same service providers as with Microsoft Exchange Server).

Collaboration Data Objects for NTS (CDONTS) is a Simple Mail Transfer Protocol (SMTP) based Messaging offering, which is part of the overall CDO (1.1, 1.2) Library, but is not MAPI dependent.

Collaboration Data objects for Windows 2000 (CDOSYS), is a quantum leap in SMTP Messaging over CDONTS. CDOSYS provides for rich content through extensive MIME support.