OK This tactic worked, and report is done. Thank you Duane! One problem I am having is with email function:
DoCmd.SendObject acSendReport, "MTA_WeeklyOvertimeRprt_ByDept.pdf", strOutputFormat, strTo, strCC, , "All Department - Weekly OT Report", "Attached is the Weekly Overtime Report for All departments.", _
False, "S:\FinMgt\Beavin_Archives\JS\Overtime Data\WeeklySummaryReports\MTA_WeeklyOvertimeRprt_ByDept.pdf"
when i run the email function driven by macro on the main form I receive following error "Cannot find obect 1"
This email function has worked for me previously so I cannor see why this is occurring. Any thoughts.
----------------------------------------------------------------------------------------------------------------------------------
Function EmailReports()
Dim strOutputFormat As String
Dim strObjectName As String
strOutputFormat = "PDF Format (*.pdf)"
strObjectName = "MTA_WeeklyOvertimeRprt_ByDept.pdf"
'strOutputFormat = "JPG Format (*.jpg)"
'strOutputFormat = "XLS Format (*.xls)"
'GENERAL SYNTAX
'DoCmd.SendObject [objecttype][, objectname][, outputformat][, to][, cc][, bcc][, subject][, messagetext]
'[, editmessage][, templatefile]
'acFormatPDF
'Reference to Current Database
Dim db As Database
Set db = CurrentDb()
'Identify table with names
Dim tbl As TableDef
Set tbl = db.TableDefs("ListA")
'Declare a string variable to hold the names
Dim strTo As String
strTo = ""
Dim strname As String
strname = ""
'Declare a counter for a loop
Dim i As Integer
i = 0
'Open a recordset against the table with names
Dim rst As Recordset
Set rst = tbl.OpenRecordset
With rst
If .RecordCount > 0 Then 'Verify records even exist first!
.MoveFirst 'Moves to the first position
Do While i <= .RecordCount And Not .EOF
strname = rst!Name
strTo = strTo & ";" & strname
i = i + 1
.MoveNext
Loop
Else
'Do Nothing
End If
End With
'reset strTo without preceeding comma
strTo = Mid(strTo, 2, Len(strTo))
Debug.Print strTo
' 2nd step Begin process of building CC list-----------------------------------------------------------
Dim tbl2 As TableDef
Set tbl2 = db.TableDefs("ListB")
Dim strCC As String
strCC = ""
Dim strName2 As String
strName2 = "ListB"
[URL unfurl="true"]https://res.cloudinary.com/engineering-com/image/upload/v1704301613/tips/MTA_WeeklyOvertimeRprt_ByDept_x1qafd.pdf[/url]
i = 0
'Open a recordset against the table with names
Dim rst2 As Recordset
Set rst2 = tbl2.OpenRecordset
With rst2
If .RecordCount > 0 Then 'Verify records even exist first!
.MoveFirst 'Moves to the first position
Do While i <= .RecordCount And Not .EOF
strName2 = rst2!Name
strCC = strCC & ";" & strName2
i = i + 1
.MoveNext
Loop
Else
'Do Nothing
End If
End With
'reset strTo without preceeding comma
strCC = Mid(strCC, 2, Len(strCC))
' 3rd step Begin process of building CC list------------------------------------------------------------
Dim tbl3 As TableDef
Set tbl3 = db.TableDefs("ListF")
Dim strToC As String
strToC = ""
Dim strName3 As String
strName3 = "ListF"
i = 0
'Open a recordset against the table with names
Dim rst3 As Recordset
Set rst3 = tbl3.OpenRecordset
With rst3
If .RecordCount > 0 Then 'Verify records even exist first!
.MoveFirst 'Moves to the first position
Do While i <= .RecordCount And Not .EOF
strName3 = rst3!Name
strToC = strToC & ";" & strName3
i = i + 1
.MoveNext
Loop
Else
'Do Nothing
End If
End With
'reset strTo without preceeding comma
strToC = Mid(strToC, 2, Len(strToC))
'4th step send out the 2 emails to the groups defined in the ListA, ListB, ListC tables
'original text for ListA below
'DoCmd.SendObject acSendReport, "All_Depts_Rprt", strOutputFormat, strTo, strCC, , "All Department - Weekly ID Sales ReportAB", "Attached is the ID Sales Report for All departments.", _
'False, "M:\Corp_Mkt\Non-Perishables\Reports\IdSales"
'rewrite
DoCmd.SendObject acSendReport, "MTA_WeeklyOvertimeRprt_ByDept.pdf", strOutputFormat, strTo, strCC, , "All Department - Weekly OT Report", "Attached is the Weekly Overtime Report for All departments.", _
False, "S:\FinMgt\Beavin_Archives\JS\Overtime Data\WeeklySummaryReports\MTA_WeeklyOvertimeRprt_ByDept.pdf"
Debug.Print strCC
Debug.Print strToC
End Function
