Email Report

[sue gray]

I posted earlier today and can't find my original message. I have fixed my
earlier problem, but now I have another issue (of course).

The problem I am having is that I don't know how to filter to get the
results I want. My code will select one employee to email the report to, but
the attached report will have all employees included. I have tried
everything I can think of. Help is greatly appreciated.

Option Compare Database

Private Sub Email_RPT_to_All_Emp_Click()
On Error GoTo Some_Err

Dim strBody As String, lngCount As Long, lngRSCount As Long
Dim db As Database
Dim rst As Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("All EmpTimeToDate_Crosstab")

lngRSCount = rst.RecordCount
If lngRSCount = 0 Then
MsgBox "No Reports to email.", vbInformation
Else

Do Until rst.EOF
lngRSCount = lngRSCount + 1
strTo = rst!EmailAddress

DoCmd.SendObject acSendReport, "All Emp Time", acFormatSNP, strTo, ,
, "Monthly Time", "Attached is your monthly time"

rst.MoveNext

Loop

End If
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing
Close


MsgBox "Done sending Employee Time email. ", vbInformation, "Done"

Exit Sub

Some_Err:
MsgBox "Error (" & CStr(Err.Number) & ") " & Err.Description, _
vbExclamation, "Error!"


End Sub

 

[Alex Dybenko]

Hi,
I found my old post to you:

here some air code, hope it explains my idea:

set rst=currentdb.openrecordset("MyQueryWithEmployees",dbopenforwardonly)
do until rst.eof
currentdb.querydefs("myquery").sql="Select .... From .... Where
EmployeeID=" & rst.EmployeeID
docmd.sendobject acSendReport,"MyReport",,rst!Email
rst.movenext
loop

myquery - is a recordsource query of your report, which you are sending

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[sue gray]

Thanks for the response. I don't know what is happening I was posting, but
could never find the post again and I am not being notified of replies. So
sorry to post more that once.

I did use your air code and other peoples. I have come along ways since my
last post. The main problem I am still having and I have tried numerous
times to fix is filtering the report for the employee. I step thru the code
and I can see the emp # & email address change accordingly, but I still get
the whole report.

I did add this line:
DoCmd.OpenReport "All Emp Time", acViewPreview, , "[EmployeeID] = strempid"

First time thru it worked fine, but next loop it sent email to correct
employee, but the report was the first employee. Attached is my new code.
Thanks for any help. Sorry for the multiple post.

Private Sub Email_RPT_to_All_Emp_Click()
On Error GoTo Some_Err

Dim strBody As String, lngCount As Long, lngRSCount As Long
Dim db As Database
Dim rst As Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("All EmpTimeToDate_Crosstab")

lngRSCount = rst.RecordCount
If lngRSCount = 0 Then
MsgBox "No Reports to email.", vbInformation
Else

Do Until rst.EOF
lngRSCount = lngRSCount + 1
strempid = rst!EmployeeID
strto = rst!EmailAddress

DoCmd.OpenReport "All Emp Time", acViewPreview, , "[EmployeeID] =
strempid"

DoCmd.SendObject acSendReport, "All Emp Time", acFormatSNP, strto, ,
, "Monthly Time", "Attached is your monthly time"


rst.MoveNext

Loop

End If
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing
Close


MsgBox "Done sending Employee Time email. ", vbInformation, "Done"

Exit Sub

Some_Err:
MsgBox "Error (" & CStr(Err.Number) & ") " & Err.Description, _
vbExclamation, "Error!"


End Sub