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

Send email with report attachment using where statement

reginaldmerritt
100+
P: 201
Hi,

I have two report layouts that are used to run 12 different reports using where statement.

for example
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport "SomeReport", _
  2.                  acViewPreview, _
  3.                  , _
  4.                  "[Something] = " & Something
I use the following code to send Report as a PDF attachment in an email. ReportName, EmailAddress, EmailSubject and EmailBody are all string variables declared and set before the following code is run

Expand|Select|Wrap|Line Numbers
  1. 'send email
  2. DoCmd.SendObject _
  3. acSendReport, _
  4. ReportName, _
  5. acFormatPDF, _
  6. EmailAddress, _
  7. , _
  8. , _
  9. EmailSubject, _
  10. EmailBody, _
How do I use a where statement with the .SendObject procedure? I can't find any details anywhere so perhaps this is not possible with .SendObject. If that is the case is there a different method I can use?

Many Thanks.
Sep 21 '10 #1

✓ answered by reginaldmerritt

To set an open reports filter you must use the following:

Reports.ReportName.Filter = "[FieldName] = 2"
Reports.ReportName.FilterOnLoad = True

For changing a report that is not open, i.e. a form that you want to add as an attachment to an email using .SendObject you must first open the report in design mode to change the reports properties, then close the report and add it to the .SendObject method.

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport "MYREPORT", acViewDesign
  2. Reports!MYREPORT.Filter = "[FIELDNAME] = 3"
  3. Reports!MYREPORT.FilterOnLoad = True
  4. DoCmd.Close acReport, "MYREPORT"
  5. EmailSubject = "Subject Header"
  6. EmailBody = "Main Body Text"
  7. ReportName = "MYREPORT"
  8.  
Expand|Select|Wrap|Line Numbers
  1. 'RunEmail
  2. DoCmd.SendObject _ 
  3. acSendReport, _ 
  4. ReportName, _ 
  5. acFormatPDF, _ 
  6. EmailAddress, _ 
  7. , _ 
  8. , _ 
  9. EmailSubject, _ 
  10. EmailBody, _ 
  11. False 
  12.  

Share this Question
Share on Google+
8 Replies


Expert 100+
P: 1,221
Not sure I understand, but it seems to me you want to put this docmd.sendobject within an if/then or maybe a Select Case statement, something like that.

Jim
Sep 21 '10 #2

reginaldmerritt
100+
P: 201
Sorry Jim.

I open reports using where statements. It seems that when you can attach reports to .SendObject, you can't use where statements.

I don't see how an IF Statement will help here?
Sep 21 '10 #3

reginaldmerritt
100+
P: 201
I managed to create a work around.

I was going to use a RecordSet to get the details for a where statement I need to use but as you can't use where statements with .SendObject I went the long way round.

I created a Form based on the Query that holds that data I need. I then modified the Reports Query to look at the Form. The form cycles through records and sends emails.

Problem with this solution is that it requires separate Queries and Reports for each place where I would have used a Where statement. I have 12+ report options which would mean lots of duplications.

This is the code I'm using at the moment:

Expand|Select|Wrap|Line Numbers
  1. Private Sub SendAllEmails()
  2.  
  3. On Error Resume Next
  4.  
  5. EmailSubject = "Your Learners NEAR END and OVER END as of " & Date
  6. EmailBody = "Attached Report run from 'Learner Managment'" & vbNewLine & "Your Learners NEAR END and OVER END as of " & Date
  7. ReportName = "RPAssessorsLearners<3WksOrOverEndDate" ' Critria on query used includes [Forms].[FRMSendAssessorEmail].[AssessorID]
  8.  
  9. For i = 1 To DCount("*", "QYAssessorsDISTINCT")
  10.  
  11.     If Not IsNull(Me.Email) Then
  12.  
  13.         EmailAddress = Me.Email
  14.  
  15.         'RunEmailAssessors 'Somehow using where [AssessorID] = rsAssessorsDISTINCT(AssessorID)
  16.         DoCmd.SendObject _
  17.         acSendReport, _
  18.         ReportName, _
  19.         acFormatPDF, _
  20.         EmailAddress, _
  21.         , _
  22.         , _
  23.         EmailSubject, _
  24.         EmailBody, _
  25.         False
  26.  
  27.     End If
  28.  
  29.     If i <> DCount("*", "QYAssessorsDISTINCT") Then
  30.         DoCmd.GoToRecord , , acNext
  31.     End If
  32.  
  33. Next
  34.  
  35. End Sub
  36.  
  37.  
  38. Private Sub Form_Load()
  39. SendAllEmails
  40. End Sub
  41.  
