VBA Email faillures

[apsrbstar]

Hi all, looking for some help with an odd emailing error. Here is my code:

'Setup Email

Dim cdoObj As Object
Dim iConf As Object
Set cdoObj = CreateObject("cdo.message")
Set iConf = CreateObject("CDO.Configuration")
Set iFields = iConf.Fields
iFields.Item("http://schemas.microsoft.com/cdo/con...tion/sendusing") = 2  '  SMTP
iFields.Item("http://schemas.microsoft.com/cdo/con...ion/smtpserver") = "my_exchange_ip_address"
iFields.Update
Set cdoObj.Configuration = iConf

'emailTo is the email address I want to eventually deliver the email to
'emailStyle is some css to style the email
'HTMLBody is the html body of the content

cdoObj.Subject = "Products needing attention " & emailTo
cdoObj.HTMLBody = emailStyle & HTMLBody
cdoObj.to = "my_email_address"
cdoObj.from = "another_email_address"
cdoObj.send

This is enclosed in a while/wend loop. The first few emails send, so there's something about the HTMLBody or emailTo strings which is problematic, however the emailTo string has worked before, all I have changed is the content on the HTMLBody string, but again, I can't imagine what the problem is there. I've copied the string into a text editor and run it in IE8, and it works fine.

The error message is:
Run-time error '-2147220975 (80040211)':



The message could not be sent to the SMTP server. The transport error code was 0x800ccc6a. The server response was 451 4.7.0 Timeout waiting for client input

I am running MS Access 2000 on Windows Server 2003. Exchange version is 2007.

EDIT - I have stripped out the variable strings and it still fails on the third attempt.

[rpeare]

have you done a debug.print to check the body of the email, if it contains " marks or potentially ' marks you likely have a bad string, in other words it's trying to break up a single string into multiple strings and failing for instance if your body string was:


"I told Jessica I was "thrilled" to be there"

this will likely bomb out and you'd need to replace the " marks around thrilled with double quotes to indicate it was to be treated as a string itself and not the end/beginning of a string.

so your actual body string should be more like

"I told Jessica I was ""thrilled"" to be there"

[apsrbstar]

Yeah, I've checked the string. At any rate, it is on the cdoObj.Send I get the error, otherwise I would expect to get an unterminated string constant on the cdoObj.HTMLBody line

[rpeare]

'emailTo is the email address I want to eventually deliver the email to
'emailStyle is some css to style the email
'HTMLBody is the html body of the content

cdoObj.Subject = "Products needing attention " & emailTo
cdoObj.HTMLBody = emailStyle & HTMLBody
cdoObj.to = "my_email_address"
cdoObj.from = "another_email_address"
cdoObj.send

If emailTo is the address you're sending it to, why is it appearing in the subject line? Likewise shouldn't the FROM line be your email address, unless you're building an application to send an email to yourself.

what reference are you using to use the CDO method rather than the outlook commands?

[apsrbstar]

emailTo is in the subject ilne, because I am in a development phase, and I just want to see that the email will be going to the correct recipient at this point. I'm not sure I understand what you mean by what reference am I using, I have shown the code above and have used it successfully on several projects, including this one. Emails are sent to a couple of participants, but it crashes on a certain one. I have tried this without the emailTo string incase it's something in that, although it did work at one point during development.

[rpeare]

Have you used debug.print to print all the variables before you send the email? I know you said you 'checked the string' but I don't really know what that means.

http://www.c-amie.co.uk/technical/cdo-error-80040211/

How long are your message bodies? If they are more than 1000 characters this article seems to indicate there will be some issues. Is this code within a loop (i.e. are you cycling through a larger dataset and sending out individual emails) or does the code send a single email at a time? you said it fails on the third try which indicates to me it's part of a loop that may send out 100 emails.

Have you tried hard coding the variables one at a time to see if you can pin down which variable is causing the problem?

[apsrbstar]

Ok, the email body is long, about 50,000 characters. I have had a successful email sent that was 133,000 characters though.

I've printed the string in the immediate window when the function crashes, which is equivalent to debug.print, copied that into a text editor, saved it as an html file, and run it through IE8. It works fine. I have searched the string for ", there are none.

The loop works like this:
I cycle through some data and build the HTML string that is relevant for the recipient, collect the recipients email address and then send it, and clear out the HTML string. There will be a maximum of 6 receipients.

I thought the article you found may have hit the nail on the head, however, as when I was copying HTMLBody into sublime text, there were carriage returns midway through urls, or tags. The whole thing worked fine once, but then I've had the same error every time after that.

Now I'm trying with no variables whatsoever, and it fails, the third pass. Here is the code that is failing:

cdoObj.Subject = "Products needing attention 1"
cdoObj.HTMLBody = "" 'emailStyle & HTMLBody
'cdoObj.To = emailTo
cdoObj.CC = "andrew.sharpe@rbstar.co.uk"
cdoObj.from = "itdept@rbstar.co.uk"
cdoObj.send

To be 100% clear, this works twice, and then fails on the third pass. I can see nothing that changes - of course, the string contents themselves have changes, but they are not included.

[ItsMe]

... I have shown the code above and have used it successfully on several projects...

I usually include schema for port numbers, and passwords, and others...

EDIT:
Here is one example
https://www.accessforums.net/access/...tml#post201919

[rpeare]

So you send an email using the code you posted in your most recent post 2 times and if fails on the third. I haven't used CDO commands for a long time so I'm shooting in the dark just trying to work through it without having an example or a sample database to examine. Are you properly closing your objects each pass through? sometimes (depending on what you're opening) if you leave an object open, subsequent passes will fail.

This was your original code example:

'Setup Email  
Dim cdoObj As Object 
Dim iConf As Object 

Set cdoObj = CreateObject("cdo.message") 
Set iConf = CreateObject("CDO.Configuration") 
Set iFields = iConf.Fields 

iFields.Item("http://schemas.microsoft.com/cdo/con...tion/sendusing") = 2  '  SMTP 
iFields.Item("http://schemas.microsoft.com/cdo/con...ion/smtpserver") = "my_exchange_ip_address" 
iFields.Update 

Set cdoObj.Configuration = iConf  

'emailTo is the email address I want to eventually deliver the email to 
'emailStyle is some css to style the email 
'HTMLBody is the html body of the content  

cdoObj.Subject = "Products needing attention " & emailTo 
cdoObj.HTMLBody = emailStyle & HTMLBody 
cdoObj.to = "my_email_address" 
cdoObj.from = "another_email_address" 
cdoObj.send

if this is the entirety of the code you're not closing the objects
cdoObj
iConf
iFields

Again I haven't used CDO objects for a long time I'm just trying things as we discard other more obvious things.

[apsrbstar]

I usually include schema for port numbers, and passwords, and others...

EDIT:
Here is one example
https://www.accessforums.net/access/a...tml#post201919

That looks interesting, it contains a lot of extra information I have not been using. I'll take a look!

if this is the entirety of the code you're not closing the objects
cdoObj
iConf
iFields

Fair point, perhaps, but I've never closed any of these object in previous project on the same site, and I've sent up to about 40 emails in a very similar loop. I will put it on the list of things to check.

[ItsMe]

There are additional schemas too. I think what may be causing the issue is a password. Not sure what the SMTP server is waiting for but it may be a password.