locked
CDO Mail error RRS feed

  • Question

  • Hi

    I get this error:

    When using this code:

    Option Compare Database
    Option Explicit
    
    
    Const cdoSendUsingPickup = 1    'Send message using the local SMTP service pickup directory.
    Const cdoSendUsingPort = 2    'Send the message using the network (SMTP over the network).
    
    Const cdoAnonymous = 0    'Do not authenticate
    Const cdoBasic = 1    'basic (clear-text) authentication
    Const cdoNTLM = 2    'NTLM
    
    '---------------------------------------------------------------------------------------
    ' Procedure : SendCDOMail
    ' Author    : CARDA Consultants Inc.
    ' Website   : http://www.cardaconsultants.com
    ' Purpose   : Send an e-mail using CDO Mail
    ' Copyright : The following is release as Attribution-ShareAlike 4.0 International
    '             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
    '
    ' Input Variables:
    ' ~~~~~~~~~~~~~~~~
    ' sTo         To Recipient email address string (semi-colon separated list)
    ' sSubject    Text string (HTML) to be used as the email subject line
    ' sBody       Text string to be used as the email body (actual message)
    ' sBCC        BCC Recipient email address string (semi-colon separated list)
    ' AttachmentPath    single value or array of attachment (complete file paths with
    '                   filename and extensions)
    '
    ' Revision History:
    ' Rev       Date(yyyy/mm/dd)        Description
    ' **************************************************************************************
    ' 1
    '---------------------------------------------------------------------------------------
    Function SendCDOMail(sTo As String, _
                         sSubject As String, _
                         sBody As String, _
                         Optional sBCC As Variant, _
                         Optional AttachmentPath As Variant)
        On Error GoTo SendCDOMail_Error
              'On Error GoTo Error_Handler
              Dim objCDOMsg             As Object
              Dim i                     As Long
    
    10        Set objCDOMsg = CreateObject("CDO.Message")
              'CDO Configuration
    20        With objCDOMsg.Configuration.Fields
    30            .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
                  'Server port (typically 25, 587)
    40            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = TempVars!smtpserverport
                  'SMTP server IP or Name
    50            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = TempVars!smtpserver
                  'Type of authentication, NONE, Basic (Base64 encoded), NTLM
    60            .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic
                  'SMTP Account User ID
    70            .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = TempVars!sendusername
                  'SMTP Account Password
    80            .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = TempVars!sendpassword
                  'Use SSL for the connection (False or True) -> If using SSL, do not specify the Port above
    90            .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False
    100           .Update
    110       End With
    
              'CDO Message
    120       objCDOMsg.Subject = sSubject
    130       objCDOMsg.From = DLookup("sendusername", "tbl_CDOSettings")
    140       objCDOMsg.To = sTo
              '    objCDOMsg.CC = sCC
    150       If Not IsMissing(sBCC) And IsNull(sBCC) = False Then
    160           objCDOMsg.BCC = sBCC
    170       End If
    180       objCDOMsg.TextBody = sBody
              ' Add attachments to the message.
    190       If Not IsMissing(AttachmentPath) Then
    200           If IsArray(AttachmentPath) Then
    210               For i = LBound(AttachmentPath) To UBound(AttachmentPath)
    220                   If AttachmentPath(i) <> "" And AttachmentPath(i) <> "False" Then
    230                       objCDOMsg.AddAttachment AttachmentPath(i)
    240                   End If
    250               Next i
    260           Else
    270               If AttachmentPath <> "" And AttachmentPath(i) <> "False" Then
    280                   objCDOMsg.AddAttachmentAttachmentPath
    290               End If
    300           End If
    310       End If
    320       objCDOMsg.Send
    
    
        
        On Error GoTo 0
        Exit Function
    
    SendCDOMail_Error:
    
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure SendCDOMail, line " & Erl & "."
    
    End Function

    smtpserverport 25

    smtpserver: smarthost.knowledgebase.se
    sendusername: peter@forssonline.com sendpassword: *******

    Is it my code?
    Is it the server settings?



    Cheers // Peter Forss Stockholm


    Monday, October 7, 2019 11:18 AM

