.send - anyway to continue processing & not wait. Using CDO. schemas to send emails

[bignose2]

Hi,

Been using code below to send emails for years, all worked well.
However my service provider recently moved my server over to what seems like a DoDaddy service & ever since sending emails has slowed down.
NOT the actual receiving at the other end, that's not too bad, but from the second I press SEND, I get a delay of 5 to 10 seconds before I get e.g. the message "Done"

It does the same from thunderbird & even the webmail server so not an access VBA thing but I was hoping perhaps as least try to see if I can get it to continue running in the background so I can go on the onto the next, might not seem like much of a wait but when going through lots does get very annoying, especially in all my years never had to put up with that. Have spoken to my ISP but seems nothing on the server side they can do.

Really don't want to use outlook & suspect would be the same.

Tried thowing in a few DoEvents but guess actually not what I want, or the opposite really.

    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")
    iConf.Load -1 ' CDO Source Defaults
    Set Flds = iConf.Fields
    With Flds
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = DLookup("[EmailUser]", "Utility Header", "[ID] = 1")
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = DLookup("[EmailPassword]", "Utility Header", "[ID] = 1")
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = DLookup("[MailServerOutgoing]", "Utility Header", "[ID] = 1")
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = ReadGV("EmailTimeOut", lngNumber)

        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465 ' NOT 25 OR 587
        'Use SSL for the connection (False or True)
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True

        .Update
    End With

    With iMsg
        Set .Configuration = iConf
        .BodyPart.Charset = Me!EmailEncoding
        .To = Me!Email
        .BCC = EmailBCC                    
        .From = DLookup("[BookingsEmailAddress]", "Utility Header", "[ID] = 1")
        .Subject = Me!Subject
        .htmlBody = Me!eResponseLetter
                    ' ==============================
        .Send       ' ========= SEND EMAIL =========
                    ' ==============================
     End With
     MSGBOX "Done"

 

[theDBguy]

Perhaps you could move the email routine out of VBA, so you can keep working while the mail is waiting for the server to finish.

 

[Pat Hartman]

The code itself is slower than it needs to be so I would start by cleaning it up. Remove all the dLookup()s and replace it with ONE database read. If you are sending one email at a time, the dLookup()s aren't terrible but if they are inside a loop, they are very expensive since each dLookup() creates and runs a separate query. That query needs to have an execution plan created before it can run so Access is doing a lot of unnecessary work behind the scenes.

Also, how do you decide to send the email? Maybe that process could be streamlined and put into a loop so that all the bookings that came in yesterday get processed together in a loop. That is more incentive to can the domain functions. If you can batch the emails, then doing what @theDBguy suggested is more feasible. You can have a separate db that just handles the emails. Start the process there and then go to the other FE to do your other work.

 

[The_Doc_Man]

Really don't want to use outlook & suspect would be the same.

While I understand why you would not want to use Outlook, particularly since MS wants to use the "New" Outlook, it's performance would NOT be the same since the data flow between CDO and Outlook is different.

Sending via Outlook becomes activated as a distinct child process and moves your completely-built message to an Outbox queue. From that point forward, your issue of sending messages is in the hand of essentially a parallel process. The SMTP handshake is not an issue because Outlook does it for you while your code is running independently.

CDO, however, is a subroutine of whatever is using it. I discovered this when I switched to CDO and suddenly ran into firewall issues because our IT group didn't have a rule to allow SMTP to run from MSACCESS.EXE. Which means that we were still in a subroutine of Access. And, since Access is not programmed for parallel processing within itself, we had to wait for the SMTP handshake to finish.

From the viewpoint of raw speed, Access + CDO remains single-threaded and thus non-parallel, whereas using Outlook splits the mail process into two threads and your VBA code can continue once the message makes it to the Outbox folder of Outlook. I understand fully why you still would not wish to use Outlook as it can be a real pain in the patootie to use.

 

[isladogs]

Try using my CDO code and see if it works any faster / more efficiently

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

 

[Gasman]

Surely you do a dlookup just the once to get that data and then use for all emails?

 

[bignose2]

Thanks for for all your advise.

99% of the time I am sending emails one at a time,
I write, then send,
When I exit that email I review the information in a list, with bookings & lots of other info. this list then highlights any mistakes I might have made while fresh in my mind. so don't really want a batch.

Dlookup I know is inefficient but the record is only 1 long, a header, so pretty much instant & never a problem before so just left for clarity and as not in a loop just easier & clearer, code I had from many years ago.
Its only since the new server, 20+ years its been fine.

I might revisit outlook as you have advised will run in parallel, I do have it as a backup but just not keen.
Annoying as get moved to the new & improved service but in reality a worse service.

