Sending 300 emails using Access 2013 (1 Viewer)

[JohnPapa]

I use the following function to send about 300 emails, but some of the emails fail to be delivered. Do I need to pace the sending by putting some delay between sending the emails?

John

                varDum = sendEmail(CStr(rst!strEmail), "Pay Slip", "Please find attached your Pay Slip" & vbCrLf & vbCrLf & "Confidentiality:  This e-mail is intended for the addressees only and is confidential.  If you have received this message by mistake or are not one of the addressees, you may take no action based on it and you may not print or copy or show it to anyone; please reply to this e-mail and point out the error which has occurred and delete this e-mail from your system." & vbCrLf & vbCrLf & "Security Warning:  Please note that this e-mail has been created knowing that internet e-mail is not a 100% secure way to communicate.  Please take this fact into account before deciding to send me a substantive reply by internet e-mail.", False, "", CStr(rst!strFileNamePDF))





Function sendEmail(strTo As String, strSubject As String, strBody As String, bEdit As Boolean, _

                   Optional strBCC As Variant, Optional AttachmentPath As Variant)

'Send Email using late binding to avoid reference issues

   Dim objOutlook As Object

   Dim objOutlookMsg As Object

   Dim objOutlookRecip As Object

   Dim objOutlookAttach As Object

   Dim i As Integer

   Const olMailItem = 0

 

   On Error GoTo ErrorMsgs

 

   Set objOutlook = CreateObject("Outlook.Application")

 

   Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

   With objOutlookMsg

      Set objOutlookRecip = .Recipients.Add(strTo)

      objOutlookRecip.Type = 1

 

 '     If Not IsMissing(strBCC) Then

 '       Set objOutlookRecip = .Recipients.Add(strBCC)

 '       objOutlookRecip.Type = 3

  '    End If

 

      .Subject = strSubject

      .Body = strBody

      .Importance = 2  'Importance Level  0=Low,1=Normal,2=High

     

      ' Add attachments to the message.

      If Not IsMissing(AttachmentPath) Then

        If IsArray(AttachmentPath) Then

           For i = LBound(AttachmentPath) To UBound(AttachmentPath) - 1

              If AttachmentPath(i) <> "" And AttachmentPath(i) <> "False" Then

                Set objOutlookAttach = .Attachments.Add(AttachmentPath(i))

              End If

           Next i

        Else

            If AttachmentPath <> "" Then

                Set objOutlookAttach = .Attachments.Add(AttachmentPath)

            End If

        End If

      End If

 

'      For Each objOutlookRecip In .Recipients

'         If Not objOutlookRecip.Resolve Then

'            objOutlookMsg.display

'         End If

'      Next

 

      If bEdit Then 'Choose btw transparent/silent send and preview send

        .display

      Else

        .Send

      End If

   End With

 

   Set objOutlookMsg = Nothing

   Set objOutlook = Nothing

   Set objOutlookRecip = Nothing

   Set objOutlookAttach = Nothing

 

ErrorMsgs:

   If Err.Number = "287" Then

      MsgBox "You clicked No to the Outlook security warning. " & _

      "Rerun the procedure and click Yes to access e-mail " & _

      "addresses to send your message. For more information, " & _

      "see the document at [URL]http://www.microsoft.com/office[/URL]" & _

      "/previous/outlook/downloads/security.asp."

      Exit Function

   ElseIf Err.Number <> 0 Then

      MsgBox Err.Number & " - " & Err.Description

      Exit Function

   End If

End Function

 

[Gasman]

You might be blocked by your email server with that many at one time?

Try sending in smaller blocks.?

 

[JohnPapa]

You might be blocked by your email server with that many at one time?

Try sending in smaller blocks.?

That was my first guess,but this sometimes happens when I send only 3 emails.

 

[JohnPapa]

That was my first guess,but this sometimes happens when I send only 3 emails.

I now logged into my Outlook and all emails were sent. Is there any chance that they got stuck in my Outbox?

 

[Gasman]

