Filter a Report Based on an Attribute a to be Emailed VBA

[axkoam]

I'm using Windows 7, Access 2007, Outlook 2010

Background:
My situation is that I have a report that I need to email out to different people using vba. I've written the script to generate the email, attach the report (actually in the body of the email) and send it to the correct people.

Issue:
My issue is that I want to send only the relevant records from the report to each person. I've put the relevant person's email into the report (of a query) to make it easier. I want to email all the records with one email address to that email address and so on.


Example:
This would mean, for example, there are 6 records with axkoam@company. com in the email field of the report and 3 records with notaxkoam@compa ny.com. I want the 6 records with axkoam@company. com to be emailed to axkoam@company. com and the 3 records with notaxkoam@compa ny.com to be emailed to notaxkoam@compa ny.com.


Is there any help someone can give me?

If you need more clarification and/or my code just let me know. Thanks!

[Mihail]

Base the filter on a public variable.
Set the value for this variable before running the code for send mail.

[axkoam]

Is the filter a query? Or does Access have something special designated for filtering? I'm a little confused here.

[Mihail]

Here is a fine video to understand what I mean.

Also, in the attachment, you can see an example.

Feel free to ask more if you need.
Cheers !

[axkoam]

Thanks. This is the code I wrote for anyone following along.

Public Function parse_WorkflowNew()
    Dim rs As ADODB.Recordset, str_getSend As String
    Dim rs_Missing As ADODB.Recordset
    
    Set rs = New ADODB.Recordset
    Set rs_Missing = New ADODB.Recordset
    
    rs_Missing.Open "Select Mfg_Cd from q_AuthToRoute Where [E-Mail] is null Group by Mfg_Cd", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    
    rs.Open "Select [E-Mail] from q_AuthToRoute Where [E-Mail] is not null Group by [E-Mail]", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    
    If Not rs.EOF And Not rs.BOF Then
        rs.MoveFirst
        Do
                Dim rs_Data As ADODB.Recordset
                Set rs_Data = New ADODB.Recordset
                rs_Data.Open "Select * From q_AuthToRoute Where [E-Mail] = '" & rs.Fields("E-Mail") & "'", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
                If Not rs_Data.BOF And Not rs_Data.EOF Then
                    rs_Data.MoveFirst
                    Dim str_Table As String
                End If

                exporthtml rs.Fields("E-Mail"), rs_Data
            rs.MoveNext
        Loop Until rs.EOF
    End If
  
End Functio

There are some other routines taking place btw, but this is the filtering part.