Modify CDO Code To Run Faster

Posted on 2011-10-16
Last Modified: 2012-05-12
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 = ""
                objEmail.To = carbo
                objEmail.subject = Me.Ref.Value
                objEmail.HTMLBody = ""
                objEmail.TextBody = morgan '& vbCrLf & Environ$("USERNAME")
            objEmail.Configuration.Fields.Item("") = 2
            objEmail.Configuration.Fields.Item("") = ""
            objEmail.Configuration.Fields.Item("") = 25
            objEmail.Configuration.Fields.Item("") = 1
            objEmail.Configuration.Fields.Item("") = ""
            objEmail.Configuration.Fields.Item("") = "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

Question by:mickeyshelley1
    LVL 56

    Accepted Solution


    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.


    LVL 84
    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.
    LVL 44

    Expert Comment

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

    Expert Comment

    Why are you using HTMLBody and TextBody and there is nothing in HTMLBody?

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Suggested Solutions

    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now