Send email with report attachment using where statement

[reginaldmerritt]

Hi,

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

for example

DoCmd.OpenReport "SomeReport", _
                 acViewPreview, _
                 , _
                 "[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

'send email
DoCmd.SendObject _
acSendReport, _
ReportName, _
acFormatPDF, _
EmailAddress, _
, _
, _
EmailSubject, _
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.

[jimatqsi]

Not sure I understand, but it seems to me you want to put this docmd.sendobjec t within an if/then or maybe a Select Case statement, something like that.

Jim

[reginaldmerritt]

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?

[reginaldmerritt]

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:

Private Sub SendAllEmails()

On Error Resume Next

EmailSubject = "Your Learners NEAR END and OVER END as of " & Date
EmailBody = "Attached Report run from 'Learner Managment'" & vbNewLine & "Your Learners NEAR END and OVER END as of " & Date
ReportName = "RPAssessorsLearners<3WksOrOverEndDate" ' Critria on query used includes [Forms].[FRMSendAssessorEmail].[AssessorID]

For i = 1 To DCount("*", "QYAssessorsDISTINCT")

    If Not IsNull(Me.Email) Then
    
        EmailAddress = Me.Email
           
        'RunEmailAssessors 'Somehow using where [AssessorID] = rsAssessorsDISTINCT(AssessorID)
        DoCmd.SendObject _
        acSendReport, _
        ReportName, _
        acFormatPDF, _
        EmailAddress, _
        , _
        , _
        EmailSubject, _
        EmailBody, _
        False

    End If
    
    If i <> DCount("*", "QYAssessorsDISTINCT") Then
        DoCmd.GoToRecord , , acNext
    End If
    
Next

End Sub


Private Sub Form_Load()
SendAllEmails
End Sub

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.

[reginaldmerritt]

This workaround has caused a new problem. Any code called n on Form_Load does not run DoCmd.GotoRecor d, , acNext.

Why is that?

I could put a button on the form to run the code required which will in turn will run DoCmd.GotoRecor d, , acNext.
Is there a way of getting around this so DoCmd.GotoRecor d, , acNext will run on Form_Load?

[jimatqsi]

Have you trapped the error to see what error you are getting? What actually happens?

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

Jim

[reginaldmerritt]

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.ReportN ame.Filter = "[FieldName] = 2"
ReportName.Filt er = "[FieldName] = 2"

is this possible?

Many Thanks.

[reginaldmerritt]

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

Reports.ReportN ame.Filter = "[FieldName] = 2"
Reports.ReportN ame.FilterOnLoa d = 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.

DoCmd.OpenReport "MYREPORT", acViewDesign
Reports!MYREPORT.Filter = "[FIELDNAME] = 3"
Reports!MYREPORT.FilterOnLoad = True
DoCmd.Close acReport, "MYREPORT"
EmailSubject = "Subject Header"
EmailBody = "Main Body Text"
ReportName = "MYREPORT"

'RunEmail
DoCmd.SendObject _ 
acSendReport, _ 
ReportName, _ 
acFormatPDF, _ 
EmailAddress, _ 
, _ 
, _ 
EmailSubject, _ 
EmailBody, _ 
False

[reginaldmerritt]

Your also need to set warninigs to be off so the program doesn't ask the user to save the report after editing.

DoCmd.SetWarnings False

DoCmd.OpenReport "MYREPORT", acViewDesign 
Reports!MYREPORT.Filter = "[FIELDNAME] = 3" 
Reports!MYREPORT.FilterOnLoad = True 
DoCmd.Close acReport, "MYREPORT" 

DoCmd.SetWarnings True

EmailSubject = "Subject Header" 
EmailBody = "Main Body Text" 
ReportName = "MYREPORT"

.Filter obviously has restrictions. For deeper manipulation you might consider using an SQL statement and chaning the reports RecordSource.