CDO Troubleshooting (1 Viewer)

[CompSMART]

Good day to everyone. I've always considered myself an above average troubleshooter - but I have an issue that is proving impossible to figure out despite having invested more hours than I care to admit (publicly anyway). Any suggestions on which direction(s) to head in would be most appreciated.

Hardware/site specifics:
1) 3 newer HPZ2 workstations and 2 much older HPZ400 workstations
2) All workstations run Windows 10 Pro (fully patched) / run the same antivirus/malware software / are on the same switch / there is a single internet provider for site
3) All workstations are running Office 365 (latest release)
4) All users are local admins

Issue:
I can not send CDO emails from Access on the HPZ2 workstations - the HPZ400 workstations have no problem.

My troubleshooting methodology/thinking so far:
1) Not likely an ISP or site related issue because 2/5 of the workstations have no problem

2) All workstations run the same antivirus/firewall software (there's only one antivirus/firewall policy for the entire office) - but just to rule it out - I removed the antivirus/firewall software from the HPZ2's and disabled the Windows firewall - no change in issue

4) I'm using the same server and Office 365 user email credentials during testing (saved in isladogs CDO Email Tester which I'm running from a network share) to ensure no variables when testing between workstations - so it can't be an Office 365 user-specific issue

5) The error from CDO Email Tester is always the same: " Error# -2147220974 The transport lost its connection to the server".

6) cdosys.dll is the same file version (6.6.190041.746) on all workstations

7) The HPZ2's have other software running on them (UPS monitoring software, HD health monitoring software, etc.) which use the SAME smtp.office365.com server on port 25 and user email credentials I'm using for CDO testing - and they all send their respective email alerts without any problem - so again - seems unlikely to be an Office 365 user-specific issue

8) Using PowerShell from the affected HPZ2's - I can successfully send a test email using the smtp.office365.com server on port 25

9) I set up a free account at SMTP2GO.com and using the credentials for their SMTP server can successfully send using the CDO Email Tester from the HPZ2 workstations

 

[isladogs]

Hi
Glad you are finding my CDO tester useful. Here is the link for anyone else reading this thread:

CDO EMail Tester

Used to test sending HTML or plain text emails directly from Access without using Outlook. Attachments can also be added. Attachments can also be added. EMail settings can optionally be included in the email body.

www.isladogs.co.uk

You may not be aware that the app is supplied with a help file that lists the reasons for all the CDO errors I've experienced in almost 20 years of using this feature. I'm attaching that as a PDF

Interestingly in all that time, I've never experienced Error# -2147220974 The transport lost its connection to the server"

The nearest to that is error -2147220973 with very similar wording.

I suspect the causes are similar. Please check those if not already done and report back.

Otherwise, at the moment, I don't have a solution for you.
You are welcome to contact me by email (see link below) and hopefully we can find a solution between us

 

[isladogs]

Follow up. Are you trying to send emails to a gmail account? If so, see page 4 of my help file

Also look at this link:
[RESOLVED] CDO send mail error "the transpor failed to connect to the server" only when compiled-VBForums

 

[The_Doc_Man]

This is a rare error. If you translate -2147220974 to hexadecimal, you get 0x80040212. The error is a non-fatal (0x80....) problem with SMTP (..04...) and the specifics (...0212) lead to the error message you reported. In this context, non-fatal means it didn't crash the system and didn't crash the program either. Errors with 0xC0... are the nasty kind. The 0x80... errors, you can trap and have the program keep running.

SMTP Error - 0x80040212

Hi Rs, This is not really a NAV error. The error comes from SMTP and is typically because of a wrongly configured or malfunctioning SMTP server. You should contact the people who have configured the SMTP server with this error.

www.dynamicsuser.net

Lots of folks see the very close error 0x80040213, failing to connect to the SMTP gateway server. I can't begin to tell you how often I personally saw that little gem pop up when I was working on my security-related apps. But I don't recall ever seeing the ...0212 error.

However, I have a question regarding the network machines. You say you have five systems, of which two are older and three are newer. The newer ones don't work; the older ones do. Is it also the case that the older systems are chronologically older in terms of their presence on the network; specifically, that they were present when the CDO stuff was first tried and the newer systems were later add-ons?