That was my first guess,but this sometimes happens when I send only 3 emails.

Not that then.
I use the same process and my emails go to my Outbox and I manually then send and received (home environment) without any issues.
If they were stuck in your Outbox, I would have thought that you would know/notice that?

 

[The_Doc_Man]

Is there any chance that they got stuck in my Outbox?

Absolutely possible, but here is where the nature of SMTP rears its ugly head. If you say all the mails were in the Outbox, then this is one possible cause: Mail sent via SMTP or its secure equivalent will depend on an available route.

If for some reason Outlook thinks that there is no current route to the recipient, SMTP is a STORE-AND-FORWARD class of protocol. That means that the message drops back into the Outbox. Every so often, Outlook will sweep through its Outbox to see what is still pending and will attempt again to send it. The exact timing is dependent on site settings. By a pretty much world-wide standard, there will be a point somewhere around 72 hours of "no joy" where Outlook (or any other store-and-forward protocol) will toss its hands up in the air and give up. When it does that, you will receive a "Mailer Daemon" notice that will tell you that the message could not be sent. It should give you an explanation of WHY.

If you were to select the Outbox from Outlook and then trigger a "Send All" you would be able to watch as each pending message disappears for a moment and then reappears (unless, of course, it finally makes it out).

Some of this next comment depends on local settings. For typical Outlook, if you go into the Outbox and open one of the pending messages, look in the "To:" box and see if the address is underscored. Typically, Outlook contacts the target domain controller to verify existence of the named user at that domain. If it has confirmed the target, it will underscore it. If not, then either there is no such user at the domain OR the domain is not reachable at the moment. Normally, if the domain's name cannot be resolved, you get a "blowback" immediately, so if the pending message is still there, you don't have a domain naming error.

My best advice is to wait for a Mailer Daemon error and see what reason it gives for the failure.

 

[Micron]

Also check to see if there is any consistency between the ones that don't go out when there is 3 or 33 or 300 or whatever.
A pause may not be a viable solution unless you are OK with clicking to start the routine and then go for coffee or something. Just a 1 second pause would add 5 minutes to 300 emails; a half second might not be enough of a lag if that was the problem. In the end, it may not be the issue, but it wouldn't hurt to experiment if you can accept the added time.

 

[JohnPapa]

I switched to the following code for sending the emails. If I break the code after every send (SendEmail4) there is no problem. If I send them with no delay they are not sent.

Will try to put a 3 second delay after each send. Can afford the delay.




Call SendEmail4(CStr(rst!strEmail), CStr(rst!strFileNamePDF))



Private Sub SendEmail4(strTo As String, strAttachment As String)
Dim olApp As Object
Dim olMail As Object


On Error GoTo ErrorH

'Creates the memory for email objects
Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(olMailItem)
'Generates email information
With olMail
.BodyFormat = olFormatPlain

.To = strTo
.cc = ""
.BCC = ""
.Subject = "Pay Slp"
.Body = "This is the body" '
.Attachments.Add strAttachment, olByValue, 1, "Test"
'.display
.Send
End With

Set olMail = Nothing
Set olApp = Nothing

EndCode:

Exit Sub

'Error handler to display error infor in message box, resumes end code
'Change is you want/need this to handle specific error numbers
ErrorH:
MsgBox Err.Number & " - " & Err.Description
Resume EndCode

End Sub

 

[Micron]

You create the objects, send an email then destroy the objects 300 times (or so) in a session?
If you do raise an error, the cleanup isn't done.

P.S. Please use code tags (post toolbar) with proper indentation for more than a few lines of code.

 

[JohnPapa]

You create the objects, send an email then destroy the objects 300 times (or so) in a session?
If you do raise an error, the cleanup isn't done.

P.S. Please use code tags (post toolbar) with proper indentation for more than a few lines of code.

Thanks for your comment.
The cleanup is done after

Call SendEmail4(CStr(rst!strEmail), CStr(rst!strFileNamePDF))

and is not indicated above

 

[isladogs]

You could try sending emails direct from Access using CDO

