Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004

    Unanswered: One last question for the day - I promise!!

    With lots of help from friends on this forum, I can now select a store and email the report for that individual store. last question is this...

    Can I use multi select to choose several stores, and then somehow use Cases to run through and email the proper report to each store?

    The code I use for one store is this:

    Private Sub Command10_Click()
    On Error GoTo Err_Command10_Click
    'creates strRecipient as a variable
    Dim strRecipient
    'assigns an email address to strRecipient depending on table information
    strRecipient = DLookup("EMailAddress", "Store", "StoreID=[SS]")
    'creates strBCCRecipient as a variable
    Dim strBCCRecipient
    'assigns an email address to strRecipient depending on table information
    strBCCRecipient = DLookup("Email", "OurInfo", "SetupID=1")

    'creates an email in outlook using the email address from the table and the report filtered by the store set last arg to True for editing before sending
    'or False to automatically send
    DoCmd.SendObject acSendReport, "DlbyStore", acFormatRTF, strRecipient, , strBCCRecipient, "Debtor Listing Report from the Law Office", "Attached please find your Debtor Listing Report for this month.", True

    Exit Sub

    MsgBox Err.Description
    Resume Exit_Command10_Click

    End Sub

    Can I now change it to let me print each one separately?

    For example, in lay terms, could I set it up to:

    Is store1 selected? Yes - email report for store1 to the store1 email address.
    Is store2 selected? Yes - email report for store2 to the store2 email address.etc...

    Thanks to ton!!!


  2. #2
    Join Date
    Jun 2004
    suggest you 'modulize' (my own word ) ur functions and keep each job to each function..

    GetRecipient(StoreID) - calls SendReport once recipients are determined using storeID
    SendReport(reportname, recipients, bccrecipients) - send the required report to the respective recipients..

    back to your questions:.. you can iteratively call GetRecipient by passing different StoreIDs and it will iteratively performs the sending..

    example (assuming lstbox holds list of store ids):
    for i=0 to lstbox.itemsSelected.count
    next i

  3. #3
    Join Date
    Apr 2004
    That makes a lot of sense! To do this, do I set up the VB code not attached to any button or anything, and then just reference it? Do I need to call it something special? (Does it start with Private Sub, or do I put it in the 'modules' section down below?)

    Thanks for all your help!!!


Posting Permissions

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