IF SO, ... Something to check is whether your IT people are using firewall-style filtering internally such that your CDO traffic, in order to reach your assigned SMTP gateway, has to cross one of those internal firewalls. If so, ask them to look at the permissions to access the SMTP server and verify that your three new machines are in the ALLOW list. Also, verify that they will allow traffic from MSACCESS.EXE programs. Advanced firewalls can actually do this sometimes. I ran into this with the U.S. Navy because our in-house machines ran in a protected sub-net and the SMTP Gateway Server had to be outside of that sub-net, so we needed "stateful firewalls" to handle that traffic.

If all five of those machines were present when the CDO function was first set up, my comments are less likely to apply. If your IT team is not using internal firewalls to isolate sub-nets, my comments are less likely to apply.

 

[cheekybuddha]

Here's a very old CDO module I wrote for an example database for sending emails many years ago. It was an MDB so I'll just post the code in case your version of Access can't open it.

The trick I found for dealing with the lost transport error was to reset the CDO config.

See the error handler in function fCDOSendMail.

 

[CompSMART]

Interestingly in all that time, I've never experienced Error# -2147220974 The transport lost its connection to the server"

Thank you for your reply! You had me doubting my middle-age eyes as I thought maybe I typed the Error # incorrectly - but that was not the case.

I can't see how the issue on the HPZ2's could be a simple setting/config issue - because I stashed the CDOEmailTester v2201.accdb up on a network share so that I could ensure the settings would remain the same when testing between the HPZ2's and HPZ400's.

 

[cheekybuddha]

I can't see how the issue on the HPZ2's could be a simple setting/config issue

I don't know whether this is a reply to my suggestion.

If it is, I advise you still give it a try.

What I found is that everything would work fine (emails would be sent) until suddenly that error would occur.

Re-setting the configuration object was the solution.

I wrapped it in the error handler to make it seamless - if it failed more than five times in a row, it bailed.

 

[CompSMART]

However, I have a question regarding the network machines. You say you have five systems, of which two are older and three are newer. The newer ones don't work; the older ones do. Is it also the case that the older systems are chronologically older in terms of their presence on the network; specifically, that they were present when the CDO stuff was first tried and the newer systems were later add-ons?

Thank you for taking time to reply. I have been moving in the direction of thinking of the newer vs older workstation issue as being a bit of a red herring simply because (using isladogs CDO Email Tester) the newer HPZ2 workstations that can't send CDO emails via smtp.office365.com can successfully send CDO emails via smtp2go.com. And I'm trying to keep in mind that the HPZ2's are successfully sending SMTP traffic to smtp.office365.com from other utility/monitoring software on them (UPS monitoring software, HD health monitoring software, etc.).

In more direct answer to your question - the new(er) workstations were HPZ2's put into service about 3 years ago - the older Z400 workstations have been kicking around the office for a long time but about 2 years ago refreshed with upgraded RAM, new SSD drives and fresh OS installs. CDO emails have been going out as part of an automated nightly process for years (running only on one of the older working Z400 workstations). Recently, I instituted sending of CDO emails in an area of my application used by my end-users, and hence when the problem arose.

 

[The_Doc_Man]

In-house firewalls CAN determine what image was sending and can block on that basis. Don't need to test. Just need to ask the IT people if they are blocking the SMTP Gateway by running image.

 

[isladogs]

Thanks David for supplying your version of the code. I may include parts of yours e.g. priority/importance/read receipt in my code if I don't already have these.

I can also add the screenshot from post #6 to my help file once I have a definitive solution to accompany it.
Interesting how that error is something I've never seen before despite extensive use of CDO for many years.

I agree with David's comment in his code about not storing user name and password in the code or encrypting where it is necessary.
i use the latter approach in several of my apps so that the app 'silently' emails me with error reports (what/when/where/who etc). Also to send an activation code request in several apps such as my new Database Analyzer Pro (isladogs.co.uk),,

 

[CompSMART]

I want to thank everyone who took time to contribute their thoughts to my post over the weekend. In the the interest of trying to solve the issue and move forward - I simply decided to abandon use of smtp.office365.com, set up a connector in Office365 to accept email from the IP of my site and adjusted my domains SPF record accordingly. I have just tested with isladogs CDO Email Tester from one of the (previously) non-working HPZ2's and was 100% successful.

 

[isladogs]

As I cannot reproduce this issue, is anyone able to give me a definitive cause & solution to add to my CDO help file?