Results 1 to 12 of 12
  1. #1
    MEMFBI is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Apr 2020
    Posts
    42

    Send report in body of email, limit report to recipient of email

    Hello


    I have a 2 tables, 1 table, "Email_Contracts" contains all open contracts one of the fields contains the email address of the salesperson responsible for the contract. 2nd table "Email_Contracts_Header" just contains the email address of salespeople who have open contracts.
    Currently I am able to generate emails containing a report of all open contracts "rpt_Email_Contracts". I read thru the record set of the "Email_Contracts_Header" table and send an email to those emails.
    What I need to do is limit the report being sent to to only include the open contracts associated the the email being sent to.


    Code:
        Dim strBody As String    Dim sHtml As String
       
       Dim rst As DAO.Recordset
       Set rst = CurrentDb.OpenRecordset("Select EMAIL FROM Email_Contracts_Header")
    
    
    Do While Not rst.EOF
       varTo = rst![EMAIL]
       rst.MoveNext
          
      
       stSubject = "Contracts waiting for your Approval"
       
          
    '____________________________________________________________________
    
    
    '   Output the report to HTML in the temp directory
    sFile = "C:\Users\memallazzo\Temp_Rpt\Open_Contract  " & Format(Date, "yyyymmdd") & ".html"
     DoCmd.OutputTo acOutputReport, "rpt_Email_Contracts", acFormatHTML, sFile
    
    
     
     
    'Read in the HTML File
    lFile = FreeFile
    
    
    Open sFile For Input As lFile
    sHtml = Input$(LOF(lFile) - 1, lFile)
     
    Close lFile
    
     '_____________________________________________________________________
    
    
     Loop
    Thank you

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,847
    One way would be to open the report filtered first (it can be hidden):

    http://www.baldyweb.com/wherecondition.htm

    so your code would look like:

    DoCmd.OpenReport...'open report in preview mode, filtered as desired
    DoCmd.OutputTo...'output it
    DoCmd.Close...'close the report
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    MEMFBI is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Apr 2020
    Posts
    42
    Thank you.
    I am following what you are saying. Currently I am reading thru the "Email_Header_Contracts" to grab the email address using Set rst = CurrentDb.OpenRecordset........
    How do I make sure that the email I am retrieving using OpenRecordset is the same email used as the criteria for the OpenReport?

    Thank you

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,847
    Instead of referring to a control on a form you'd refer to the recordset:

    "FieldName = '" & rst!Email & "'"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    MEMFBI is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Apr 2020
    Posts
    42
    Quote Originally Posted by pbaldy View Post
    Instead of referring to a control on a form you'd refer to the recordset:

    "FieldName = '" & rst!Email & "'"
    I thought of that, but I am receiving error msg: Object Required

    Code:
    DoCmd.OpenReport "rpt_Email_Contracts", acViewPreview, , "Email_Contracts!EMAIL = '" & rst!EMAIL & "'"

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,847
    Try

    DoCmd.OpenReport "rpt_Email_Contracts", acViewPreview, , "EMAIL = '" & rst!EMAIL & "'"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    MEMFBI is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Apr 2020
    Posts
    42
    Thanks, but still receive "Object Required"

    Code:
    DoCmd.OpenReport "rpt_Email_Contracts", acViewPreview, , "EMAIL = '" & rst!EMAIL & "'"

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,847
    What's the full sub look like now? Or can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    MEMFBI is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Apr 2020
    Posts
    42
    The sub that is shooting the error is "SendExceptionEmail" it is called from within sub "cmd_send_report"

    Thanks

    Code:
    Sub SendExceptionEmail(vDETAIL_01 As String, vDetail_02 As String) 
    'On Error GoTo eh
    'define as an object  late binding for differnet version of access
    '
    
    
       Dim olApp As New Outlook.Application
       Set olApp = New Outlook.Application
       Dim olFolder As Outlook.MAPIFolder
       Dim olMail As Outlook.MailItem
     
          
        Dim olNameSpace As Outlook.Namespace
        
        Dim sFile As String
        Dim lFile As Long
        Dim sHtml As String
        Dim olNewMail As Object
        Set olNameSpace = olApp.GetNamespace("MAPI")
        Set olFolder = olNameSpace.GetDefaultFolder(olFolderOutbox)
        Set olNewMail = olApp.CreateItem(olMailItem)
      
      
     '   Output the report to HTML in the temp directory
    sFile = "C:\Users\smith\Temp_Rpt\Open_Contract  " & Format(Date, "yyyymmdd") & ".html"
    
    
    
    
    ' DoCmd.OpenReport "rpt_Email_Contracts", acViewPreview, , "Email_Contracts!EMAIL = '" & rst!EMAIL & "'"
     
     DoCmd.OpenReport "rpt_Email_Contracts", acViewPreview, , "EMAIL = '" & rst!EMAIL & "'"
     DoCmd.OutputTo acOutputReport, "rpt_Email_Contracts", acFormatHTML, sFile
     DoCmd.Close
      
     
    'Read in the HTML File
    lFile = FreeFile
    
    
    Open sFile For Input As lFile
    sHtml = Input$(LOF(lFile) - 1, lFile)
     
    Close lFile
    
    
    'Put the file contents in the email body
    
    
    
    
    olNewMail.To = vDETAIL_01
    olNewMail.Subject = vDetail_02
    olNewMail.HTMLBody = sHtml
    olNewMail.Display
     
    
    
             With olNewMail
               .To = vDETAIL_01
               .Subject = vDetail_02
                 olNewMail.HTMLBody = sHtml
             
              .Send
            End With
       
             
    ex:
         Set olApp = Nothing
         Set olNewMail = Nothing
         Set olFolder = Nothing
         Set olNameSpace = Nothing
         Exit Sub
    eh:
       
        Resume ex
    End Sub
     
    Private Sub cmd_send_report_Click()
    
    
    On Error GoTo Err_Email_Report_Click
    
    
        Dim stWhere As String       '-- Criteria for DLookup
       
        Dim varTo As Variant        '-- Address for SendObject
        Dim stText As String        '-- E-mail text
        Dim stSubject As String     '-- Subject line of e-mail
        Dim varEstimator As Variant
           
        Dim strBody As String
        Dim sHtml As String
       
       Dim rst As DAO.Recordset
       Set rst = CurrentDb.OpenRecordset("Select EMAIL FROM Email_Contracts_Header")
    
    
    Do While Not rst.EOF
       varTo = rst![EMAIL]
       rst.MoveNext
       
    
    
       stSubject = "Contracts waiting for your Approval"
       
          
    '____________________________________________________________________
    
    
       SendExceptionEmail (varTo), (stSubject)
     '_____________________________________________________________________
    
    
     Loop
     
    ' DoCmd.Close
    
    
        On Error GoTo Err_Execute
    '    CurrentDb.Execute strSQL, dbFailOnError
        On Error GoTo 0
        
        Exit Sub
    
    
    Err_Execute:
     
        Resume Next
    
    
    Exit_EMail_Report_Click:
        Exit Sub
    
    
    Err_Email_Report_Click:
        MsgBox Err.Description
        Resume Exit_EMail_Report_Click
    End Sub

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,847
    Sure, the recordset doesn't exist in this sub. Looks like vDETAIL_01 contains the email address? If so, use that instead:

    DoCmd.OpenReport "rpt_Email_Contracts", acViewPreview, , "EMAIL = '" & vDETAIL_01 & "'"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    MEMFBI is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Apr 2020
    Posts
    42
    That did it!

    Thank you!

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,847
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 2
    Last Post: 09-03-2015, 11:33 AM
  2. Replies: 3
    Last Post: 05-18-2015, 11:24 AM
  3. Send Report as body of email
    By chris.williams in forum Reports
    Replies: 3
    Last Post: 09-15-2012, 09:43 AM
  4. Email report as body of email (RTF)
    By TheDeceived in forum Programming
    Replies: 4
    Last Post: 07-23-2012, 06:39 AM
  5. Replies: 4
    Last Post: 04-13-2011, 10:11 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums