INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Sending mail from Access VBA without CDO

Sending mail from Access VBA without CDO

(OP)
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:

CODE

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("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
  .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = pubMailServer
  .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 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:

CODE

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 = "https://mail.xxxxx.nl/EWS/Exchange.asmx"
   
sReq = "<?xml version=""1.0"" encoding=""UTF-8""?>" & vbCrLf
sReq = sReq & "<soap:Envelope xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:t=""http://schemas.microsoft.com/exchange/services/2016/types"">" & 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=""http://schemas.microsoft.com/exchange/services/2016/messages"">" & 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:

CODE

<?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:t="http://schemas.microsoft.com/exchange/services/2016/types">
<soap:Header>
<t:RequestServerVersion Version="Exchange2016"/>
</soap:Header>
<soap:Body>
<CreateItem MessageDisposition="SendAndSaveCopy" xmlns="http://schemas.microsoft.com/exchange/services/2016/messages">
<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> 

RE: Sending mail from Access VBA without CDO

>that does not support CDO

Do you mean that they don't support SMTP?

RE: Sending mail from Access VBA without CDO

(OP)
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.

RE: Sending mail from Access VBA without CDO

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 ...

RE: Sending mail from Access VBA without CDO

(OP)
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).

RE: Sending mail from Access VBA without CDO

>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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close