Access 07 - Send an Email when command button clicked.

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wlwebbAuthor Commented:
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
wlwebbAuthor Commented:
Tried it with and without Outlook being open
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Did you change the server line?   Is your mail server on port 25?
wlwebbAuthor Commented:
all those lines are remarked out..........  never executes those...
wlwebbAuthor Commented:
PS.... No onsite email server setup.... uses internet connection...... (but maybe that's what I don't understand)
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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

wlwebbAuthor Commented:
OHHHHH..... I'll see if I can figure out what's in my Outlook setup then....
wlwebbAuthor Commented:
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.

wlwebbAuthor Commented:
Never mind.........  Got it....... found a related thread that gives me the authenticate, username and password items that needed to be added.
wlwebbAuthor Commented:
Had to add a bit of code .... see last post above this regarding:

            .Item("") = 1
            .Item("") = ""
            .Item("") = "xxxpasswordxxx"
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.