Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old September 18th, 2006, 05:23 AM
Registered User
 
Join Date: Oct 2004
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to underscore10304
Default Auto send email from command button

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.

  #2 (permalink)  
Old September 18th, 2006, 07:38 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
  #3 (permalink)  
Old September 19th, 2006, 07:26 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
  #4 (permalink)  
Old September 26th, 2006, 06:41 AM
Registered User
 
Join Date: Oct 2004
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to underscore10304
Default

Haven't checked for answers for a while. Thanks v much to those who posted. I have plenty to go on - cheers guys.



Similar Threads
Thread Thread Starter Forum Replies Last Post
Store procedure,auto send email after X days.(+asp kumiko SQL Server 2005 2 February 29th, 2008 01:15 PM
How to send a auto confirmation mail?? silu95421 Classic ASP Databases 0 October 27th, 2007 04:46 AM
auto tracker function &send personalised email miracles Excel VBA 1 April 27th, 2007 04:43 AM
Send Auto Mail for Birthday wishes bspradeep Classic ASP Professional 4 February 28th, 2007 06:13 AM
auto send an emal Pavel Kadera Excel VBA 1 February 7th, 2005 12:48 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.