• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

Modify CDO Code To Run Faster

The code below sends an email to approximately 180 different cell phones, the problem is that it is taking about 10 min for it to complete. My question is there any changes that could be made to speed up the process or is there a more efficient way to send these emails using access 2003.
Private Sub Command115_Click()

Dim i As Integer
 On Error Resume Next
    Dim carbo As String
    Dim morgan As String
    Dim objEmail As Object
    Me.label01.Visible = True
    Command11.Caption = "Sending Page"
    Me.label01.Caption = "Sending Nightly Fire Test"
    Me.To.Value = "Nightly Fire Test"
    For i = 0 To NgtFirPg.ListCount - 1
       carbo = NgtFirPg.Column(2, i)
        morgan = "This is your regularly scheduled test"
        Me.Message = "This is your regularly scheduled test"
                Set objEmail = CreateObject("CDO.Message")
                objEmail.from = "Dispatch@ozarkdale911.org"
                objEmail.To = carbo
                objEmail.subject = Me.Ref.Value
                objEmail.HTMLBody = ""
                objEmail.TextBody = morgan '& vbCrLf & Environ$("USERNAME")
            objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
            objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.gl.centurytel.net"
            objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
            objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
            objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "dal911@centurytel.net"
            objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "16832976hh546"

   Me.Label150.Visible = True
        Me.Label150.Caption = "Sending " & i + 1 & " " & "of" & " " & NgtFirPg.ListCount & " " & "pages"
       Me.Form.Caption = "Sending " & i + 1 & " " & "of" & " " & NgtFirPg.ListCount & " " & "pages"

        Set objEmail = Nothing
    Next i

Me.label01.Caption = "FINISHED"
Command11.Caption = "Complete"
sl 1

Command11.Caption = "Send Page"
 Me.Form.Caption = "Complete"
Me.label01.Caption = ""
Me.Label150.Caption = "Nightly Test Page Has Been Sent"

End Sub

Open in new window

1 Solution
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

1. Make sure the app is complied.

2. Your using late binding.  That's a 10-15% performance hit.  Instead of:

Dim objEmail As Object

  Set a reference to the CDO lib in tools/references and then dim explicitly.

3. Don't use CDO myself, so I don't know that this will work, but instead of doing:

                Set objEmail = CreateObject("CDO.Message")


       Set objEmail = Nothing

 each time in the loop, move that out of the loop and just try setting the properties and the send method.


Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
As Jim indicates, move all the "config" stuff outside your loop and do it one time. There's no reason to set the smtp server and such each time you send an email.

You might also move the .Send portion outside of the loop - this would fire it one time, when all your emails are ready to send.
send to multiple users at once -- concatenate the addressee data from multiple users.  This will likely be in a comma-separated or semicolon-separated format.
Why are you using HTMLBody and TextBody and there is nothing in HTMLBody?

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now