zip up several reports and then send

[Noel2]

Here is my situation. I have a function that opens up a report that prints out a single report for each value in a query that I have for certain job numbers. I know that I can manipulate this to output each report to a snapshot file and then send them on to each of the co-workers using a combination of the code I have with this function and other code that I have used before to do something similar. What I would like to be able to do is to possibly shell to winzip and zip all of the snapshot files up before I send them off in an e-mail. Below I have the current function:

Function RockinLikeFreightTrain()
Dim dbsreport As Database
Dim rstReport As Recordset
Dim astro As QueryDef
Dim qdy As QueryDef

Set dbsreport = CurrentDb
Set astro = dbsreport.CreateQueryDef("temp_query", "SELECT tbl_man.job_num " & _
"FROM tbl_man GROUP BY tbl_man.job_num;&quot
Set qdy = dbsreport.QueryDefs("temp_query&quot

Set rstReport = qdy.OpenRecordset()

With rstReport
Do While Not .EOF
StreetBall = ![job_num]
.MoveNext
DoCmd.OpenReport "rpt_crosstb_test", acViewNormal
DoCmd.Close acReport, "rpt_crosstb_test"
Loop
End With

dbsreport.QueryDefs.Delete ("temp_query&quot

rstReport.Close
End Function

My other code that I am going to pull from to get the appropriate info is below:

Function TheJuiceIsLoose()

Dim stDocName As String
Dim stDogName As String
Dim stDicName As String
Dim appoutlook As Outlook.Application
Dim mailoutlook As Outlook.MailItem

Set appoutlook = CreateObject("outlook.application&quot
Set mailoutlook = appoutlook.CreateItem(olMailItem)

stDocName = "rpt_crosstb"
stDogName = InputBox("Enter Time Sheet Description&quot
stDicName = "N:\Shared\ppe_" & stDogName & ".snp"
DoCmd.OutputTo acOutputReport, stDocName, "Snapshot Format", stDicName
With mailoutlook
.To = "obb@who.com;tbl@who.com"
.Subject = "PPE " & stDogName
.Attachments.Add stDicName
.Display
End With

End Function

Please let me know what you think as I do not like to send individual reports to these people.

Thank you

 

[Noel2]

please note that this is the code that I have come up for a combination of the two. But, I still want to zip all of the snapshot files together before I e-mail them off. Please let me know if you have any thoughts:

Function RockinLikeFreightTrain()
Dim dbsreport As Database
Dim rstReport As Recordset
Dim astro As QueryDef
Dim qdy As QueryDef
Dim stDocName As String
Dim stDogName As String
Dim stDicName As String
Dim appoutlook As Outlook.Application
Dim mailoutlook As Outlook.MailItem

Set dbsreport = CurrentDb
Set astro = dbsreport.CreateQueryDef("temp_query", "SELECT tbl_man.job_num " & _
"FROM tbl_man GROUP BY tbl_man.job_num;&quot
Set qdy = dbsreport.QueryDefs("temp_query&quot
Set appoutlook = CreateObject("outlook.application&quot
Set mailoutlook = appoutlook.CreateItem(olMailItem)

Set rstReport = qdy.OpenRecordset()

stDocName = "rpt_timesht_job_num"
stDogName = InputBox("Enter Time Sheet Description&quot

With mailoutlook
.To = "mo@yourmom.com"
.Subject = "Pay Period Ending " & stDogName
End With
With rstReport
Do While Not .EOF
StreetBall = ![job_num]
stDicName = "N:\Shared\HVAC\One Source\Timesheets\2003\" & StillBallin() _
& "_ppe_" & stDogName & ".snp"
DoCmd.OutputTo acOutputReport, stDocName, "Snapshot Format", stDicName
mailoutlook.Attachments.Add stDicName
.MoveNext
Loop
End With

mailoutlook.Display

dbsreport.QueryDefs.Delete ("temp_query&quot

rstReport.Close
End Function