CDO strange problem (1 Viewer)

[ellenr]

My cdo code for sending emails has been working error free for several years. Now, a strange problem has arisen. I have a group of 40 people in the membership list, each with a selection box for receiving the next email. I just sent out emails to the entire group via my email form. 38 were delivered, 2 email addresses threw an error. Both problem email addresses were gmail addresses, as were the majority of the successful addresses. The error was: "555 5.5.2 Syntax error. f9sm6810330qkk.115 - gsmtp". The sender email address was my gmail address in both instances. I then sent each of the recipients the exact same body and used the exact same email address from my personal Outlook and both were delivered to the recipient. The body of the email was a simple invitation to an event. This has happened twice to the same two email addresses. I don't have a clue how to fix or troubleshoot. Thanks for any help you can give me!

 

[Ranman256]

we'd have to see the code, and the point where it fails.

 

[isladogs]

Its not clear whether you are trying to send CDO email using a GMail account
If so, you need to manage the additional security that Google added to GMail accounts
In case it helps, the final page of the attached document outlines what is required

 

[ellenr]

Its not clear whether you are trying to send CDO email using a GMail account
If so, you need to manage the additional security that Google added to GMail accounts
In case it helps, the final page of the attached document outlines what is required

 

[ellenr]

Thanks for the document. I won't have a chance until tomorrow to study it and post code, etc. but will get back to this then. I need to figure this out! Just note that the cdo routine happily sent 38 emails, the majority of which are gmail addresses. Also, I sent using my email address as sender and I do have less secure setting on my gmail account.

 

[isladogs]

I would recommend the other GMail option - Set App Password

 

[ellenr]

we'd have to see the code, and the point where it fails.

Ranman256, I have no way to tell where it fails that I know of. All other gmail emails arrive at their destination. I harvest the error message in case of failure as is specified in my first post above.

 

[Uncle Gizmo]

I would recommend the other GMail option - Set App Password

I got an idea that they have discontinued that option Colin. Not 100% sure....

 

[isladogs]

No - its still available: Sign in with App Passwords - Google Account Help

 

[ellenr]

Not sure what happened--I posted code several days ago. Anyway, here it is again. I thank you for taking a look at it. As I said, it has worked for several years and only fails on these two legitimate gmail addresses. I just cannot figure out a way to troubleshoot.

Private Function Command67Send()

On Error GoTo errRoutine

Dim dbs As Database
   Set dbs = CurrentDb()
Dim rstSendinvites As Recordset
Dim sname As String, sEmail As String, sinvitsentdate As String, sSent As String
Dim sServer As String, sSenderEmail As String, sSenderPassword As String, sSendError As String
Dim Salutation As String

Dim iInvitsent As Integer, iSendinvite As Integer, jk As Integer
Set rstSendinvites = dbs.OpenRecordset("sendinvites", dbOpenDynaset)
DoCmd.Hourglass (-1)
Dim sFromField As String, sReplytoEmailAdd As String, sSubjectLine As String, _
    sAttachmentLocation As String, sAttach As String, sBody As String, strbody As String, sbounce As String
    
If Me.Dirty Then Me.Dirty = False   'force save current record
    Salutation = Me.Salutation
    If Len(Salutation) < 2 Then
        Salutation = "Dear"
    End If
    
    sFromField = Me.FromField
    sReplytoEmailAddr = Me.ReplyToEmailAddr
    sSubjectLine = Me.SubjectLine
    If Not IsNull(Me.AttachmentLocation) Then sAttachmentLocation = Me.AttachmentLocation
    If InStr(sAttachmentLocation, ";") > 0 Then
        sAttach = Trim(Mid(sAttachmentLocation, InStr(sAttachmentLocation, ";") + 1))
        sAttachmentLocation = Trim(Left(sAttachmentLocation, InStr(sAttachmentLocation, ";") - 1))
    End If
    
    If Attach1 Then
        sAttachmentLocation = CurrentProject.Path & "\CLGAFullList.PDF"
        DoCmd.OutputTo acOutputReport, "CLGAFullList", acFormatPDF, sAttachmentLocation
        If Attach2 Then
            sAttach = CurrentProject.Path & "\Schedule.PDF"
            DoCmd.OutputTo acOutputReport, "Schedule", acFormatPDF, sAttach
        End If
    Else
        If Attach2 Then
            sAttachmentLocation = CurrentProject.Path & "\Schedule.PDF"
            DoCmd.OutputTo acOutputReport, "Schedule", acFormatPDF, sAttachmentLocation
        End If
    End If

    On Error GoTo 0
    If IsNull(Me.Body) Then
        sBody = " "
    Else
        sBody = Me.Body
    End If
    
    sServer = Me.MoSender             'sender credentials picked up from registration file
    sSenderEmail = Me.MoEmail
    sSenderPassword = Me.MoPassword
    sPort = Me.MoPort
    
    sSSL = True
    If Me.MoSSL = 0 Then sSSL = False

    sAuth = True
    If Me.MoAuth = 0 Then sAuth = False
    
