Outlook doesnt send all emails (1 Viewer)

[Hello1]

Hey guys,
Im having trouble with Outlook not sending all emails, after the code below is executed. The Outlook application is closed most of the time before I start the code and that will be usually the case, when its opened all emails send normally after the code execution. However, as I said before if its not opened before the code starts it doesnt want to send all emails, when I open Outlook after the code some emails are still in Outbox folder and send a bit after but if I dont open Outlook they will stay in Outbox folder until I do it so.
The .Display I added because of the signature which I need in the email and I didnt find another way to add it which is not so complicated for me.

Private Sub cmdSendEmail_Click()     '20190403
    Dim MyRs1 As Recordset
    Dim FileName As String
    Dim emailText As String
    Dim outApp As Outlook.Application
    Dim outMail As Outlook.MailItem
    Dim outlookStarted As Boolean
    'Dim myInspector As Outlook.Inspector
        
    On Error Resume Next
    Set outApp = GetObject(, "Outlook.Application")
    On Error GoTo 0
        If outApp Is Nothing Then
            Set outApp = CreateObject("Outlook.Application")
            outlookStarted = True
        End If
    Set MyDb = CurrentDb
    Set MyQry = MyDb.QueryDefs("qryQueryWhichContainsReceivers")
            MyQry.Parameters("[Forms]![frmName1]![IDOfReading]") = [Forms]![frmName1]![IDOfReading]
            MyQry.Parameters("[Forms]![frmName1]![cboNetwork]") = [Forms]![frmName1]![cboNetwork]
            Set MyRs1 = MyQry.OpenRecordset
            If Not MyRs1.EOF Then
                MyRs1.MoveFirst
                    While Not MyRs1.EOF
                        If InStr(MyRs1!FirstOfEmail, "@") = 0 Or InStr(MyRs1!FirstOfEmail, ".") = 0 Or IsNull(MyRs1!FirstOfEmail) Then
                            MsgBox "For " & MyRs1!CustName & ", ID of customer: '" & MyRs1!IdCustomer & "' the email is invalid or not entered"
                            Exit Sub
                        End If
                    MyRs1.MoveNext
                    Wend
            End If
    Set MyQry = MyDb.QueryDefs("qryQueryWhichContainsReceivers")
            MyQry.Parameters("[Forms]![frmName1]![IDOfReading]") = [Forms]![frmName1]![IDOfReading]
            MyQry.Parameters("[Forms]![frmName1]![cboNetwork]") = [Forms]![frmName1]![cboNetwork]
            Set MyRs1 = MyQry.OpenRecordset
            If Not MyRs1.EOF Then
                MyRs1.MoveFirst
                    While Not MyRs1.EOF
                        FileName = Format(MyRs1!IdCustomer, "000000") & "_" & Left(MyRs1!CurrentDate, 2) & "_" & Right(MyRs1!CurrentDate, 2)
                        FilePath = "D:\Email_PDF\" & Right(MyRs1!CurrentDate, 4) & "year\" & FileName & ".pdf"
                        emailText = emailText & _
                            "Some text"
                        Set outMail = outApp.CreateItem(olMailItem)
                        With outMail
                            .To = MyRs1!FirstOfEmail
                            .Subject = "Receipt for " & MyRs1!CurrentDate & ""
                            .Display
                            'Set myInspector = .GetInspector
                            .HTMLBody = emailText & outMail.HTMLBody
                            .Attachments.Add FilePath
                            .Send
                        End With
                            emailText = ""
                MyRs1.MoveNext
                    Wend
                If outlookStarted Then
                    outApp.Quit
                End If
                MsgBox "All E-mails have been sent"
            End If
End Sub

Thats the code I use. I read somewhere that the .GetInspector would help but I dont know it just didnt, or I didnt use it right whats more possible.

Thanks!

 

[Hello1]

By using this function I found

Public Function Pause(NumberOfSeconds As Variant)
    On Error GoTo Error_GoTo

    Dim PauseTime As Variant
    Dim Start As Variant
    Dim Elapsed As Variant

    PauseTime = NumberOfSeconds
    Start = Timer
    Elapsed = 0
    Do While Timer < Start + PauseTime
        Elapsed = Elapsed + 1
        If Timer = 0 Then
            ' Crossing midnight
            PauseTime = PauseTime - Elapsed
            Start = 0
            Elapsed = 0
        End If
        DoEvents
    Loop

