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 5482 jimatqsi 1,274
Recognized Expert Top Contributor
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
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.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 1,274
Recognized Expert Top Contributor
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
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.
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
-
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.
Step 1:
I want to place a button which outputs my report as a snapshot to a
specific folder. What is he VBA code to acomplish this ?
Step2:
I...
|
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 all known sources.
Can someone please help.
Regards,
|
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 solutions in msdn to resolve it.
Any idea how I can do this ?
Michal
|
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 MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
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 report
on the fly and send an email with that report as an attachment. Here
is the code I have.
...
| |
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")
qul=request.Form("quali")
aply=request.Form("apply")
expr=request.form("exper")
|
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 http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title></title>
|
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# projeect, select "Add Referece"
|
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 instead of an aneurysm. =)
I'm creating a form with a list box of values that, when selected, will create a filter for a report. On this same form, I...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
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, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
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 presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
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 last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
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 can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |