Results 1 to 4 of 4
  1. #1
    vnwallace is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    2

    Email Individual Reports to their corresponding email addresses

    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. Any help would be GREATLY appreciated!! Thanks!

    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

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,676
    Common topic, search forum.

    Start with:

    https://www.accessforums.net/access/...ils-36702.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    vnwallace is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    2
    Hi,

    I am having troubles fitting that in to my current code.. I am not sure where to put the certain information requred to loop through the record sets and gather the email addresses from the reports to send to.. any help would be greatly appreciated!! Thanks!!

    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

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,676
    Code logic to send individual emails:
    1. open recordset of email addresses
    2. get address from record
    3. open filtered report
    4. send report
    5. save PDF
    6. close report
    7. move to next record
    8. repeat from 2

    Something like:

    Dim rs As DAO. Recordset
    stCaption = "Collateral Demand" & Format(Now(), " mm-dd-yyyy")
    myPath = "J:\Investments\Trade Operations\Derivative Transactions\Collateral\Access Database\"
    stEmailMessage = "Please see the attached collateral call for today."
    stSubject = "Collateral Demand"
    stReport = "DECO Import File"
    Set rs = CurrentDb.OpenRecordset("SELECT email FROM tablename;")
    DoCmd.OpenReport "DECO Import File", acViewPreview
    While Not rs.EOF
    DoCmd.SendObject acSendReport, , acFormatPDF, rs!email, , , stSubject, stEmailMessage, True, ""
    DoCmd.OutputTo acOutputReport, , acFormatPDF, myPath & stCaption & ".pdf", False, , , acExportQualityPrint
    rs.MoveNext
    Wend
    DoCmd.Close acReport, stReport, acSaveNo
    rs.close
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Email Individual Access Reports
    By Nae in forum Reports
    Replies: 3
    Last Post: 08-26-2012, 04:39 PM
  2. Finding email addresses using VBA
    By P.Malius in forum Programming
    Replies: 1
    Last Post: 07-04-2012, 07:17 AM
  3. Replies: 2
    Last Post: 07-29-2011, 12:33 PM
  4. send email to email addresses in database?
    By cnstarz in forum Access
    Replies: 5
    Last Post: 03-02-2011, 09:46 PM
  5. Combining two Email Addresses
    By Frodo in forum Access
    Replies: 0
    Last Post: 09-16-2007, 07:07 AM

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
  •  
Tech Forums: Microsoft Office Forums