Exit_GoTo:
    On Error GoTo 0
    Exit Function
Error_GoTo:
    Debug.Print Err.Number, Err.Description, Erl
    GoTo Exit_GoTo
End Function

I put Pause(10) before the last MsgBox and then all mails arrive (3 of them) but thats not practical for me, nor for the user
I suppose it will matter how many mails are there to be sent, whats the network speed, PC stats and so on, so I wont know how many seconds exactly to pause and the user would need to wait way too long for the MsgBox.

 

[Mark_]

Just to be clear,

As you are submitting Emails to Outlook for Outlook to send, you are expecting them to be sent if Outlook is not open?

if you see them in Outlook's Outbox, Access has done all that it can. You need to have Outlook open for Outlook to connect to a mail server and send.

 

[Gasman]

Not sure if this would help, but I have not had any problems sending using this code.

See post 4
https://www.access-programmers.co.uk/forums/showthread.php?t=303552&highlight=send+email

I use a template, so that has signature etc and it is a HTML email.

HTH

 

[Hello1]

Just to be clear,

As you are submitting Emails to Outlook for Outlook to send, you are expecting them to be sent if Outlook is not open?

if you see them in Outlook's Outbox, Access has done all that it can. You need to have Outlook open for Outlook to connect to a mail server and send.

In the recordset Im testing it on, 2 of 3 emails always get sent just the last one not unless I put a delay before the End Sub. And I never see the Outlook window with the Set myInspector = .GetInspector and it does put the signature I tested it, so I actually dont need the .Display for the signature.

So it seems Outlook is sending the emails in the background until VBA code in access reaches the End Sub and if it reaches the End Sub before Outlook managed to send all of the emails, the unsent emails will stay in Outbox folder.

Gasman, thats quite some piece of code hehe, I hope I find something shorter before I have to check all of it. I didnt check the whole code but I see the main part for you was to get the signature without the .Display or .GetInspector? However they work for me, Im just having the problem outlook stopping to send the emails after the End Sub of the code is reached and executed.

 

[Gasman]

Comment out outApp.Quit line ?

What is your setting for automatic send/receive?

 

[Hello1]

I did comment it out, same.
How exactly do I check those settings, Im using outlook 2007?

 

[Gasman]

Tools/Options/Mail Setup then Send/Receive button

 

[The_Doc_Man]

COM (Component Object Model) methods have an issue with Outlook because it gets "picky" about whether or not another instance of Outlook exists when you try to launch it. I.e. it has special startup rules.

However, as I said before if its not opened before the code starts it doesnt want to send all emails, when I open Outlook after the code some emails are still in Outbox folder and send a bit after but if I dont open Outlook they will stay in Outbox folder until I do it so.

The above is correct Outlook behavior in that if Outlook isn't up it cannot establish the socket to its mail gateway server and that is how things get OUT of the Outbox. If you have things in the Outbox, they are SUPPOSED to stay there until Outlook is active. Access isn't sending mail. It is merely depositing mail into a structure exposed by COM methods for Outlook. But Access cannot send mail directly in the way that Outlook does it.

If you are sending text-only mail, you could try using CDO (Collaboration Data Objects) as a way to build and send e-mail without Outlook, though typically to do this on a normal business network, you would need your security guys to allow that. Using CDO, you can build a mail message and send it directly from Access via SMTP, but if you are going through Outlook, there is the chance that you would have sent using some other protocol like POP3 or IMAP - and there are a couple of other Outlook options as well.

Using CDO, your messages are sent quickly or else you get an error message quickly. No delays because there is no "staging" - no Outbox. There are pros and cons to this method and I don't recommend it if you want to do anything fancy with the e-mails. With Outlook and COM methods, "fancy" is possible. With CDO, not so much.

 

[Hello1]

These are the settings, I tried ticking the "Perform an automatic send/receive when exiting" but same.

 

[Hello1]

The_Doc_Man, well I do need an attachment and a signature tho, is it possible?

 

[Mark_]

The_Doc_Man, well I do need an attachment and a signature tho, is it possible?

You can use your existing code, just make sure that Outlook is running. In most business environments this would be normal. Any reason you'd NOT have Outlook running?

 

[Hello1]