Answers

  • Since it is complaining about Recipient addresses, I'd be reviewing the sTo value.

    Have you tested just sending an e-mail to yourself?

    I'd also recommend using SSL mode if you can for security/privacy reasons.


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Monday, October 7, 2019 11:56 AM
  • Using the following config

        With objCDOMsg.Configuration.Fields
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "YourEmailServer" 'When using SSL is often different
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "YourEmailAddress" 'the account username, in most case is the email address
            .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "YourPassword"
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
            .Update
        End With
    and then don't forget to adjust the .From line
    objCDOMsg.From = "YourEmailAddress"


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    • Marked as answer by ForssPeterNova Tuesday, October 8, 2019 10:19 AM
    Monday, October 7, 2019 2:31 PM
  • Peter - The company I work for blocks all ports being accessed from a client/workstation for security reasons. And there is no work-around other than white-listing the workstation's IP address. But because our workstations do not have static IP addresses, white-listing won't help. Lots of our software just suddenly stopped being able to send emails using CDO. The ports can only be accessed from another server. This might be the case for you as well. Contact your server person about using CDO.

    If there is no solution for CDO you might try everythingaccess.com vbMAPI. The code utilizes Outlook and can work around the Outlook security warnings. I found this to be the simplest solution for me.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Marked as answer by ForssPeterNova Tuesday, October 8, 2019 10:20 AM
    Monday, October 7, 2019 5:48 PM
  • Hi Peter

    You might find my CDO Email Tester utility useful

    http://www.mendipdatasystems.co.uk/email-tester/4594365455

    It includes a help file explaining all the error messages that can occur

    • Marked as answer by ForssPeterNova Tuesday, October 8, 2019 10:20 AM
    Tuesday, October 8, 2019 6:34 AM
  • Your original error message indicates, that your code is working and sent the mail, but the receiving server rejected it. That would be the source of a 500 error.

    Some receiving mail servers are very keen on checking that a sending IP address "belongs" to the sending domain. Most domains have a primary sending server which will be registered in that company's DNS settings. It could be that the IP address or hostname (smarthost.knowledgebase.se) of the sending SMTP server of yours is not registered in the DNS for the domain you use as sender.

    This is usually corrected by adding an SPF record in the DNS. That can only be done by an administrator of the domain's DNS server.

    In the meantime, feel free to attempt sending me some test mails; you have the address.


    Gustav Brock


    Tuesday, October 8, 2019 8:17 AM

All replies

  • Since it is complaining about Recipient addresses, I'd be reviewing the sTo value.

    Have you tested just sending an e-mail to yourself?

    I'd also recommend using SSL mode if you can for security/privacy reasons.


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Monday, October 7, 2019 11:56 AM
  • Hi Daniel

    Yes I have my own address as the Recipient.

    OK I will set SSL = True


    Cheers // Peter Forss Stockholm

    Monday, October 7, 2019 1:00 PM
  • Hi Daniel

    SSL is now True

    Still an error message (different now) but referring to line 320


    Cheers // Peter Forss Stockholm

    Monday, October 7, 2019 1:06 PM
  • Using the following config

        With objCDOMsg.Configuration.Fields
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "YourEmailServer" 'When using SSL is often different
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "YourEmailAddress" 'the account username, in most case is the email address
            .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "YourPassword"
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
            .Update
        End With
    and then don't forget to adjust the .From line
    objCDOMsg.From = "YourEmailAddress"


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    • Marked as answer by ForssPeterNova Tuesday, October 8, 2019 10:19 AM
    Monday, October 7, 2019 2:31 PM
  • Peter - The company I work for blocks all ports being accessed from a client/workstation for security reasons. And there is no work-around other than white-listing the workstation's IP address. But because our workstations do not have static IP addresses, white-listing won't help. Lots of our software just suddenly stopped being able to send emails using CDO. The ports can only be accessed from another server. This might be the case for you as well. Contact your server person about using CDO.

    If there is no solution for CDO you might try everythingaccess.com vbMAPI. The code utilizes Outlook and can work around the Outlook security warnings. I found this to be the simplest solution for me.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Marked as answer by ForssPeterNova Tuesday, October 8, 2019 10:20 AM
    Monday, October 7, 2019 5:48 PM
  • Hi Daniel

    Still error on row 320


    Cheers // Peter Forss Stockholm

    Tuesday, October 8, 2019 6:22 AM
  • Hi Bill

    I have now asked the technical guys about blocked ports.


    Cheers // Peter Forss Stockholm

    Tuesday, October 8, 2019 6:24 AM
  • Hi Peter

    You might find my CDO Email Tester utility useful

    http://www.mendipdatasystems.co.uk/email-tester/4594365455

    It includes a help file explaining all the error messages that can occur

    • Marked as answer by ForssPeterNova Tuesday, October 8, 2019 10:20 AM
    Tuesday, October 8, 2019 6:34 AM
  • Your original error message indicates, that your code is working and sent the mail, but the receiving server rejected it. That would be the source of a 500 error.

    Some receiving mail servers are very keen on checking that a sending IP address "belongs" to the sending domain. Most domains have a primary sending server which will be registered in that company's DNS settings. It could be that the IP address or hostname (smarthost.knowledgebase.se) of the sending SMTP server of yours is not registered in the DNS for the domain you use as sender.

    This is usually corrected by adding an SPF record in the DNS. That can only be done by an administrator of the domain's DNS server.

    In the meantime, feel free to attempt sending me some test mails; you have the address.


    Gustav Brock


    Tuesday, October 8, 2019 8:17 AM
  • Hi Gustav

    You just got mail. It works now. My "technical guy" did something ("modified the SMTP Gateway)

    I will ask him to be more specific on what he did.


    Cheers // Peter Forss Stockholm

    Tuesday, October 8, 2019 8:52 AM
  • Solved!

    Tech guy say "I am sorry, missed the final settings"

    Relay list (server was not allowed to send e-mail via smart host).


    Cheers // Peter Forss Stockholm

    Tuesday, October 8, 2019 10:21 AM