473,320 Members | 1,961 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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 !

4 2160
Mihail
759 512MB
Base the filter on a public variable.
Set the value for this variable before running the code for send mail.
Jun 27 '12 #2
axkoam
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
Mihail
759 512MB
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, 115 views)
Jun 28 '12 #4
axkoam
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

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

Similar topics

3
by: Richard | last post by:
Hi, I have a form based on a table. When I filter the form I want to run a report based on the same table with the same filter as the form. No problem until I want to filter a combo box where...
2
by: Rosy | last post by:
I am attempting to use the following code to print a report based on the current record in the form. Users bring up the record with a parameter box and then can make changes to the sub-form on the...
5
by: favor08 | last post by:
have a mainform called PendingsMain and a subform called PendingsSub. You can filter the subform by different filters and this works fine. i want to create a report that will print out the...
5
by: jonosborne | last post by:
Hi, i have managed to filter a report based on selections made in a list box but am totally confused with a message box that appears everytime i run my report. Let me explain (i apologise for...
94
by: mlcampeau | last post by:
I have a report (JobVacanciesOnly) that has a subreport (JobVacanciesOnlySR) that are based on two separate queries. MY - JobVacancyJobs SELECT Job.Code, Job.Title, Job.Grade, Grade.Minimum,...
6
by: pouj | last post by:
what i want to do is basically have form where my selection in the combox make the diffrence with what the form reports. this is what the underlying informaion is.... software is access 2007 i...
12
by: HowHow | last post by:
I need to create a few reports using one query and I wish to do the program filter from the buttons in a form. I had created a query named q_ClientsHvServices and a report based on that query named...
11
by: billa856 | last post by:
Hi, I have project in MS Access.In that I have one form in which there is one combobox .I want to know when I select an item from that combobox and click on submit button then it should open a...
7
by: Xaysana12345 | last post by:
Hello there, I have created number of Buttons on unbound form to filter the report based on a pivot query called filters. Report named AnnualReport-ProjectFilter. What I would like to do is...
0
by: munkee | last post by:
Hi all, This would normally be quite simple however for some reason I can not getting working. My code to open my report filtered to a primary key is as follows: Case "Full record"...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.