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

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

P: 4
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@company.com. I want the 6 records with axkoam@company.com to be emailed to axkoam@company.com and the 3 records with notaxkoam@company.com to be emailed to notaxkoam@company.com.


Is there any help someone can give me?

If you need more clarification and/or my code just let me know. Thanks!
Jun 26 '12 #1

✓ answered by 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 !

Share this Question
Share on Google+
4 Replies


100+
P: 759
Base the filter on a public variable.
Set the value for this variable before running the code for send mail.
Jun 27 '12 #2

P: 4
Is the filter a query? Or does Access have something special designated for filtering? I'm a little confused here.
Jun 27 '12 #3

100+
P: 759
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 !
Attached Files
File Type: zip Axcoam.zip (73.7 KB, 87 views)
Jun 28 '12 #4

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

Expand|Select|Wrap|Line Numbers
  1. Public Function parse_WorkflowNew()
  2.     Dim rs As ADODB.Recordset, str_getSend As String
  3.     Dim rs_Missing As ADODB.Recordset
  4.  
  5.     Set rs = New ADODB.Recordset
  6.     Set rs_Missing = New ADODB.Recordset
  7.  
  8.     rs_Missing.Open "Select Mfg_Cd from q_AuthToRoute Where [E-Mail] is null Group by Mfg_Cd", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
  9.  
  10.     rs.Open "Select [E-Mail] from q_AuthToRoute Where [E-Mail] is not null Group by [E-Mail]", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
  11.  
  12.     If Not rs.EOF And Not rs.BOF Then
  13.         rs.MoveFirst
  14.         Do
  15.                 Dim rs_Data As ADODB.Recordset
  16.                 Set rs_Data = New ADODB.Recordset
  17.                 rs_Data.Open "Select * From q_AuthToRoute Where [E-Mail] = '" & rs.Fields("E-Mail") & "'", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
  18.                 If Not rs_Data.BOF And Not rs_Data.EOF Then
  19.                     rs_Data.MoveFirst
  20.                     Dim str_Table As String
  21.                 End If
  22.  
  23.                 exporthtml rs.Fields("E-Mail"), rs_Data
  24.             rs.MoveNext
  25.         Loop Until rs.EOF
  26.     End If
  27.  
  28. End Functio
There are some other routines taking place btw, but this is the filtering part.
Jul 3 '12 #5

Post your reply

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