CDO EMail Tester

Attached is a simple utility for testing the settings for sending emails from Access using collaborative data objects (CDO). The zip file contains the Access utility (.accdb) & a Word help file (.docx) I've had to zip it as the site won't allow uploading of docx files! If the email fails due...

www.access-programmers.co.uk

Whilst it will still be subject to bulk email limits, the process may be more efficient than what you are doing now

 

[JohnPapa]

You could try sending emails direct from Access using CDO

CDO EMail Tester

Attached is a simple utility for testing the settings for sending emails from Access using collaborative data objects (CDO). The zip file contains the Access utility (.accdb) & a Word help file (.docx) I've had to zip it as the site won't allow uploading of docx files! If the email fails due...

www.access-programmers.co.uk

Whilst it will still be subject to bulk email limits, the process may be more efficient than what you are doing now

Thanks for the suggestion. Tried the code. Could not make it work, please see attachment

 

[isladogs]

Click the Help button. The Help file explains the various errors that occur when your email settings are incorrect and how to fix them. That error is listed with the fix.

 

[Micron]

and is not indicated above

It sure is:
Set olMail = Nothing
Set olApp = Nothing

Here's the order of events as I see it:
Call the sub
define and set (create) the objects
add values to message object properties
Send
clean up (Set objects to Nothing)
End Sub

Now what, call the sub 299 more times from your other procedure? If not, then I don't know what you mean when you say you're sending 300 emails. If yes, then it is as I said. Regardless, as mentioned, if your sub errors the clean up doesn't happen anyway.

 

[The_Doc_Man]

I note that you are regenerating a new Outlook object every time you call that routine. However, you don't cause Outlook to close. When you create an Outlook Application Object you attempt to create a new instance of Outlook. However, it is possible that your second CreateObject fails because the first copy of Outlook is still running. See, for example,

Home | Ron de Bruin Excel Automation

Welcome to https://www.rondebruin.nl

www.rondebruin.nl

Outlook will not allow you to open it twice on the same system, unlike Excel which is perfectly happy to run as many times as you open it. When you set your object variables to "Nothing" you let go of the link between your app and the external object, but it is still out there in memory because you created an external task. Releasing the pointers does not close the external task.

Personally, I would open Outlook externally to this routine and pass in an object variable for it. Then just create each mail message, send it, and when you exit the subroutine, if you have more to send you just keep on calling the subroutine inside a loop. Then when the loop is over, you can command Outlook to exit (actually, quit) - THEN reset the object variables to Nothing. Should go faster because you wouldn't be"churning" the system so much. And it would take that part of the problem out of the question of why you weren't sending as many messages as you thought.

 

[Micron]

Outlook will not allow you to open it twice on the same system,

I think you've said that before, but this time I decided to suggest that it isn't true - at least not always. I've done it accidentally before - a few times.

 

[zeroaccess]

I have as well, recently.

 

[Gasman]

I have had two instances running myself at times, though not opened via code.?
Still good advice I would have thought.?

 

[isladogs]

@Doc
I agree with the previous comments. You can definitely open more than one copy of Outlook at once.

What you can't do is have two different versions of Outlook installed on the same PC.
I have both Office 2010 & 365 installed on this PC and have two versions of Access, Word, Excel etc but it won't let you do that with Outlook

@JohnPapa
Have you looked at the CDO help file yet. This is the relevant part:

You need to swop your SendUsing setting from 1 to 2 (or vice versa)

 

[JohnPapa]

The initial goal was to send 311 emails with one PDF attachment from within an Access 2013 program, using VBA. Each PDF attachment ranges from 400K to 800K.
1) I tried sending the emails with a 3 second delay and we received word that some of the emails were not delivered.
2) I then tried to break after each sending of every email (311 times) and had to press return for the program to send the next email. I waited for several seconds. I do not know the outcome.
3) If 2) above does not work, is it worth inserting say a 10-15 second delay between sending emails? The subsequent time delay is not a problem, since this will be done once every month. The emails relate to Payroll data.