I'll be very interested in any ideas how to make this simpler or if there is a different method for sending emails that isn't stopped by the limitations of .SendObject.

Thanks.
Sep 21 '10 #4

reginaldmerritt
100+
P: 201
This workaround has caused a new problem. Any code called n on Form_Load does not run DoCmd.GotoRecord, , acNext.

Why is that?

I could put a button on the form to run the code required which will in turn will run DoCmd.GotoRecord, , acNext.
Is there a way of getting around this so DoCmd.GotoRecord, , acNext will run on Form_Load?
Sep 21 '10 #5

Expert 100+
P: 1,221
Have you trapped the error to see what error you are getting? What actually happens?

You might try putting
DoCmd.GoToRecord , , acFirst
prior to your loop

Jim
Sep 22 '10 #6

reginaldmerritt
100+
P: 201
Thanks Jim.

i actually ended creating a form that looks like a msgbox and uing the vbyes button to run the code.

I'm thinkg that a way around not being able to use where statment with .SendObject could be by setting the reports filter property. Can this be done to a report that is not yet open?

Actually i can't even get this to work on reports that are open i've tried using

Reports.ReportName.Filter = "[FieldName] = 2"
ReportName.Filter = "[FieldName] = 2"

is this possible?

Many Thanks.
Sep 23 '10 #7

reginaldmerritt
100+
P: 201
To set an open reports filter you must use the following:

Reports.ReportName.Filter = "[FieldName] = 2"
Reports.ReportName.FilterOnLoad = True

For changing a report that is not open, i.e. a form that you want to add as an attachment to an email using .SendObject you must first open the report in design mode to change the reports properties, then close the report and add it to the .SendObject method.

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport "MYREPORT", acViewDesign
  2. Reports!MYREPORT.Filter = "[FIELDNAME] = 3"
  3. Reports!MYREPORT.FilterOnLoad = True
  4. DoCmd.Close acReport, "MYREPORT"
  5. EmailSubject = "Subject Header"
  6. EmailBody = "Main Body Text"
  7. ReportName = "MYREPORT"
  8.  
Expand|Select|Wrap|Line Numbers
  1. 'RunEmail
  2. DoCmd.SendObject _ 
  3. acSendReport, _ 
  4. ReportName, _ 
  5. acFormatPDF, _ 
  6. EmailAddress, _ 
  7. , _ 
  8. , _ 
  9. EmailSubject, _ 
  10. EmailBody, _ 
  11. False 
  12.  
Sep 23 '10 #8

reginaldmerritt
100+
P: 201
Your also need to set warninigs to be off so the program doesn't ask the user to save the report after editing.

Expand|Select|Wrap|Line Numbers
  1.  
  2. DoCmd.SetWarnings False
  3.  
  4. DoCmd.OpenReport "MYREPORT", acViewDesign 
  5. Reports!MYREPORT.Filter = "[FIELDNAME] = 3" 
  6. Reports!MYREPORT.FilterOnLoad = True 
  7. DoCmd.Close acReport, "MYREPORT" 
  8.  
  9. DoCmd.SetWarnings True
  10.  
  11. EmailSubject = "Subject Header" 
  12. EmailBody = "Main Body Text" 
  13. ReportName = "MYREPORT" 
.Filter obviously has restrictions. For deeper manipulation you might consider using an SQL statement and chaning the reports RecordSource.
Sep 23 '10 #9

Post your reply

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