CDO Email Tester, exactly the same delay.

Must be down to the handshake.

I am chasing my provider but don't hold out much hope, if not outlook.
I might even try a separate mail provider but will end up paying twice as already included with my domain.

Thanks again.

 

[xavier.batlle]

Have yo tested to authenticate using SSL?

 

[bignose2]

Not sure what you mean, is SSL & password etc all accepted OK & set up as such.
Same with Thunderbird & even the webmail service as default from TSOHost & the new server, I suspect to be linked to godaddy which I hear are not the best.
The emails go OK, just take longer than before, it might be normal for a lot of servers
I have just been use to instant SEND, able to go on the the next email without waiting
hence seeing if a way to allow it in the background.

I would much prefer the serve just works faster.

 

[xavier.batlle]

Not sure what you mean, is SSL & password etc all accepted OK & set up as such.

No you should ask your SMTP provider what configuration you have to use when using SSL and then configure it in the CDO procedure. Search in google: 'sending messages using SSL authentication in CDO'
Lately it's recomended to use SSL when sending messages.

 

[Gasman]

Surely if SSL is not setup correctly, then the account would not connect?

 

[bignose2]

That's what I was saying but not very clearly,

It works fine, In & Out, just rather slow.
I have SSL, ports & passwords all OK

Just having a look at outlook, not used for a while & now sodding around trying to decide if to use New or Old & then persuading the right one to open! cannot see an option old/new anywhere suggested. Uninstalled New for the moment, consensus seems to be New is rubbish, as I only want it as a link will leave to what more likely people know & use to with VBA

 

[Gasman]

Do not touch New if you want to automate.
I think you have already proved it is not the email client?

 

[xavier.batlle]

It works fine, In & Out, just rather slow.
I have SSL, ports & passwords all OK

According the code you posted, SSL is not configured.

 

[amorosik]

If you want to completely free yourself from the Access code for sending emails, I recommend SwithMail
You prepare a text file with the commands to pass to the program and then start it with vba shell command
The entire task will be delegated to the operating system and the flow of your code can continue without problems and without delay
It is also possible to create a log file with the outcome of the operation, which you can also read at a later time
Before I used Blat but some features were not present, with SwithMail I have always managed to send effectively

 

[bignose2]

According the code you posted, SSL is not configured.

Sorry my stupid mistake on original post, I had the port & ssl settings buried in a load of notes & when I copied to here I missed them off, so is SSL is set up correctly.

 

[bignose2]

Probably should start a new thread but while here.

Has anyone used google workspace as their email server, just wondered if be faster.
I have used ordinary gmail via the CDO to send but not sure if more security, authorization.

Working with outlook, little fiddly but think will get there.
Think may stick with outlook anyway, I use a really old service for incoming, Chilkat, has always worked really well & never had to mess with it but I guess could fail or my paid token fail at some point.

 

[AngelSpeaks]

Try using my CDO code and see if it works any faster / more efficiently

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

Hi Colin, I just downloaded this but it's not working. The first image is the error I'm getting. The second image, none of the buttons work. Quit, Show, Email, Browse, Clear, etc.

I have no idea what I am doing wrong. I did change the Options to run all Macros, but that didn't work. I'm on Access 2019.

Thanks. Your database looks very promising.

 

[isladogs]

Hi Colin, I just downloaded this but it's not working. The first image is the error I'm getting. The second image, none of the buttons work. Quit, Show, Email, Browse, Clear, etc.

I have no idea what I am doing wrong. I did change the Options to run all Macros, but that didn't work. I'm on Access 2019.

Thanks. Your database looks very promising.

As with any downloaded databases, you must first unblock the file then click the Enable Content button in the security banner before any code can run. However, you may not see that banner depending on your settings in Access Options

Make sure you use this setting to ensure the banner appears,

Ideally save the file to a trusted location

Have a look at the guidance in my article:

Unblock downloaded files

This article explains what the 'mark of the web' is and how to remove it from downloaded files before use. It also covers two issues you may observe in certain cases

www.isladogs.co.uk

 

[AngelSpeaks]

As with any downloaded databases, you must first unblock the file then click the Enable Content button in the security banner before any code can run. However, you may not see that banner depending on your settings in Access Options

Make sure you use this setting to ensure the banner appears,
View attachment 115456

Ideally save the file to a trusted location

Have a look at the guidance in my article:

Unblock downloaded files

This article explains what the 'mark of the web' is and how to remove it from downloaded files before use. It also covers two issues you may observe in certain cases

www.isladogs.co.uk

Thanks