• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

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 = "namenamenameofdefaultemail@emailaddr.com"
    ' 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 = "'namenamenameofdefaultemail@emailaddr.com'" ' 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.
  • 8
  • 3
1 Solution
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    '        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
    '                       = "Fill in your SMTP server here"
    '        .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 = "ron@debruin.nl"
        .CC = ""
        .BCC = ""
        .From = """Ron"" <ron@something.nl>"
        .Subject = "New figures"
        .TextBody = strbody
    End With

End Sub

Open in new window

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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)PresidentCommented:
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 "xxxxx.xxxx.com"
I keyed my smtpserver = to "xxxxx.xxxxx.net"
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("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "xyzname@abc.com"
            .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "xxxpasswordxxx"

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 8
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now