Dim objMsg As Object
Dim objConf As Object
Dim objFlds As Object

Const cdoDSNDefault = 0 'None
Const cdoDSNNever = 1 'None
Const cdoDSNFailure = 2 'Failure
Const cdoDSNSuccess = 4 'Success
Const cdoDSNDelay = 8 'Delay
Const cdoDSNSuccessFailOrDelay = 14 'Success, failure or delay''

Set objMsg = CreateObject("CDO.Message")
Set objConf = CreateObject("CDO.Configuration")

Set objFlds = objConf.Fields
With objFlds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'NTLM method
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = sServer   
.Item("http://schemas.microsoft.com/cdo/configuration/smptserverport") = sPort   '465
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = sAuth   ' True
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = sSSL  'True
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = sSenderEmail  
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = sSenderPassword    
  .Update
End With

Dim messageTrigger As Integer
messageTrigger = 0
Dim rstEmailLog As Recordset
Set rstEmailLog = dbs.OpenRecordset("EmailLog", dbOpenDynaset)

With rstSendinvites
If Len(sAttachmentLocation) > 0 Then objMsg.Addattachment sAttachmentLocation
If Len(sAttach) > 0 Then objMsg.Addattachment sAttach   'add second attachment
    Do While Not .EOF
        sname = .Fields("namealpha")
        snameKEEP = .Fields("namealpha")
        mName = .Fields("propername")
        sname = Mid$(sname, InStr(sname, ",") + 2)
        sEmail = .Fields("emailaddr")
        sbounce = Me.Mobounce
        strbody = sBody
                      
        Dim strHTML
        strHTML = "<FONT Face='Comic Sans MS' Size=3>" & Salutation & " " & sname & ",<br><br>" & strbody & "</font>"
        strHTML = strHTML & "</BODY></HTML>"
'    sServer = Me.MoSender             'sender credentials picked up from registration file
'    sSenderEmail = Me.MoEmail
'    sFromField = Me.FromField                   Sender's name from email setup screen
'    sReplytoEmailAddr = Me.ReplyToEmailAddr     Reply to email addr from email setup screen
                With objMsg
                  Set .Configuration = objConf
                  .To = sEmail        
                  .from = sFromField & " <" & sReplytoEmailAddr & ">"
                  .sender = sReplytoEmailAddr
                  .ReplyTo = sReplytoEmailAddr
                  .Subject = sSubjectLine
                  .htmlbody = strHTML
                        
                  On Error Resume Next
                  .send
                        sSent = "Sent"
                        If err.Number <> 0 Then
                            sSent = "Failed"   'this is the log entry
                            messageTrigger = -1
                            .To = Me.ReplyToEmailAddr
                            .Subject = "Delivery Failure to " & mName
                            .htmlbody = "Email to " & mName & "  Failed:  " & Error$
                            strHTML = "Email to " & mName & "  Failed:  " & Error$
                            .send
                            On Error GoTo errRoutine
                            GoTo skipper 'skip record update to uncheck sendemail box
                        End If
                   On Error GoTo errRoutine
                End With
                                        
        .Edit
        .Fields("sendemail") = 0
        .Update
skipper: With rstEmailLog
            .AddNew
            .Fields("FromField") = sFromField
            .Fields("Sent") = sSent
            .Fields("ReplyToaddr") = sReplytoEmailAddr
            .Fields("SendToaddr") = sEmail
            .Fields("SubjField") = sSubjectLine
            
            .Fields("BodyField") = strHTML
            .Fields("MemberName") = snameKEEP
            .Fields("Attachment") = sAttachmentLocation
            .Fields("Screen") = "Invites"
            
            .Fields("EmailClient") = sSenderEmail
            .Fields("smtp") = sServer
            .Fields("usedSSL") = sSSL
            .Fields("usedAUTH") = sAuth
            .Fields("usedPort") = sPort
            '.Fields("timestamp") = Now()
            .Update
        End With
        If InStr("strbody", "transport") > 0 Then
            MsgBox "Lost Transport connection--Press Send Email button again.", vbInformation, "Caution!"
            GoTo results_Exit
        End If
        .MoveNext
    Loop
End With
    If Attach1 Then
        Kill (sAttachmentLocation)
        If Attach2 Then
            Kill (sAttach)
        End If
    End If
