I have a problem where I have code that first outputs several different variations of a report into snapshot files, saves them, zips them into a zip file, and then is supposed to e-mail them out. The problem that I have is that I am getting the same error over and over again:
Run-time error '-589889534 (dcd70002)':
The system cannot find the file specified
whenever I get to the point where it tries to attach the zip file to the e-mail. I am using WinZip's command line feature to zip up the files with this function:
Function DontBelieveTheHype(ZipFileName As String, _
Optional SourceFiles As String, _
Optional ZipOptions As String)
Wzzip = "C:\Program Files\WinZip\Wzzip.exe"
ZipCommandLine = Wzzip & " " & ZipOptions & " " & ZipFileName & " " & SourceFiles
TaskID = Shell(ZipCommandLine, vbHide)
End Function
Right now, I am working with a work around that just e-mails all of the reports unzipped. The code for this is below:
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 stDykName As String
Dim appoutlook As Outlook.Application
Dim mailoutlook As Outlook.MailItem
Set dbsreport = CurrentDb
Set astro = dbsreport.CreateQueryDef("timely_query", "SELECT tbl_man.job_num " & _
"FROM tbl_man GROUP BY tbl_man.job_num;"
Set qdy = dbsreport.QueryDefs("timely_query"
Set appoutlook = CreateObject("outlook.application"
Set mailoutlook = appoutlook.CreateItem(olMailItem)
Set rstReport = qdy.OpenRecordset()
stDocName = "rpt_timesht_job_num"
stDogName = InputBox("Enter Time Sheet Description"
With mailoutlook
.To = "bob@2onebill.com"
.Subject = "Pay Period Ending " & stDogName
End With
With rstReport
Do While Not .EOF
StreetBall = ![job_num]
stDicName = "N:\Shared\HVAC\One\Timesheets\2003\" & StillBallin() _
& "_ppe_" & stDogName & ".snp"
DoCmd.OutputTo acOutputReport, stDocName, "Snapshot Format", stDicName
mailoutlook.Attachments.Add stDicName
.MoveNext
Loop
End With
DontBelieveTheHype "N:\Shared\HVAC\One_Source\Timesheets\2003\" & stDogName & ".zip", _
"N:\Shared\HVAC\One_Source\Timesheets\2003\*_ppe_" & stDogName & ".snp", "-yp"
mailoutlook.Display
dbsreport.QueryDefs.Delete ("timely_query"
rstReport.Close
End Function
to get it working the way i want it to, I have attempted moving the:
mailoutlook.Attachments.Add "N:\Shared\HVAC\One_Source\Timesheets\2003\" & stDogName & ".zip"
statement after the DontBelieveTheHype function and am getting the error. I have tried this with existing zip files and it works fine. The problem only seems to occur when I am using the zip file that I just created. Is there a way to fix this?
Run-time error '-589889534 (dcd70002)':
The system cannot find the file specified
whenever I get to the point where it tries to attach the zip file to the e-mail. I am using WinZip's command line feature to zip up the files with this function:
Function DontBelieveTheHype(ZipFileName As String, _
Optional SourceFiles As String, _
Optional ZipOptions As String)
Wzzip = "C:\Program Files\WinZip\Wzzip.exe"
ZipCommandLine = Wzzip & " " & ZipOptions & " " & ZipFileName & " " & SourceFiles
TaskID = Shell(ZipCommandLine, vbHide)
End Function
Right now, I am working with a work around that just e-mails all of the reports unzipped. The code for this is below:
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 stDykName As String
Dim appoutlook As Outlook.Application
Dim mailoutlook As Outlook.MailItem
Set dbsreport = CurrentDb
Set astro = dbsreport.CreateQueryDef("timely_query", "SELECT tbl_man.job_num " & _
"FROM tbl_man GROUP BY tbl_man.job_num;"
Set qdy = dbsreport.QueryDefs("timely_query"
Set appoutlook = CreateObject("outlook.application"
Set mailoutlook = appoutlook.CreateItem(olMailItem)
Set rstReport = qdy.OpenRecordset()
stDocName = "rpt_timesht_job_num"
stDogName = InputBox("Enter Time Sheet Description"
With mailoutlook
.To = "bob@2onebill.com"
.Subject = "Pay Period Ending " & stDogName
End With
With rstReport
Do While Not .EOF
StreetBall = ![job_num]
stDicName = "N:\Shared\HVAC\One\Timesheets\2003\" & StillBallin() _
& "_ppe_" & stDogName & ".snp"
DoCmd.OutputTo acOutputReport, stDocName, "Snapshot Format", stDicName
mailoutlook.Attachments.Add stDicName
.MoveNext
Loop
End With
DontBelieveTheHype "N:\Shared\HVAC\One_Source\Timesheets\2003\" & stDogName & ".zip", _
"N:\Shared\HVAC\One_Source\Timesheets\2003\*_ppe_" & stDogName & ".snp", "-yp"
mailoutlook.Display
dbsreport.QueryDefs.Delete ("timely_query"
rstReport.Close
End Function
to get it working the way i want it to, I have attempted moving the:
mailoutlook.Attachments.Add "N:\Shared\HVAC\One_Source\Timesheets\2003\" & stDogName & ".zip"
statement after the DontBelieveTheHype function and am getting the error. I have tried this with existing zip files and it works fine. The problem only seems to occur when I am using the zip file that I just created. Is there a way to fix this?
