Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2011
    Posts
    42

    Unanswered: sending filtered report to a stored e-mail

    Patience please :

    I have report : attendance record, the report show the staff attendance grouped by department, when i wish to print i choose the department from one combo box to open the selected department report.

    Now... If I wish to send this report to the department manager by email using an e-mail address stored in my database how to do this.

    I want the report to be filtered first before sending because I tried already using some code but I sends all departments in an PDF format file

    Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I tried already using some code but I sends all departments in an PDF format file
    Please post the code you're using now. It's almost impossible to figure out what's wrong without knowing what we're dealing with.
    Have a nice day!

  3. #3
    Join Date
    Mar 2011
    Posts
    42
    >>> here's the code I'm using <<<<<<<<<<

    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    Dim strSQL As String
    Dim eSub, eText As Variant
    strSQL = "Select * from EmailTable"
    eSub = Me!msgSubject
    eText = Me!msgMessage
    rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
    If rs.EOF And rs.BOF Then GoTo noEmail
    rs.MoveFirst
    Do While Not rs.EOF
    report_name = "report_name"
    DoCmd.SendObject acSendReport, report_name, acFormatPDF, rs!EmailAddress, , , eSub, eText, False
    DoCmd.SendObject , , , rs!EmailAddress, , , eSub, eText, False
    rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    Exit Sub
    noEmail:
    rs.Close
    Set rs = Nothing
    MsgBox "The EmailTable does not have any names to email. Enter names and email addresses in this table first."

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. If you want to send the report to only one department manager you need to select only that one in the Email Table (here with the name of the manager being held in the [RecipientName] column of the table:
    Code:
    strSQL = "Select * from EmailTable WHERE [RecipientName] = '<Name of the manager>';"
    2. If you want to send the same report with differerent data to each recipient strored in the EmailTable, you can change the data source query of the report at each iteration of the loop. Without knowing how the report is built and on what criteria the data it contains should change according to the recipient, it's not possible to be more precise here.
    Have a nice day!

  5. #5
    Join Date
    Mar 2011
    Posts
    42
    I need a code to get the e-mail for the first department and then print the report where report!department=table.department and after that goes to the next record and so on

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Once more: Without knowing how the report is built and on what criteria the data it contains should change according to the recipient, it's not possible to be more precise.

    Namely:
    - I don't know anything (almost) about your project.
    - I don't know how data is organized and structured.
    - I don't know how the report is built.
    - I don't know how you identify departments.
    - I don't know what report!department=table.department means nor what impact this has on the report as I don't know how this report is built, etc.

    Please provide some references and details on your project. See also: http://www.dbforums.com/microsoft-ac...ml#post4534485
    Have a nice day!

  7. #7
    Join Date
    Mar 2011
    Posts
    42
    apart of the project is about attendance and absence
    1- attendance data is stored in (attendance) table having the following main fields:
    1) date , 2) employee name , 3) attended "yes, no" , 4) department
    2- a report is generated grouped by department (i'm using a form from which i choose the department and then on the after_update event the next code will run :

    docmd.openreport("attendance",..... where department =combo_box

    meanwhile i have a table for departments which includes the fields : department, .... and e-mail fileds

    i need a code to seek in the departments table and pull the e-mail of the department and send the report by e-mail; but only the data pertaining to this department will be generated in the report

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Thank you for the explanations, this is clearer now.

    The simplest way to achieve your goal that comes to my mind would consist in creating a query derived from the query that is used as RecordSource for the report to have it filter its rows by department (instead of filtering on the report). Something like:
    Code:
    Function SendReports()
    
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Dim qdf As DAO.QueryDef
        Dim strSQL As String
        Dim eSub As String
        Dim eText As String
        Dim eMail As String
        Dim report_name As String
        
        
        Set dbs = CurrentDb
        '
        ' The table [Departments] contains the names of the departments
        ' (field: [Department]) associated with their e-mail addresses (field: [e-mail]).
        '
        strSQL = "SELECT Department, [e-mail] FROM Departments;"
        Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
        With rst
            Do Until .EOF
                '
                ' QueryReport is the name of the query that originally was the RecordSource of the report.
                '
                strSQL = "SELECT * FROM QueryReport WHERE Department = '" & !Department & "';"
                '
                ' Qry_ReportSource is the new query used as RecordSource for the report.
                ' Note: Qry_ReportSource must exist in the database prior to running this code.
                '
                Set qdf = dbs.QueryDefs("Qry_ReportSource")
                qdf.SQL = strSQL
                dbs.QueryDefs.Refresh
                qdf.Close
                '
                ' Provided that there are two controls named msgSubject and msgMessage in the current form.
                '
    '            eSub = Me.msgSubject.Value
    '            eText = Me.msgMessage.Value
                '
                ' report_name is the name of the report to be sent.
                '
                report_name = "report_name"
                eMail = ![e-mail]
                DoCmd.SendObject acSendReport, report_name, acFormatPDF, eMail, , , eSub, eText, False
                .MoveNext
            Loop
            .Close
        End With
        '
        ' Cleanup.
        '
        Set qdf = Nothing
        Set rst = Nothing
        Set dbs = Nothing
        
    End Function
    Have a nice day!

  9. #9
    Join Date
    Mar 2011
    Posts
    42
    Thanks alot I'll try it

  10. #10
    Join Date
    Mar 2011
    Posts
    42

    thanks

    My mail server is down;

    let me ask you : what if I want the output to be in a PDF file which will be stored with the department name , and to be saved on some folder on the hard disk

    You're great and doing great things

  11. #11
    Join Date
    Mar 2011
    Posts
    42
    Dear, sinndho
    I'ts working fine thanks, but with every department a message is coming from outlook giving the option (allow, deny, help) I have to click allow on every department sending.

    Anything to do to send the mail without this messages

    Thanks alot

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Have a nice day!

  13. #13
    Join Date
    Mar 2011
    Posts
    42
    Dear, Sinndho,
    I've done all instructions and much more, but still receiving the same warning,

    ====== so sorry =========

Posting Permissions

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