Access 07 - Send an Email when command button clicked.

Posted on 2014-08-19
Last Modified: 2014-08-19
Hello Experts.

I have tried following the examples on this site to no avail.  I am trying to automatically send an Maintenance Request email to specific contacts when the user selects a command button.  I found a thread regarding a "CDOSendEmail"

I copied the CDO Function into a new module  following is that function:
Option Compare Database
Option Explicit

Function CDOSendEmail(varFrom As Variant, strTo As String, strSubj As String, strMess As String)
    Dim strDefaultFrom  As String
    Dim objMess As Object
    Set objMess = CreateObject("CDO.Message")
    ' This is your default FROM address, and to be used if varFrom is Null or Empty
    strDefaultFrom = ""
    ' Subject
    objMess.Subject = strSubj
    ' Message
    objMess.textbody = strMess
    ' Determine if varFrom is Null/Empty and use default address,
    ' otherwise use the From address passed through the function call
    objMess.From = IIf(Nz(varFrom, "") = "", strDefaultFrom, varFrom)
    ' To
    objMess.To = strTo
    ' This is a simple example... you can do a lot more
    ' Send it
    ' Clear the object variable
    Set objMess = Nothing
End Function

Open in new window

Then for my command button i Call the following code:
Private Sub SendEmail()
Dim strSQL As String
Dim rs As DAO.Recordset
Dim a As Variant, b As String, c As String, d As String
Dim z As String, y As String

z = Me.sfrm_MaintReqMaintCat.Form![MaintReqCatID].Column(1)
If Me.sfrm_MaintRequestMemo.Visible = True Then
    y = Me.sfrm_MaintRequestMemo.Form![MaintReqLongDescr]
    y = z
End If
    strSQL = "SELECT MaintReqCatID, MaintReqEmailNotifyInactive, MaintReqCat, NotifyEmail, NickName, CompName " & _
    "FROM qry_MaintReqEmailNotify " & _
    "WHERE MaintReqEmailNotifyInactive=" & False & " AND MaintReqCat='" & z & "'"
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

a = "''" ' From
b = "'" & rs!NotifyEmail & "'"  'To
c = "Maintenance Request " & rs!CompName & " " & Me.MaintReqShortDescr ' Subject
d = y ' Message

Call CDOSendEmail(a, b, c, d)

End Sub

Open in new window

I am getting an error
Run-time error ' -2147220960 (80040220)
The "SendUsing" Configuration value is invalid.  I have tried it with and without the single quote around the email addresses of the from and the to.

Keeps bombing on the objMess.Send (line 28 of that code) near bottom of the Public Function

ALSO NOTE: I have tried this with Outlook already open and without Outlook already open.
Question by:wlwebb
    LVL 56

    Accepted Solution

    Since you haven't specified a server, user,etc a mail config needs to be setup already.   Typically one avoids all that with CDO code that looks something like this:

    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("") = 2
        '        .Item("") _
        '                       = "Fill in your SMTP server here"
        '        .Item("") = 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 = ""
            .CC = ""
            .BCC = ""
            .From = """Ron"" <>"
            .Subject = "New figures"
            .TextBody = strbody
        End With
    End Sub

    Open in new window


    Author Comment

    Thanks for the quick response.......!!!!!!!!!!

    Hangs on the .send

    at the very end of the code

    I copy and pasted all of it direct and remarked out all of my code....
    only change was the .From and the .To where I put my email address in both

    Author Comment

    Tried it with and without Outlook being open
    LVL 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    Did you change the server line?   Is your mail server on port 25?

    Author Comment

    all those lines are remarked out..........  never executes those...

    Author Comment

    PS.... No onsite email server setup.... uses internet connection...... (but maybe that's what I don't understand)
    LVL 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    To send mail, you need to talk to a mail server one way or another.    When you setup you email in outlook,  you use what your ISP gives you.   You name the server, username, password, authentication, etc

    You either need to setup a mail profile or un comment those lines and specify them here


    Author Comment

    OHHHHH..... I'll see if I can figure out what's in my Outlook setup then....

    Author Comment

    Ok.... I unremarked the iconf.load -1 through the End WIth

    I Keyed my Sendusing as ""
    I keyed my smtpserver = to ""
    and changed my port number

    Then I executed it and now I get......
    Runtime error 3749
    Fields update failed. For further information, examine the Status property of individual field objects.

    I tried it with Outlook open.

    I tried it with Outlook closed

    I then went to the Task Menu and ended the "Outlook.exe" process that was running...

    Still the same error.


    Author Comment

    Never mind.........  Got it....... found a related thread that gives me the authenticate, username and password items that needed to be added.

    Author Closing Comment

    Had to add a bit of code .... see last post above this regarding:

                .Item("") = 1
                .Item("") = ""
                .Item("") = "xxxpasswordxxx"

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now