Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Aug 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    email from query (A2K_SR-1)

    Does anyone have an answer for this dilemma? If not then I'll be eating a little crow...

    I said that MS Access could generate individual emails for supervisors based on a query of employee information.

    ---begin 'cry for help' snippet-----------------------------------------------

    I have a report that groups results by EMPLOYEE and groups EMPLOYEE results to a SUPR.
    i.e.
    employee #1: has 4 rows returned
    employee #2: has 8 records returned
    employee #3: null set returned ...etc.
    and each EMPLOYEE is grouped under a SUPR.
    i.e.
    SUPR #1: has employee #1, employee #3, employee #5
    SUPR #2: has employee #2, employee #9, Employee #12...etc.

    I want to generate email(s), unique to the SUPR, and have the email provide the employee & row info.
    i.e.
    EMAIL #1:
    TO: SUPR #1
    SUBJECT: Emp Report
    BODY:
    employee #1
    Row #1
    Row #2
    Row #3
    Row #4
    employee #3
    Row = null set
    employee #5
    Row...
    etc
    ---end email---

    EMAIL #2:
    TO:SUPR #2
    Subject: Emp Report
    BODY:
    Employee #2
    Row #1
    Row #2
    Row....etc
    --end email---

    etc...thru each of the SUPR


    ----end 'cry for help' snippet----

    Can this be done? Is it a SQLquery or utilize a make-table query?

    Any suggestions would be appreciated.

    thanks...Brad

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: email from query (A2K_SR-1)

    If you use Outlook, us can use Automation to control Outlook from Access. If you do a search in this Forum on Outlook.Application, you'll find lots of info. For instance in <post#=158114>post 158114</post#> and <post#=133130>post 133130</post#>.

  3. #3
    New Lounger
    Join Date
    Aug 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: email from query (A2K_SR-1)

    Thank you for your quick reply.

    I did find a number of posts that will automate sending a general, or singular, email body to mulitple emails. BUT, I'm looking for a way to create content specific emails to a person based on a query result. The content of each email is unique...Maybe I'm looking to closely and missing the bigger picture.

    sorry to sound so dense...Brad

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: email from query (A2K_SR-1)

    One method is to construct a version of the query that accepts a supervisorid (? Sorry I can't see the original post anymore) as a parameter, so the query will at any time contain only the info you want that person to see.
    Next use DAO to generate a recordset of all the supervisors, and loop through the recordset. For each record in the recordset, you need to pass the supervisorID to the query.
    There is probably a more sophisticated way to do this, but one apporach is to have a textbox on a form. Have the query use that as its source of supervisorID, and set its value from the recordset:
    e.g forms!frmyform!txtsupervisor = rs!supervisorID
    Then after you have set that use, docmdsendobject to email the report.

    Each report will then only contain the relevant info.
    Regards
    John



  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: email from query (A2K_SR-1)

    John has already given you the basic idea. Here is some air code that sends e-mails with specific information to each supervisor. It's menat to give an example of how to compose and send e-mails whose contents vary from Access.

    Sub SendMail()
    Dim dbs As DAO.Database
    Dim rstSupervisors As DAO.Recordset
    Dim rstEmployees As DAO.Recordset
    Dim rstInfo As DAO.Recordset
    Dim olApp As Outlook.Application
    Dim olMailItem As Outlook.MailItem
    Dim blnStartOutlook As Boolean
    Dim strBody As String

    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")
    If olApp Is Nothing Then
    Set olApp = CreateObject("Outlook.Application")
    If olApp Is Nothing Then
    MsgBox "Can't start Outlook", vbExclamation
    Exit Sub
    End If
    blnStartOutlook = True
    End If
    On Error GoTo Err_SendMail

    Set dbs = CurrentDb
    Set rstSupervisors = dbs.OpenRecordset("tblSupervisors")
    ' Loop through supervisors
    Do Until rstSupervisors.EOF
    Set olMailItem = olApp.CreateItem(olMailItem)
    olMailItem.Recipients.Add rstSupervisors!EMailAddress
    olMailItem.Subject = "Employee report for " & rstSupervisors!LastName
    strBody = ""
    Set rstEmployees = dbs.OpenRecordset("SELECT * FROM tblEmployees WHERE SupervisorID = " & _
    rstSupervisors!SupervisorID)
    ' Loop through employees
    Do Until rstEmployees.EOF
    strBody = strBody & "Employee: " & rstEmployees!EmployeeID & vbCrLf
    Set rstInfo = dbs.OpenRecordset("SELECT * FROM tblInfo WHERE EmployeeID = " & _
    rstEmployees!EmployeeID)
    If rstInfo.RecordCount = 0 Then
    strBody = strBody & "No info records for this employee" & vbCrLf
    Else
    ' Loop through employee info
    Do Until rstInfo.EOF
    strBody = strBody & rstInfo!InfoText & vbCrLf
    rstInfo.MoveNext
    Loop
    End If
    rstInfo.Close
    rstEmployees.MoveNext
    Loop
    rstEmployees.Close
    olMailItem.Body = strBody
    olMailItem.Send
    rstSupervisors.MoveNext
    Loop
    rstSupervisors.Close

    Exit_SendMail:
    On Error Resume Next
    rstInfo.Close
    Set rstInfo = Nothing
    rstEmployees.Close
    Set rstEmployees = Nothing
    rstSupervisors.Close
    Set rstSupervisors = Nothing
    Set olMailItem = Nothing
    If blnStartOutlook Then
    olApp.Quit
    End If
    Set olApp = Nothing
    Exit Sub

    Err_SendMail:
    MsgBox Err.Description, vbExclamation
    Resume Exit_SendMail
    End Sub

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: email from query (A2K_SR-1)

    I don't use Outlook as my email program, and I deal with quite a lot who don't, so my preference is to use docmd.sendobject if I can. This works with whatever email program the user happens to use.
    Regards
    John



  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: email from query (A2K_SR-1)

    Good point. The main goal of the code I posted was to show how one can compose the body of an e-mail by concatenating strings containing info from recordsets. This technique can be used for the MessageText argument of DoCmd.SendObject too.

  8. #8
    New Lounger
    Join Date
    Aug 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: email from query (A2K_SR-1)

    Thank you for the code. This is exactly what I needed.

    Brad

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •