Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old January 21st, 2005, 09:56 AM
Authorized User
 
Join Date: Jan 2005
Location: , , .
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default Sending automatic emails in Access

How can I make access send an email to a user in one of my tables for example if someone changes their status ID to 2. I made the report just not sure on how to automatically send it when statusID = 2.
Thanks a lot for the help

Reply With Quote
  #2 (permalink)  
Old January 24th, 2005, 04:09 PM
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

Try this:

On your form where the user is changing the ID, on the combo box's After Update Event, put this sort of code:

'==================================
strStatus = Me.YourStatusComboBoxName

If strStatus = 2 Then
   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 = "Your Subject"
   objMessage.Sender = "YourDatabase@yourdomain.com"
   objMessage.To = strUserName & "@yourdomain.com"
   objMessage.TextBody = "This is your message"

'I am not sure how to make an attachment of your report.

'==This section provides the configuration information for the remote SMTP server.

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.aoc.gov"

'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") = "****"

'Your password on the SMTP server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "****"

'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

End If
'=================================

I hope this gets you started.

mmcdonal
Reply With Quote
  #3 (permalink)  
Old January 24th, 2005, 05:55 PM
Authorized User
 
Join Date: Jan 2005
Location: , , .
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks a lot I'll play around with it

Reply With Quote
  #4 (permalink)  
Old January 31st, 2005, 03:48 PM
Authorized User
 
Join Date: Jan 2005
Location: , , .
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Not really working out, Any other clues. I would like it to look at my users table and send it to the email under the record currently selected. Also I need it to send a report. Any help would be great
Thanks

Reply With Quote
  #5 (permalink)  
Old January 31st, 2005, 06:10 PM
Authorized User
 
Join Date: Jul 2004
Location: clapton-in-gordano, n.somerset, United Kingdom.
Posts: 46
Thanks: 0
Thanked 1 Time in 1 Post
Default

The following code is easily amendable to do whatever you want.

Sub email()
Dim olApp As Outlook.Application
Dim i%,Body$, Recipient$, Attachment$, Subject$

Set olApp = CreateObject("Outlook.Application")
    SenderDetails = olApp.Session.CurrentUser

    i = InStr(1, SenderDetails, ",", vbTextCompare)

                SenderDetails = Trim(Mid(SenderDetails, i + 1, Len(SenderDetails) - i))

                Body = "The text message you want to send"
                Recipient = "Recipents e-mail address"

                Subject = "whatever the subject is"
                Attachment = "Full path of whatever file you want to attach"
                With olApp.CreateItem(olMailItem)
                        .Subject = Subject
                        .Body = Body
                        .To = Recipient
                        .Attachment.Add Attachment
                        .send

                End With
Dim o As Outlook.MailItem
Set olApp = Nothing

End Sub

Try it and see.

Alan T


Reply With Quote
  #6 (permalink)  
Old February 1st, 2005, 11:34 AM
Authorized User
 
Join Date: Jan 2005
Location: , , .
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default

how can i relate this to a record set, because I have a table with employee name, email and status. Basically whenever their status is changed in a cboStatus to 2 I want it to automatically send an email to that person in the record.

Reply With Quote
  #7 (permalink)  
Old February 1st, 2005, 01:41 PM
Authorized User
 
Join Date: Jul 2004
Location: clapton-in-gordano, n.somerset, United Kingdom.
Posts: 46
Thanks: 0
Thanked 1 Time in 1 Post
Default

As I do not know your underlying data, there are two options. In both you will need to alter Sub email to Sub email(Recipient as string) and take Recipient out of the Dim statement.

1) On your checkbox's OnChange event include a line

Recipient = Me!Text1.value

where Text1 is a textbox holding the e-mail address

then

email(Recipient)

will pass the address to email and the message will go automatically. If your message and attachment also change, you will need to store these somewhere else in the form's underlying table and do for these what you have done for Recipient

2. Your underlying table is updated remotely. Suppose [code] is the field that populates cboStatus, and your underlying table is called tbl_data. In that table you have a field called [emailadd]

dim rst_emps as dao.recordset

set rst_emps = currentdb.Openrecordset("SELECT * FROM [tbl_data] WHERE [tbl_data].[code] = 2")

 with rst_emps
          .movefirst
          Do until .EOF
            email(.Fields("emailadd").value)
            .movenext
          Loop
End With

Set rst_emps = Nothing

This will email all employees with [code] = 2

Alan T




Reply With Quote
  #8 (permalink)  
Old February 1st, 2005, 02:36 PM
Authorized User
 
Join Date: Jan 2005
Location: , , .
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok I have made this but still struggling, someone please tell me what I am doing wrong:

Private Sub cmdSend_Click()
    Dim rst As DAO.Recordset
    Dim db As DAO.Database
    Dim appOutLook As Outlook.Application
    Dim MailOutLook As Outlook.MailItem

    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)

    Set rst = db.OpenRecordset("SELECT * FROM tUsers WHERE ID=1")

    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)
    With MailOutLook
        .To = rst!Email
                .Subject = "Testing"
                .Body = "Test number one"
                .Send
    rst.Close
    Set rst = Nothing
    Set db = Nothing
    End With
End Sub

This is suppose to send an email when I press a cmd button, to a user with user id = 1. The table tUsers has fields: ID, Name, Email

Thanks for the help

Reply With Quote
  #9 (permalink)  
Old February 3rd, 2005, 07:58 PM
Registered User
 
Join Date: Feb 2005
Location: Peoria, IL, USA.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This is a different approach, based off of the command button wizard code. Although the code below uses the snapshot format, we went to a plain text message, as some of our customers do not have Microsoft Office.


Private Sub SendMail_Click()

'This will send an object, such as a report, along with your message when the button is clicked

Dim stDocName As String

On Error GoTo Err_SendMail_Click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    stDocName = "Name of Report You Want to Appear"

    'Use this to filter your report for the recipient's information based off of whatever identifier you are using.
    stLinkCriteria = "[AccountNumber]=" & Me![AccountNumber]

    DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

    'Note that the format chosen below is snapshot, which can only be read if the recipient has Microsoft Office
    'Also the Me.email refers to a field on the form with the recipient's e-mail.
    DoCmd.SendObject acReport, stDocName, acFormatSNP, Me.email, , , "Subject Line", "Your Message"

    DoCmd.Close acReport, stDocName

Exit_SendMail_Click:
    Exit Sub

Err_SendMail_Click:
    If Err.Number = 2498 Then
        DoCmd.Close acReport, stDocName
        MsgBox "Please enter email address first, and then try to send email again"
        Me.email.SetFocus
    Else
        MsgBox Err.Description
        DoCmd.Close acReport, stDocName
        Resume Exit_SendMail_Click
    End If

End Sub


Regards,

Bonnie

Reply With Quote
  #10 (permalink)  
Old February 8th, 2005, 12:08 PM
Authorized User
 
Join Date: Jan 2005
Location: , , .
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks it all works great with a couple changes, One more question, is there a way to make the report appear as the body of the email?

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
sending emails for all users in the domain arabuafef ASP.NET 1.0 and 1.1 Basics 6 December 6th, 2006 10:51 AM
Sending automatic emails maitias C# 2005 3 March 3rd, 2006 09:51 PM
Sending emails from smartphone application. unprsandeep C# 0 March 24th, 2005 02:01 PM
sending emails from vb.Net elan22 General .NET 5 May 27th, 2004 09:53 AM



All times are GMT -4. The time now is 05:22 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.