Filter Access Report to Email, Relevant Records Only

[axkoam]

Microsoft Access 2007.

Background: I have a query that received work orders from out client system and runs them through access. Once in Access, an employee goes through the query output and organizes the records (mentally) by product_code. There can be be 1 or more records of a certain product_code and there are 129 total different product_codes. Say, a typical time the employee runs this query they might see 25 different records consisting of 6 different product_codes.

Each product_code designates who (an email address) should be sent the work orders to approve. The employee currently copy/paste the relevant records in an Outlook 2010 email body and uses an external excel sheet with the email addresses to see who it should be sent to. I'm automating this whole process.

I've gotten an email to generate in vba, and to send an 1 email to each person who has one of their product_codes in the query output. My problem is that I'm having trouble figuring out how to filter the emails so that instead of seeing all 25 records (from above example) they only see the records with their product_code.

Another thing to mention is that as of now, I have a script that converts the report to HTML and sticks it in the body of the email. So I'm not sure how to edit that script to allow to filter the report or if that's even where I need to do the filtering (another procedure maybe?)

Any advice would be greatly appreciated; ahh that feeling when a project's winding down ;-)

This is my html conversion script:

'product_code = Mfg_Cd in this script
 
Function exporthtml(str_Sender As String, str_DataMsg As String)
 
Dim strlin As String, strHTML As String
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim varX As Variant
Dim RS As Recordset
 
  Set RS = Me.Recordset
 
  Set objOutlook = Outlook.Application
  Set objOutlookMsg = Outlook.Application.CreateItem(olMailItem)
 
  DoCmd.OutputTo acOutputReport, "Report-q_Workflow", acFormatHTML, "filepath"
 
  Open "filepath" For Input As 1
  Do While Not EOF(1)
  Input #1, strline
    strHTML = strHTML & strline
  Loop
  Close 1
  If Left(objOutlook.Version, 2) = "10" Then
    objOutlookMsg.BodyFormat = olFormatHTML
  End If
 'strHTML is how I stick the html conversion in the body if anyone is wondering 
 objOutlookMsg.HTMLBody = "Hi Team,<br>Please let me know if the following orders are okay to approve." & vbCrLf & vbCrLf & strHTML & "<br>Thank You" & vbCrLf & vbCrLf 
 
      With objOutlookMsg
      Do
 
      Set objOutlookRecip = .Recipients.Add(str_Sender)
      objOutlookRecip.Type = olTo
      Loop Until RS.EOF
      Close
      Set objOutlookRecip = .Recipients.Add("")
      objOutlookRecip.Type = olCC
      objExport = exporthtml
      .Subject = "International Authorization"
      .Importance = olImportanceHigh
      For Each objOutlookRecip In .Recipients
         objOutlookRecip.Resolve
      If Not objOutlookRecip.Resolve Then
         objOutlookMsg.Display
      End If
      Next
      .Send
      End With
 
End Function
 

[twinnyfo]

axkoam,

I can't go through and write all the code for you, but I have a very similar function in one of my databases. I will outline how I would do it, and then you can play with the code to find out how it will work for you.

1. First, I would establish a global variable like current_product_code, which will hold the product_code for which you want to send the report.

2. When you want to send these reports, you need to find out which product_codes will be contained in the current set of reports. Build an aggregate query that lists all the product_codesfor the current list of reports, i.e. even if a product_code is found twice, it should only show up on the query once. In your code, create a recordset based on that query--this will be the outer loop of your e-mail code. You will set your global variable current_product_code to the first item in this recordset.

3. You should re-configure your report to have as its RecordSource a query that lists all the orders limited by the current_product_code. When you do this, because of step 2 above your report will have only those records that apply, instead of all available records.

4. Now that you know the current_product_code, you also know who should receive the work orders. Establish the E-mail body text, using variables, concatenation and other methods to build an e-mail note to the approval authority. Use the DoCmd.SendObject method, sending your newly configured report as an attachment (there are various options), to the e-mail address designated.

5. Now, loop back to the next product_code in your recordset, setting your global variable again, and repeat.

Again, this is just the basic outline, but I'd be glad to work through this as you build your code. It took me a while to perfect this process, but works like a charm with multiple types of reports that I generate.

Let me know if you need more help!