By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,830 Members | 682 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,830 IT Pros & Developers. It's quick & easy.

Filter Access Report to Email, Relevant Records Only

P: 4
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:

Expand|Select|Wrap|Line Numbers
  1. 'product_code = Mfg_Cd in this script
  3. Function exporthtml(str_Sender As String, str_DataMsg As String)
  5. Dim strlin As String, strHTML As String
  6. Dim objOutlook As Outlook.Application
  7. Dim objOutlookMsg As Outlook.MailItem
  8. Dim objOutlookRecip As Outlook.Recipient
  9. Dim varX As Variant
  10. Dim RS As Recordset
  12.   Set RS = Me.Recordset
  14.   Set objOutlook = Outlook.Application
  15.   Set objOutlookMsg = Outlook.Application.CreateItem(olMailItem)
  17.   DoCmd.OutputTo acOutputReport, "Report-q_Workflow", acFormatHTML, "filepath"
  19.   Open "filepath" For Input As 1
  20.   Do While Not EOF(1)
  21.   Input #1, strline
  22.     strHTML = strHTML & strline
  23.   Loop
  24.   Close 1
  25.   If Left(objOutlook.Version, 2) = "10" Then
  26.     objOutlookMsg.BodyFormat = olFormatHTML
  27.   End If
  28.  'strHTML is how I stick the html conversion in the body if anyone is wondering 
  29.  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 
  31.       With objOutlookMsg
  32.       Do
  34.       Set objOutlookRecip = .Recipients.Add(str_Sender)
  35.       objOutlookRecip.Type = olTo
  36.       Loop Until RS.EOF
  37.       Close
  38.       Set objOutlookRecip = .Recipients.Add("")
  39.       objOutlookRecip.Type = olCC
  40.       objExport = exporthtml
  41.       .Subject = "International Authorization"
  42.       .Importance = olImportanceHigh
  43.       For Each objOutlookRecip In .Recipients
  44.          objOutlookRecip.Resolve
  45.       If Not objOutlookRecip.Resolve Then
  46.          objOutlookMsg.Display
  47.       End If
  48.       Next
  49.       .Send
  50.       End With
  52. End Function
Jun 24 '12 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 2.5K+
P: 3,205

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!
Jul 25 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.