Page 1 of 5 123 ... LastLast
Results 1 to 10 of 42

Thread: Split In To Individual MS-Access Reports And Email To Corresponding Address

  1. #1
    Junior Member
    Join Date
    Aug 2013
    Posts
    23
    Rep Power
    0

    Split In To Individual MS-Access Reports And Email To Corresponding Address

    Hi,

    I have the below code that will run my query, print my reports, save them in bulk as a single PDF, and send them in one email. I need help finding the right code to separate the reports so that each report will email individually to their corresponding email address in a table I have created (the reports gather data from this table as well). My code currently will generate and email and I have to click the send button--this is what I want. I want to be able to review the email before I send it.

    Can someone help me with the correct code I need in order to make this happen and also where to put the code in my current code?

    Any help would be GREATLY appreciated!! Thanks!

    Code:
    Private Sub Command11_Click()
    
    DoCmd.OpenQuery "Report Query"
    DoCmd.Close acQuery, "Report Query"
    
    DoCmd.OpenReport "DECO Import File", PrintOut
    'This will print the reports
    
    Dim stReport As String
    Dim stWhere As String
    Dim stSubject As String
    Dim stEmailMessage As String
    Dim stCaption As String
    Dim myPath As String
    'This sets the strings for each part of the email
    
    stEmailMessage = "Please see the attached collateral call for today."
    stSubject = "Collateral Demand"
    stReport = "DECO Import File"
    
    stCaption = "Collateral Demand" & Format(Now(), " mm-dd-yyyy")
    myPath = "J:\Investments\Trade Operations\Derivative Transactions\Collateral\Access Database\"
    
    DoCmd.SendObject acSendReport, stReport, acFormatPDF, , , , stSubject, stEmailMessage, True, ""
    DoCmd.OutputTo acOutputReport, stReport, acFormatPDF, myPath & stCaption & ".pdf", False, , , acExportQualityPrint
    
    End Sub
    Last edited by Excel Fox; 08-16-2013 at 08:08 PM. Reason: Code Tags

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    vmwallace, the report query seems to be fetching the entire report. To split to individual reports, you probably need to break down the query based on whatever criteria your reports have to be split. So it would be best if you can save a sample of your database on a file-share site, and post the link here. One of the developers can have a look.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    And please ensure you wrap your code with code tags. The instructions are clearly mentioned in the header area of the post window, and also when you start a new thread. Please do not ignore them.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Junior Member
    Join Date
    Aug 2013
    Posts
    23
    Rep Power
    0
    Thank you! I will definitely do that next time! What share site should I use? I have never shared a database this way so any help would be greatly appreciated. Thanks again!

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    One of these should be free. 4shared should be fine. You could also try Top 15 Most Popular File Sharing Websites

    But ensure that it doesn't require an account to download files.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  6. #6
    Junior Member
    Join Date
    Aug 2013
    Posts
    23
    Rep Power
    0
    Thank you, I used 4Share like directed and below is the link. Please let me know if this does not work, if it doesn't work I will use another one.

    Collateral Database Test - Download - 4shared - Valerie Wallace

  7. #7
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Thanks, but you'll need to provide the linked excel file also.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  8. #8
    Junior Member
    Join Date
    Aug 2013
    Posts
    23
    Rep Power
    0
    Apologies, I have attached the link to the file. Thanks!

    DECO Import File - Download - 4shared - Valerie Wallace

  9. #9
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    I ran your query, and the report is only showing data for one record, ie, RGA Re DV COL

    Does the report have any filter somewhere?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  10. #10
    Junior Member
    Join Date
    Aug 2013
    Posts
    23
    Rep Power
    0
    You are correct in that there is only one record. That day for our demands there was only one record that had the column for "collateral to receive" > 0. I am attaching another link for the excel file that will have more than one record. Thank you so much for your help!!

    DECO Import File - Download - 4shared - Valerie Wallace

Similar Threads

  1. VBA To Extract Email Address From Text
    By dunndealpr in forum Excel Help
    Replies: 43
    Last Post: 06-05-2019, 03:56 PM
  2. Converting text to an email address hyperlink
    By Aerodynamix in forum Excel Help
    Replies: 2
    Last Post: 08-09-2013, 09:56 PM
  3. Replies: 2
    Last Post: 05-23-2013, 08:08 AM
  4. Replies: 6
    Last Post: 05-20-2013, 10:06 PM

Tags for this Thread

Posting Permissions

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