CFMI
asked on
MS Access VBA does not attach zip file to email
Experts,
Please assist by identifying the correction of the error message listed below.
After receiving the message, the attachment VBA statement was highlighted so I checked the directory of the zip file and nothing was incorrect; therefore, the code should be able to locate.
Thank you in advance for your help!
Error message:
cannot open the file: mk:@MSITStore:C:\Program Files (x86)\Common Files\Microsoft Shared\Office12\1033\jeter r40.chm
VBA:
Private Sub SelecteEmailBill_Click()
If IsNull(Forms![FrmMain]!Spe cificVendo r) Then
MsgBox "Please Select a Plan!!!"
Else
DoCmd.SetWarnings False
Dim TblEmailPayTemp As Recordset
Dim strSql As String
Dim EmailAddress As String
Dim Contact As String
Dim FirstName As String
Dim FileName As String
Dim mess_body As String
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Appl ication")
Set MailOutLook = appOutLook.CreateItem(olMa ilItem)
Dim ContactEM As String
Dim FirstnameC As String
Dim SourceTable As String
Set db = CurrentDb
Set rstTblzip = db.OpenRecordset("TblEmail PayTemp")
Dim oApp, unzipfile, pathname, location
Set oApp = CreateObject("Shell.Applic ation")
pathname = "S:\Vendor Fee Billing\TempBills\"
location = [rstTblzip].[FileName]
NewZip (pathname & location & ".zip")
rstTblzip.MoveFirst
DoCmd.OpenQuery "QdelTblEmailPayTemp", acViewNormal, acEdit
DoCmd.OpenQuery "QappTblEmailPaymentsTempS elected", acViewNormal, acEdit
'FileName = "S:\Vendor Fee Billing\TempBills\" & [rstTblzip].[Contact] & "MPP.xls"
'FileNameR = "S:\Vendor Fee Billing\TempBills\" & [rstTblzip].[Contact] & "MPP.pdf"
DoCmd.OutputTo acOutputQuery, "1f5_Email", acFormatXLS, "H:\MonthlyCFVendorFeeInvo ice.xls", False, ""
Dim xlApp As Object, xlWb As Object, xlWs As Object
Set xlApp = CreateObject("Excel.Applic ation")
Set xlWb = xlApp.Workbooks.Open("H:\M onthlyCFVe ndorFeeInv oice.xls")
For Each xlWs In xlWb.Worksheets
xlWs.PageSetUp.Orientation = 2 'xlLandscape
Next
xlWb.Save
xlWb.Close
Set xlWs = Nothing
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing
DoCmd.OutputTo acOutputReport, "Monthly_parplan_summary_d etail_Emai l", acFormatPDF, "H:\MonthlyCFVendorFeeInvo ice.pdf", False, ""
Pause (0.5) 'for a half second pause
'FileCopy "H:\MonthlyCFVendorFeeInvo ice.xls", "H:\" & "AAA" & [rstTblzip].[Contact] & ".xls"
'FileCopy "H:\MonthlyCFVendorFeeInvo ice.pdf", "H:\" & "AAA" & [rstTblzip].[Contact] & ".pdf"
Call ZipFiles
Pause (4.5) 'for a one and a half second pause
ContactEM = DLookup("[FileName]", "TblEmailPayTemp")
FirstnameC = DLookup("FirstName", "TblEmailPayTemp")
Set appOutLook = CreateObject("Outlook.Appl ication")
Set MailOutLook = appOutLook.CreateItem(olMa ilItem)
With MailOutLook
.BodyFormat = olFormatHTML
.To = DLookup("EmailAddress", "TblEmailPayTemp")
.Subject = "SECURE CareFirst(Host) VendorFees Invoice to Home Plan"
.HTMLBody = (FirstnameC) & ", " & "<BR>" & "<BR>" & _
"Please find attached new invoices and backup data for Third Party Vendor fee reimbursements as we are catching up with outstanding vendor fees for your plans. " & _
"When mailing us a check, please make sure to list our mail stop number 01-660 in the address. " & _
"Feel free to contact me if you have any questions. " & _
(vbCr & vbLf & vbCrLf & vbCrLf & vbCrLf & vbCrLf & "Thank you for a prompt response to this matter.") & "<BR>" & "<BR>" & _
"NOTICE: This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure " & _
"or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message."
.Attachments.Add "S:\Vendor Fee Billing\TempBills\" & [rstTblzip].[FileName] & ".zip"
.Close 0
End With
rstTables.MoveNext
Set MailOutLook = Nothing
'Call KillSomeTime
Set oApp = Nothing
DoCmd.SetWarnings True
Beep
MsgBox "A zipped file with the Report and Spreadsheet was created for the Selected Plan!!! Check Outlook Draft Folder for e-mail details"
End If
End Sub
Please assist by identifying the correction of the error message listed below.
After receiving the message, the attachment VBA statement was highlighted so I checked the directory of the zip file and nothing was incorrect; therefore, the code should be able to locate.
Thank you in advance for your help!
Error message:
cannot open the file: mk:@MSITStore:C:\Program Files (x86)\Common Files\Microsoft Shared\Office12\1033\jeter
VBA:
Private Sub SelecteEmailBill_Click()
If IsNull(Forms![FrmMain]!Spe
MsgBox "Please Select a Plan!!!"
Else
DoCmd.SetWarnings False
Dim TblEmailPayTemp As Recordset
Dim strSql As String
Dim EmailAddress As String
Dim Contact As String
Dim FirstName As String
Dim FileName As String
Dim mess_body As String
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Appl
Set MailOutLook = appOutLook.CreateItem(olMa
Dim ContactEM As String
Dim FirstnameC As String
Dim SourceTable As String
Set db = CurrentDb
Set rstTblzip = db.OpenRecordset("TblEmail
Dim oApp, unzipfile, pathname, location
Set oApp = CreateObject("Shell.Applic
pathname = "S:\Vendor Fee Billing\TempBills\"
location = [rstTblzip].[FileName]
NewZip (pathname & location & ".zip")
rstTblzip.MoveFirst
DoCmd.OpenQuery "QdelTblEmailPayTemp", acViewNormal, acEdit
DoCmd.OpenQuery "QappTblEmailPaymentsTempS
'FileName = "S:\Vendor Fee Billing\TempBills\" & [rstTblzip].[Contact] & "MPP.xls"
'FileNameR = "S:\Vendor Fee Billing\TempBills\" & [rstTblzip].[Contact] & "MPP.pdf"
DoCmd.OutputTo acOutputQuery, "1f5_Email", acFormatXLS, "H:\MonthlyCFVendorFeeInvo
Dim xlApp As Object, xlWb As Object, xlWs As Object
Set xlApp = CreateObject("Excel.Applic
Set xlWb = xlApp.Workbooks.Open("H:\M
For Each xlWs In xlWb.Worksheets
xlWs.PageSetUp.Orientation
Next
xlWb.Save
xlWb.Close
Set xlWs = Nothing
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing
DoCmd.OutputTo acOutputReport, "Monthly_parplan_summary_d
Pause (0.5) 'for a half second pause
'FileCopy "H:\MonthlyCFVendorFeeInvo
'FileCopy "H:\MonthlyCFVendorFeeInvo
Call ZipFiles
Pause (4.5) 'for a one and a half second pause
ContactEM = DLookup("[FileName]", "TblEmailPayTemp")
FirstnameC = DLookup("FirstName", "TblEmailPayTemp")
Set appOutLook = CreateObject("Outlook.Appl
Set MailOutLook = appOutLook.CreateItem(olMa
With MailOutLook
.BodyFormat = olFormatHTML
.To = DLookup("EmailAddress", "TblEmailPayTemp")
.Subject = "SECURE CareFirst(Host) VendorFees Invoice to Home Plan"
.HTMLBody = (FirstnameC) & ", " & "<BR>" & "<BR>" & _
"Please find attached new invoices and backup data for Third Party Vendor fee reimbursements as we are catching up with outstanding vendor fees for your plans. " & _
"When mailing us a check, please make sure to list our mail stop number 01-660 in the address. " & _
"Feel free to contact me if you have any questions. " & _
(vbCr & vbLf & vbCrLf & vbCrLf & vbCrLf & vbCrLf & "Thank you for a prompt response to this matter.") & "<BR>" & "<BR>" & _
"NOTICE: This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure " & _
"or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message."
.Attachments.Add "S:\Vendor Fee Billing\TempBills\" & [rstTblzip].[FileName] & ".zip"
.Close 0
End With
rstTables.MoveNext
Set MailOutLook = Nothing
'Call KillSomeTime
Set oApp = Nothing
DoCmd.SetWarnings True
Beep
MsgBox "A zipped file with the Report and Spreadsheet was created for the Selected Plan!!! Check Outlook Draft Folder for e-mail details"
End If
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I tried using the UNC and it displayed an error message, "Record is deleted and run time error 3167". Debugging displays the Attachment statement.
.Attachments.Add "\\carefirst.com\corp\shar ed\Vendor Fee Billing\TempBills\" & [rstTables].[FileName] & ".zip"
.Attachments.Add "\\carefirst.com\corp\shar
ASKER
Turns out, it was the File Name, at that point in time - Thank You!
ASKER
Private Sub Command20_Click()
DoCmd.SetWarnings False
Dim TblEmailPayments As Recordset
Dim strSql As String
Dim EmailAddress As String
Dim Contact As String
Dim FirstName As String
Dim FileName As String
Dim mess_body As String
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Appl
Set MailOutLook = appOutLook.CreateItem(olMa
Dim ContactEM As String
Dim FirstnameC As String
Dim SourceTable As String
Set db = CurrentDb
Set rstTables = db.OpenRecordset("TblEmail
Dim oApp, unzipfile, pathname, location
Set oApp = CreateObject("Shell.Applic
pathname = "S:\Vendor Fee Billing\TempBills\"
location = [rstTables].[FileName]
NewZip (pathname & location & ".zip")
rstTables.MoveFirst
Do While Not rstTables.EOF
DoCmd.OpenQuery "QdelTblEmailPayTemp", acViewNormal, acEdit
strSql = "INSERT INTO TblEmailPayTemp([Contact],
"SELECT TblEmailPayments.Contact, TblEmailPayments.EmailAddr
FileName = "S:\Vendor Fee Billing\TempBills\" & [rstTables].[Contact] & "MPP.xls"
FileNameR = "S:\Vendor Fee Billing\TempBills\" & [rstTables].[Contact] & "MPP.pdf"
DoCmd.RunSQL strSql
DoCmd.OutputTo acOutputQuery, "1f5_Email", acFormatXLS, "H:\MonthlyCFVendorFeeInvo
Dim xlApp As Object, xlWb As Object, xlWs As Object
Set xlApp = CreateObject("Excel.Applic
Set xlWb = xlApp.Workbooks.Open("H:\M
For Each xlWs In xlWb.Worksheets
xlWs.PageSetUp.Orientation
Next
xlWb.Save
xlWb.Close
Set xlWs = Nothing
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing
DoCmd.OutputTo acOutputReport, "Monthly_parplan_summary_d
Pause (0.5) 'for a half second pause
FileCopy "H:\MonthlyCFVendorFeeInvo
FileCopy "H:\MonthlyCFVendorFeeInvo
Call ZipFiles
Pause (0.5) 'for a half second pause
ContactEM = DLookup("[FileName]", "TblEmailPayTemp")
FirstnameC = DLookup("Firstname", "TblEmailPayTemp")
Set appOutLook = CreateObject("Outlook.Appl
Set MailOutLook = appOutLook.CreateItem(olMa
With MailOutLook
.BodyFormat = olFormatHTML
.To = DLookup("EmailAddress", "TblEmailPayTemp")
.Subject = "SECURE CareFirst(Host) VendorFees Invoice to Home Plan"
.HTMLBody = (FirstnameC) & ", " & "<BR>" & "<BR>" & _
"Please find attached new invoices and backup data for Third Party Vendor fee reimbursements as we are catching up with outstanding vendor fees for your plans. " & _
"When mailing us a check, please make sure to list our mail stop number 01-660 in the address. " & _
"Feel free to contact me if you have any questions. " & _
(vbCr & vbLf & vbCrLf & vbCrLf & vbCrLf & vbCrLf & "Thank you for a prompt response to this matter.") & "<BR>" & "<BR>" & _
"NOTICE: This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure " & _
"or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message."
.Attachments.Add "S:\Vendor Fee Billing\TempBills\" & [rstTables].[FileName] & ".zip"
.Close 0
End With
rstTables.MoveNext
Set MailOutLook = Nothing
'Call KillSomeTime
Loop
Set oApp = Nothing
DoCmd.SetWarnings True
Beep
MsgBox "A zipped file with the Report and Spreadsheet was created for all Accounts!!! Check Outlook Draft Folder for e-mail details"
End Sub