Well, not really. I could use it just thought it would be more fancy everything being done in the background.
I could put into a msg box to keep an eye on the outbox or sth.
I can use the shell ("OUTLOOK") to show the outlook if not opened already?

 

[Hello1]

How about this?

Private Sub cmdSendEmail_Click()     '20190403
    Dim MyRs1 As Recordset
    Dim FileName As String
    Dim emailText As String
    Dim outApp As Outlook.Application
    Dim outMail As Outlook.MailItem
    Dim myInspector As Outlook.Inspector
        
    
    Set outApp = CreateObject("Outlook.Application")

    Set MyDb = CurrentDb
    Set MyQry = MyDb.QueryDefs("qryQueryWhichContainsReceivers")
            MyQry.Parameters("[Forms]![frmName1]![IDOfReading]") = [Forms]![frmName1]![IDOfReading]
            MyQry.Parameters("[Forms]![frmName1]![cboNetwork]") = [Forms]![frmName1]![cboNetwork]
            Set MyRs1 = MyQry.OpenRecordset
            If Not MyRs1.EOF Then
                MyRs1.MoveFirst
                    While Not MyRs1.EOF
                        If InStr(MyRs1!FirstOfEmail, "@") = 0 Or InStr(MyRs1!FirstOfEmail, ".") = 0 Or IsNull(MyRs1!FirstOfEmail) Then
                            MsgBox "For " & MyRs1!CustName & ", ID of customer: '" & MyRs1!IdCustomer & "' the email is invalid or not entered"
                            Exit Sub
                        End If
                    MyRs1.MoveNext
                    Wend
            End If
    Set MyQry = MyDb.QueryDefs("qryQueryWhichContainsReceivers")
            MyQry.Parameters("[Forms]![frmName1]![IDOfReading]") = [Forms]![frmName1]![IDOfReading]
            MyQry.Parameters("[Forms]![frmName1]![cboNetwork]") = [Forms]![frmName1]![cboNetwork]
            Set MyRs1 = MyQry.OpenRecordset
            If Not MyRs1.EOF Then
                MyRs1.MoveFirst
                    While Not MyRs1.EOF
                        FileName = Format(MyRs1!IdCustomer, "000000") & "_" & Left(MyRs1!CurrentDate, 2) & "_" & Right(MyRs1!CurrentDate, 2)
                        FilePath = "D:\Email_PDF\" & Right(MyRs1!CurrentDate, 4) & "year\" & FileName & ".pdf"
                        emailText = emailText & _
                            "Some text"
                        Set outMail = outApp.CreateItem(olMailItem)
                        With outMail
                            .To = MyRs1!FirstOfEmail
                            .Subject = "Receipt for " & MyRs1!CurrentDate & ""
                            Set myInspector = .GetInspector
                            .HTMLBody = emailText & outMail.HTMLBody
                            .Attachments.Add FilePath
                            .Send
                        End With
                            emailText = ""
                MyRs1.MoveNext
                    Wend
                If MsgBox ("All E-mails have been sent. If Outlook isnt already opened click No, otherwise click Yes") = vbNo Then
                      Shell ("OUTLOOK")
                End If
            End If
End Sub

I did pretty much everything as Steve in his tutorial and I see on the video his email send perfectly fine without outlook showing up. Might be because of the version?
26. (Advanced Programming In Access 2013) Send Outlook Email With VBA

 

[Gasman]

My outlook is generally running all the time, however other than what I show in the status bar, you would not know that Access is creating emails in the Outbox.
If it is not running, it just takes a little longer to process the emails, but the result is the same, they are in the Outbox ready for when I choose to do a Send or Send & Receive.

 

[Hello1]

Ok I found a function which checks if Outlook is opened, if not Outlook will open minimized and I like that. I implemented it and now it works good, at least for now
Code was writen by MVP Ben Clothier and I found it on this site:
Test if Outlook is open and open Outlook with VBA

#Const LateBind = True

Const olMinimized As Long = 1
Const olMaximized As Long = 2
Const olFolderInbox As Long = 6

#If LateBind Then

Public Function OutlookApp( _
    Optional WindowState As Long = olMinimized, _
    Optional ReleaseIt As Boolean = False _
    ) As Object
    Static o As Object
#Else
Public Function OutlookApp( _
    Optional WindowState As Outlook.OlWindowState = olMinimized, _
    Optional ReleaseIt As Boolean _
) As Outlook.Application
    Static o As Outlook.Application
