CDO emailing freezes menu buttons

[Ringhal]

Hi all,

I have a problem with my code and I can’t seem to find the cause of it. I have code that loops through the customers sending a statement (report) to each customer. The code is perfect in that it sends the report to the customer as intended, but at the end of running, it causes all my menu buttons to not work. Closing the entire database fixes the menus. Disabling the send method (.Send) doesn’t cause this issue with my menu, but obviously this isn’t ideal.

Below is certain relevant section of my code.



Dim cdoMessage As CDO.Message
Dim cdoConfig As CDO.Configuration

Set cdoConfig = CreateObject("CDO.Configuration") 'Assign Config object

'these details are correct and confindetial
With cdoConfig.Fields
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "" 'smtp address
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = "" 'smtp port number
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = "" 'clear text authentication
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = "" 'smtp over the network
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = "" 'Use SSL
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60 'timeout in seconds

'following settings are optional
.Item("http://schemas.microsoft.com/cdo/configuration/smtpaccountname") = ""
.Item("http://schemas.microsoft.com/cdo/configuration/sendemailaddress") = ""
.Item("http://schemas.microsoft.com/cdo/configuration/smtpuserreplyemailaddress") = ""

'warning: it’s a security risk to hard-code your username and password
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = ""
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = ""
.Update
End With

'A temporary table is created that filters only the customers we're emailing
CountCustomers = Dcount("Customers", "MyTable") 'Count customers we're emailing to
for i =1 to CountCustomers
If cdoMessage Is Nothing Then Set cdoMessage = CreateObject("CDO.Message") 'Assign Message object

'Code to determine customer name and email address

'Code edits record source of the report to show only the customer name

'Report is saved in a folder on the drive
DoCmd.OutputTo acOutputReport, sExistingReportName, _
acFormatPDF, myReportOutput, , , , acExportQualityPrint

With cdoMessage
Set .Configuration = cdoConfig
.Subject = ""
.FROM = ""
.To = ""
.TextBody = ""
.AddAttachment myReportOutput 'Attachment path
.Send
Kill myReportOutput 'Deletes saved report
End With

next i

Set cdoConfig = Nothing 'Empty assigned object
Set cdoMessage = Nothing 'Empty assigned object

[Ringhal]

Okay, I'll ask a different question:

Has anyone ever experienced an issue whereby after emailing using CDO, having the menu bars freeze and become non-functional?

[jonh]

Nope. And I can't be bothered to work out what cdo is.

Check for a newer version or updates of the software.

[Ringhal]

CDO is built in to Microsoft Office and I had copied the code from this thread:
http://www.paulsadowski.com/wsh/cdo.htm

[jonh]

I see.

Well, I tested it through gmail and there were no lockups here on Access 2010.
http://www.blueclaw-db.com/access_email_gmail.htm

It's a weird sounding bug. I assume you've tested the code in isolation and without the attachment?

[Ringhal]

I use this code in another area of my database and I don't experience any problems. I've tried to compare the two, but the only difference is the report used and the info in the report.

Thoroughly going through the code step-by-step in break mode doesn't cause any lockups. Also, if I add a breakpoint at the .Send point, run the code and then click Continue after the break, it runs perfectly. My issue lies with something at the .Send command. It appears that there is something 'behind the scenes' that I can't troubleshoot.

[jonh]

It sounds a timing issue. Try sticking a doevents in before the the send.




It probably wont solve the problem, but you've added a reference to cdo so I don't see why you're using createobject


I would use one or the other,


set cdoconfig = new CDO.Configuration


or set the variables to variant.


Also, if you have code that works and that you want to use in multiple places, make it a public function and pass variables to it. If the code works for one thing and not another, it's easier to see where the fault lies.

[Ringhal]

It works, thank you!
I used the code in the link you posted and made it into a public function. Although, I'm not exactly sure where or why there was an issue and it was one of the reasons for posting this thread.