Thread: Checkbox to trigger email via CDO

I am trying to generate an email message through CDO when a checkbox is marked. Right now I get an error message

Runtim error '-2147220960 (80040220)': The "SendUsing" configuration value is invalid.

I am new to Visual Basic but not to command lines and syntax. This is on a work network. If you want to know more about what I am trying to do see after my code:

Sub CheckBox2_Click()
If Range("L2").Value = True Then

CDO_Mail_Small_Text

End If
End Sub

Sub CDO_Mail_Small_Text()
    Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    '    Dim Flds As Variant

    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/sendusing") = 2
     '       .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
     '                      = "http://mail.fairview.org"
     '       .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
     '       .Update
     '   End With

    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"

    With iMsg
        Set .Configuration = iConf
        .To = "skearne2@fairview.org"
        .CC = ""
        .BCC = ""
        .From = """Parts Order"" <skearne2@fairview.org>"
        .Subject = "Emergency Order Generated"
        .TextBody = strbody
    ' Set importance or Priority to high
        .Fields("urn:schemas:httpmail:importance") = 2
        .Fields("urn:schemas:mailheader:X-Priority") = 1
        .Send
    End With

End Sub

In a nutshell, I have a shared spreadsheet (through a shared folder on the network) that will have part orders inputed. If the part is needed ASAP there is a check box. When the box is checked I would like an email sent to me to tell me so. I don't want the user to deal with any prompts or having Outlook open up on them, just a click and go kind of deal.

If I'm going about it wrong let me know what alternatives I have. I would like this done within the week.

Thanks in advance.

I never spent much time on it, but here is the only way I got CDO to work from Excel:

    Dim objMessage As Object
    Dim objConfig As Object
    Dim objFields As Object
    
    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
    
    'Delivery Status Notifications
    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 objMessage = CreateObject("CDO.Message")
    Set objConfig = CreateObject("CDO.Configuration")
    
    '==This section provides the configuration information for the remote SMTP server.
    
    Set objFields = objConfig.Fields
    With objFields
      .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
    
      'Name or IP of Remote SMTP Server
      .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "" '*** ADD SMTP SERVER NAME HERE
    
      'Type of authentication, NONE, Basic (Base64 encoded), NTLM
      .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic
      
'      'Your UserID on the SMTP server
'      .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "" '*** ADD ACCOUNT NAME HERE
'
'      'Your password on the SMTP server
'      .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "" '*** ADD ACCOUNT PASSWORD HERE
      
      'Server port (typically 25)
      .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
      
      'Use SSL for the connection (False or True)
      .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False
      
      'Connection Timeout in seconds (the maximum time CDO will try to establish a connection to the SMTP server)
      .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
      
      .Update
    End With
    
    With objMessage
        Set .configuration = objConfig
        
        .Subject = ""
        .From = ""
        .To = ""
        .TextBody = ""
        .Send
    End With
    
    Set objMessage = Nothing
    Set objConfig = Nothing
    Set objFields = Nothing