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.
8 5464 jimatqsi 1,271
Recognized Expert Top Contributor
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
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?
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.
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?
jimatqsi 1,271
Recognized Expert Top Contributor
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
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.
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. -
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
-
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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Prakash Wadhwani |
last post by:
Hi !! I have been browsing around but have not been able to find a
simple, lucid solution ... or maybe I'm just too confused. I know this
has been asked before by many ... but pls bear with me.
...
|
by: Fred |
last post by:
Is there a good code sample, in C#, showing how to send
eamil with attachment, from a Windows Form like 'Send To'
in Word, that will pickup Contacts in Outlook/Exchange
2000.
I have searched...
|
by: Michal |
last post by:
Hi
I need to connect to Exchange 5.5 from .Net Web Application and send email
using user profile.
I did it two years ago on ASP but now I have to migrate it to .Net.
I can not find any...
|
by: Mr T |
last post by:
I know how to send email from Access and I know how to create a custom
form in Outlook. but.... How do I put the email info from Access into
the Outlook custom form ???
Dim MyDB As Database
Dim...
|
by: shil |
last post by:
Hi,
I'm using FW 2.0 to send an email with an attachment using
System.Net.Mail class. If I try to attach a file from my file system,
I could successfully send the eamil. But I need to generate a...
| |
by: monyrajesh |
last post by:
<%@LANGUAGE="VBSCRIPT"%>
<%
nam=request.Form("name")
mail=request.Form("email")
phoneres=request.Form("res")
phonembl=request.Form("mobile")
adrs=request.Form("address")...
|
by: PleaseHelpMe |
last post by:
Any1 can tell me how to go about sending email with attachment in vb programming code. I know SMTP is one option, but I can't seem to get it working. Any other suggestion??
Thanks in advance
|
by: sunraj |
last post by:
With the Following Fields can Anybody Help me, How do I send email from ASP using SMTP Authentication
<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta...
|
by: ram1927 |
last post by:
Here is my platform:
.net framewrok 1.1
Windows Xp Propfessional
MS Office 2007.
Visual Studio 2003.
Window based application.
I did below mentioned steps:
1. Right click on C#...
|
by: beacon |
last post by:
Hi everybody,
I'm sure that everyone cringes when they see "email" in the subject for posts in the forum, but I'm hoping that what I'm trying to do is different enough to present a challenge...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |