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 scriptFunction exporthtml(str_Sender As String, str_DataMsg As String)Dim strlin As String, strHTML As StringDim objOutlook As Outlook.ApplicationDim objOutlookMsg As Outlook.MailItemDim objOutlookRecip As Outlook.RecipientDim varX As VariantDim 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 WithEnd Function**code tags made it more difficult to read

[pbaldy]

Your code is very difficult to read. Here's one way to filter the report in situations like this:

Emailing a different report to each recipient

[axkoam]

Ya sorry about the code, reposting it at the end of this post.

I've referenced that site, it was really helpful on some things with emailing in vba but I was having a hard time applying it to what I'm trying to do now.




'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

[pbaldy]

You would include the code in the link in your report. Inside your loop you either set a form control or global variable to the appropriate value so the report code can use it to filter itself. The OutputTo line would be inside the loop so it could take advantage of that. Typically you'd also customize the saved file's name so it wouldn't overwrite itself.

[axkoam]

I'm not sure what you mean about including the code "in the link in your report". Could you explain what this means please?

I'm not sure the granite resource really works for me because I need to execute some SQL statement to filter the report or query results so that they can be grouped by an attribute that is not within the table (E-Mail) but a separate table.

I have joined the query that has Mfg_Cd's to the table that has E-Mail's and I want to filter each group of Mfg_Cd (0 or more records) to each associated E-Mail.

[pbaldy]

I meant that the link demonstrates code that goes in the open event of the report, essentially allowing it to filter itself when it opens. That makes the filtering work with OutputTo.

As long as the value to filter on can be passed from the code to the report code, the method should work. If yours is not that situation, I apologize for wasting your time.

[axkoam]

O ok, that is my situation. Thanks for explaining.