#End If
On Error GoTo ErrHandler
 
    Select Case True
        Case o Is Nothing, Len(o.Name) = 0
            Set o = GetObject(, "Outlook.Application")
            If o.Explorers.Count = 0 Then
InitOutlook:
                'Open inbox to prevent errors with security prompts
                o.Session.GetDefaultFolder(olFolderInbox).Display
                o.ActiveExplorer.WindowState = WindowState
            End If
        Case ReleaseIt
            Set o = Nothing
    End Select
    Set OutlookApp = o
 
ExitProc:
    Exit Function
ErrHandler:
    Select Case Err.Number
        Case -2147352567
            'User cancelled setup, silently exit
            Set o = Nothing
        Case 429, 462
            Set o = GetOutlookApp()
            If o Is Nothing Then
                Err.Raise 429, "OutlookApp", "Outlook Application does not appear to be installed."
            Else
                Resume InitOutlook
            End If
        Case Else
            MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Unexpected error"
    End Select
    Resume ExitProc
    Resume
End Function

#If LateBind Then
Private Function GetOutlookApp() As Object
#Else
Private Function GetOutlookApp() As Outlook.Application
#End If
On Error GoTo ErrHandler
    
    Set GetOutlookApp = CreateObject("Outlook.Application")
    
ExitProc:
    Exit Function
ErrHandler:
    Select Case Err.Number
        Case Else
            'Do not raise any errors
            Set GetOutlookApp = Nothing
    End Select
    Resume ExitProc
    Resume
End Function

 

[Gasman]

If you look at my code I linked to, I initially tested if it was running, then found out, I did not need it, and commented out the code.?
As I mentioned before, my code works whether Outlook is running or not. Just takes a little longer if not, as it has to load up.?
Plus I do not quit Outlook, I just set the object to Nothing.

However, if you have it working as you want, leave well alone.

 

[The_Doc_Man]

I'm with Gasman. There is no particular reason to NOT have Outlook running in the background unless you are running Win10 on a 4 GB system and don't have a lot of virtual space available either.

As a matter of fact, when I was with the U.S. Navy we were required to have Outlook open all of the time due to security notices that would be released at almost any time. It wasn't an issue.

 

[Hello1]

COM (Component Object Model) methods have an issue with Outlook because it gets "picky" about whether or not another instance of Outlook exists when you try to launch it. I.e. it has special startup rules.



The above is correct Outlook behavior in that if Outlook isn't up it cannot establish the socket to its mail gateway server and that is how things get OUT of the Outbox. If you have things in the Outbox, they are SUPPOSED to stay there until Outlook is active. Access isn't sending mail. It is merely depositing mail into a structure exposed by COM methods for Outlook. But Access cannot send mail directly in the way that Outlook does it.

If you are sending text-only mail, you could try using CDO (Collaboration Data Objects) as a way to build and send e-mail without Outlook, though typically to do this on a normal business network, you would need your security guys to allow that. Using CDO, you can build a mail message and send it directly from Access via SMTP, but if you are going through Outlook, there is the chance that you would have sent using some other protocol like POP3 or IMAP - and there are a couple of other Outlook options as well.

Using CDO, your messages are sent quickly or else you get an error message quickly. No delays because there is no "staging" - no Outbox. There are pros and cons to this method and I don't recommend it if you want to do anything fancy with the e-mails. With Outlook and COM methods, "fancy" is possible. With CDO, not so much.

I did all that work for Outlook and then found out that they cant run Outlook on their PC, some error pops-up of which im not yet sure about what, but I do suspect the Username of the windows being in special characters coz I tried quite some solutions, nothing worked.

So at the end I used CDO as you suggested, works quite well. I had some trouble putting the signature, actually the .html file in the .HTMLBody (not showing special characters correctly) and still didnt find an easy way to add a logo image to the .HTMLBody. Another downside is that I had to enable "less secure apps" option in gmail to be able to send the emails, also the actual sending is not that fast as Outlook. For few emails its acceptable but for many it takes quite some time and thats not really good for the user.
When it starts executing the .Send it takes hmm about 5 to 10 seconds I would say for one email, other lines of the code are executed fast.

 

[Gasman]

Might be this one?

https://support.microsoft.com/en-gb/help/3189806/a-program-is-trying-to-send-an-e-mail-message-on-your-behalf-warning-i