|
Subject:
|
Auto send email from command button
|
|
Posted By:
|
underscore10304
|
Post Date:
|
9/18/2006 5:23:47 AM
|
Hello,
I have a database that logs our company's faults and queries.
Currently, we enter into it, details of their fault, try to apply a fix and then notify them when the call has been closed.
We would like to implement a command bound to the 'JOB COMPLETE' check box that, when
ticked, fires off an email to the user who logged the fault, detailing that the job is
complete.
We have a drop down list of users ( source is a standard table ) - I'm presuming I need to
add a second column with their addresses for lookup purposes but am not entirely sure.
Also, my VBA is somewhat limited to docmd's so complex code might throw me a bit!
Would appreciate any assistance offered.
Many thanks.
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
9/18/2006 7:38:48 AM
|
Here is some generic code that works well on any SMTP server. You will need to supply the proper port, etc. I used variables for the Sender, To, etc.
'--------------------------
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
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = strSubject
objMessage.Sender = strSender
objMessage.To = strTo
objMessage.TextBody = "Add Text Here or use variable with message."
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.YourDomain.com"
'Type of authentication, NONE, Basic (Base64 encoded), NTLM
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic
'Your UserID on the SMTP server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusername") = "****" 'nothing on ours, maybe something on yours.
'Your password on the SMTP server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "****" 'nothing on ours, maybe something on yours
'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
'Use SSL for the connection (False or True)
objMessage.Configuration.Fields.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)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
objMessage.Configuration.Fields.Update
'==End remote SMTP server configuration section==
objMessage.sEnd
'------------------------
You want to make sure you declare your variables first. Then take the values from the form. For example, if you have a textbox with the receiver's email address, just make it invisible on the form, but take the value like this:
strTo = Me.txtUsersEmail
Or from a hidden column in a combo box (let's say the 3rd column)
strTo = Me.cboUser.Column(3) 'I think that is right, it will auto complete for you in VBA.
You Can take other items from the form, and have your message already in the code, etc.
Does this help?
mmcdonal
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
9/19/2006 7:26:29 AM
|
Look up help for the DoCmd.SendObject method. It tells you how to send a simple e-mail with or without an attachment. The e-mail system it uses is the default on on your computer.
Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
|
|
Reply By:
|
underscore10304
|
Reply Date:
|
9/26/2006 6:41:47 AM
|
Haven't checked for answers for a while. Thanks v much to those who posted. I have plenty to go on - cheers guys.
|
