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

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 = "[email protected]"
   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

Thanks a lot I'll play around with it

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

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

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.

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

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

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

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?