Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Mar 2003
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    E-mail Report (2000)

    I know that someone would have had to do this before. I have a report that is broken up by employee. There is a page break between each employee. We would like to e-mail to each employee their part of the report. I'd appreciate any ideas of how anyone has done this. Thanks!

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

    Re: E-mail Report (2000)

    You would have to write code to:
    Open the employees table as a recordset,
    Loop through the records of the recordset,
    For each employee, open the report filtered to display only the data for the employee,
    Use DoCmd.SendObject to e-mail the report to the employee.

  3. #3
    Lounger
    Join Date
    Mar 2003
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: E-mail Report (2000)

    Thanks! I got it to e-mail the whole report to everyone in my employee table. Now, I'm working on just e-mailing the employee's part of the report. The report is broken up by employee. The data from the report comes from a query. In the query, I have a field called Condition Assigned to which comes from EmployeName field in the Employees table - it was a lookup on the data entry form. The e-mail address in the Employees table is called Email. I do not have this field in my query. Also, I do not have the Employees table in my query.

    From your last post, you said to open the report for each employee. I guess I'm confused as to how to pass the employee name to the query, taking into account that the employee name field is named employename and the employee e-mail address is named email.

    Please help. Here is the code I wrote so far.

    On Error GoTo Err_Command20_Click

    Dim DB As Database: Set DB = DBEngine(0)(0)
    Dim RS As Recordset: Set RS = DB.OpenRecordset("Test Employees")
    Dim Employee As Field: Set Employee = RS![Email]
    Dim Employee2 As Field: Set Employee2 = RS![EmployeName]
    Dim stDocName As String: stDocName = "Test Pending Conditions by Person"


    While RS.EOF = False

    DoCmd.OpenReport "Test Pending Conditions by Person", acViewNormal, where [Condition Assigned To] = Employee2

    DoCmd.SendObject acReport, stDocName, acFormatRTF, _
    "Postmaster", , Employee, "Report Text", _
    "Here is the test report.", False
    RS.MoveNext

    Wend
    Set Victim = Nothing
    RS.Close: Set RS = Nothing
    Set DB = Nothing


    Exit_Command20_Click:
    Exit Sub

    Err_Command20_Click:
    MsgBox Err.Description
    Resume Exit_Command20_Click

    End Sub

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

    Re: E-mail Report (2000)

    This turned out to be more complicated than I originally thought, and the method I proposed won't work. Sorry about that. Here is a solution (hopefully).

    1. In a standard module, define a public variable

    Dim gstrEmployeeName As String

    2. Put the following code in the On Open event procedure of the report:

    If Not (gstrEmployeeName = "") Then
    Me.RecordSource = "SELECT * FROM [query name] WHERE [Condition Assigned] = " & Chr(34) & gstrEmployeeName & Chr(34)
    End If

    where query name must be replaced by the name of the query that acts as record source of the report.

    3. Modify the code you had in Command20_Click as follows:

    Private Sub Command20_Click()
    On Error GoTo Err_Command20_Click

    Dim DB As DAO.Database
    Dim RS As DAO.Recordset
    Dim Employee As DAO.Field
    Dim Employee2 As DAO.Field
    Dim stDocName As String

    Set DB = CurrentDb
    Set RS = DB.OpenRecordset("Test Employees", dbOpenDynaset)
    Set Employee = RS![Email]
    Set Employee2 = RS![EmployeName]
    stDocName = "Test Pending Conditions by Person"

    Do While RS.EOF = False
    gstrEmployeeName = Employee2
    DoCmd.SendObject acReport, stDocName, acFormatRTF, _
    "Postmaster", , Employee, "Report Text", _
    "Here is the test report.", False
    RS.MoveNext
    Loop

    Exit_Command20_Click:
    On Error Resume Next
    gstrEmployeeName = ""
    Set Employee = Nothing
    Set Employee2 = Nothing
    RS.Close
    Set RS = Nothing
    Set DB = Nothing
    Exit Sub

    Err_Command20_Click:
    MsgBox Err.Description
    Resume Exit_Command20_Click
    End Sub

  5. #5
    Lounger
    Join Date
    Mar 2003
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: E-mail Report (2000)

    I did what you said in your previous e-mail. It

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

    Re: E-mail Report (2000)

    I tested the code with Access 2002 (in a database in Access 2000 format) and with Outlook 2002 as e-mail program. It works there without opening the report explicitly using DoCmd.OpenReport. There is the well-known problem with Outlook security: each time the code tries to send an e-mail, you're warned that a program is trying to send an e-mail on your behalf, and you have to wait 10 seconds before you can click Yes to allow the program to proceed. You can get around this by installing ClickYes or by using the Outlook Redemption Library.

    It seems improbable to me that Access 2000 wouldn't be able to send a report without opening it, but I can't test that, since I don't have Access 2000.

    I notice one problem with your code:

    gstrEmployeeName = Employee

    will set gstrEmployeeName to the same employee each time; it should be something like

    gstrEmployeeName = rs!Employee

  7. #7
    Lounger
    Join Date
    Mar 2003
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: E-mail Report (2000)

    Thanks! I tried clickyes and it's working now!

Posting Permissions

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