DoCmd.Close acForm, "EmailSetupJean", acSaveYes

results_Exit:
    Set objMsg = Nothing
    Set rstEmailLog = Nothing
    Set rstSendinvites = Nothing
    If messageTrigger Then
        MsgBox "Check Email Log for Failed Email Addresses", vbInformation, "Caution!"
    End If
    DoCmd.Hourglass (0)
    Exit Function

errRoutine:
    If err = -2147024894 Then
        MsgBox "The system cannot find the attachment file.", vbInformation, "Caution!"
    Else
        MsgBox Error$
    End If
    Resume results_Exit

End Function

 

[isladogs]

I've quickly skimmed your code but not studied it properly.
If it works for other email addresses, it suggests your code and settings are OK

So please try the following to hopefully eliminate some possible issues:
a) use my CDO EMail Tester and attempt to send a test email to each of those GMail addresses
If it doesn't work, check the error message displayed against the supplied help file
b) As you are using GMail as a Send address, try setting an App Password first for my CDO EMail tester and then for your own Access app

 

[ellenr]

I've quickly skimmed your code but not studied it properly.
If it works for other email addresses, it suggests your code and settings are OK

So please try the following to hopefully eliminate some possible issues:
a) use my CDO EMail Tester and attempt to send a test email to each of those GMail addresses
If it doesn't work, check the error message displayed against the supplied help file
b) As you are using GMail as a Send address, try setting an App Password first for my CDO EMail tester and then for your own Access app

isladogs, I finally have time to get back on this--downloaded your zip and it only has the documentation, not the program. Where might I find it?

 

[Isaac]

@ellenr
I recommend you check this out and see what you can adjust that may be relevant. This suggests it may have nothing to do with security protocols. I can't explain why you might get this with just a few of your emails, but you may discover that on your end - or maybe you can make an adjustment that solves it regardless of figuring out the difference.

email - 555 5.5.2 Syntax error. gmail's smtp - Stack Overflow

 

[isladogs]

Its available here: CDO EMail Tester - Mendip Data Systems

 

[ellenr]

Isladogs, I have tested using your program. One delivered properly, the other said:

Error # -2147220977
The server rejected one or more recipient addresses. The server response was 555 5.5.2 Syntax error: a207sm2377284qkc. 135 -gsmtp

 

[The_Doc_Man]

The problem with error 555 is that the last digit is vendor-specific. Errors 5xx = "permanent negative completion" - usually meaning syntax or bad semantics; x5x = "problem with mail receiver" (i.e. receiver just showed you the finger); but the xx5 is not common. I would look at the article that Isaac posted because it might be related.

Among other things, the article DOES suggest that you need to be "picky" about the format of the outgoing address. The fact that Outlook accepted the message but CDO didn't suggests an imperfection, perhaps a leading zero or something, in the destination address. Outlook doesn't care about spaces outside of the address but I must admit I don't know how picky CDO can get. I know I never had problems but that doesn't mean I know everything about CDO.

EDIT: @ellenr, what is the 135 in that response? If it is a file type, that space in front of 135 is the error because spaces aren't allowed in that part of a file specification syntax.

 

[isladogs]

I've never seen that precise error before. If you look at the help file, the nearest equivalent is

 

[ellenr]

Doc_Man, I obviously don't have a clue. My program records the response in case of an error so what follows is a list of responses for one of the two rejected emails.

after changing recipient to: sEmail & " <" & sEmail & ">" I copied and pasted the error:
Email to Judy D Failed: The server rejected one or more recipient addresses. The server response was: 555 5.5.2 Syntax error. l186sm2281249qke.92 - gsmtp

previous try (after adding "< >" around sEmail):
Email to Judy D Failed: The server rejected one or more recipient addresses. The server response was: 555 5.5.2 Syntax error. s17sm2061580qta.44 - gsmtp

previous (02/26/21):
Email to Judy D Failed: The server rejected one or more recipient addresses. The server response was: 555 5.5.2 Syntax error. v6sm6607045qkf.132 - gsmtp

previous (o3/24/21):
Email to Judy D Failed: The server rejected one or more recipient addresses. The server response was: 555 5.5.2 Syntax error. l191sm758352oih.16 - gsmtp


Incidently, I have been able to send to my own email address before and after the addition of the "< >" in the To: address.

 

[ellenr]

I've never seen that precise error before. If you look at the help file, the nearest equivalent is

View attachment 90476

It was set at 1.

 

[isladogs]

OK so either there is a problem with that email address OR you should try setting an app password as previously suggested.
Doing that is quick, easy and free. Why not try it?