Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2004
    Location
    Ft Myers, FL
    Posts
    5

    Question Unanswered: Auto Email, Many Recipients

    I have an auto mail script that works great.

    I have an employee table that has an "Agent#" number for each sales rep. I also have a "Request For Daily Status Report" check box. I can get a query that will get the correct information and email address for those reps with the request check box.

    I have a query that asks for a "Agent # Parameter" and it is a in popup dialog. I can hard code a query for each rep but it grows by the day. I have tested this with hard coded queries and it works great. I need to have the query parameter changed on the fly to email a different report for each sales rep in VBA. I have created a recordset that gets the information for the email address and the Rep ID and I can not figure out how to get the query parameter to get the status report automaticly from VBA.

    Hope someone can point me in the right direction.

    ~Gary

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What do you mean by "different reports"?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jun 2004
    Location
    Ft Myers, FL
    Posts
    5
    Same report, same fields

    Different Data based on sales rep id. That has to go to a different person.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That's easy enough...

    What specific method are you using to open the report?

    You may be able to use report parameters, filter, dynamic where clause, build the query in the on open event for the report...

    all sorts of possibilities...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jun 2004
    Location
    Ft Myers, FL
    Posts
    5
    I could use different report filters to do this?

    How would I do that and email that data in .rtf and to a different email address and rep id

    ??????

  6. #6
    Join Date
    Jun 2004
    Location
    Ft Myers, FL
    Posts
    5
    Code:
    Dim CurTime As Date
    
    CurTime = TimeValue(Now)
    
    Me.txtCurrentTime = CurTime
    
    If CurTime = "8:30:00PM" Then
    
    'Region 1
    DoCmd.SendObject acSendReport, "rptReg1Status", "Rich Text Format", "JGabel@FirstMerchants.org", , , "Your Daily Status Report", "Your Status Report Is Attached.", 0
    
    'Region 3
    DoCmd.SendObject acSendReport, "rptReg3Status", "Rich Text Format", "GBennett@FirstMerchants.org", , , "Your Daily Status Report", "Your Status Report Is Attached.", 0
    
    'Region 4
    DoCmd.SendObject acSendReport, "rptReg4Status", "Rich Text Format", "RHiggins@FirstMerchants.org", , , "Your Daily Status Report", "Your Status Report Is Attached.", 0
    
    'Region 5
    DoCmd.SendObject acSendReport, "rptReg5Status", "Rich Text Format", "REverett@FirstMerchants.org", , , "Your Daily Status Report", "Your Status Report Is Attached.", 0
    
    End If
    This is what I have done for the main people.

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What does your db schema look like. It looks like it's not going to be very friendly. Ideally you could accomplish this using a WELL LAID OUT db schema. I you could create a recordset containing all of the agents receiving reports, then cycle through each record, creating and sending the report using the current agent# as a report filter.

    As it looks right now, this is no good.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Jun 2004
    Location
    Ft Myers, FL
    Posts
    5
    So I would have to make a recordset that would have each agent# and email address and filter the information based upon those things.

    How would I start the recordset?

Posting Permissions

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