Emailing a Report

[Barry McCocinner]

Hello all,


I need a second set of eyes or someone that knows VBA better than I. I have a form that when a user selects "yes" generates a report. The report gets displayed as was as saved. On the report I have put an "Email report" button so that once the user verifies the information is correct they can just click and away it goes. The problem is the file name is dynamic. It works fine when generating the report, but when it comes to attaching the report to the email I get a "File not found" error even though the file is there. I've include code for the form and the report. :banghead:



Form VBA for generating and saving report



Private Sub CORLReq_AfterUpdate()
On Error GoTo CORLReq_AfterUpdate_Err


Dim stVendor As String
stVendor = "[VendorName]"

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "VSRA Request", acViewReport, , "[tblprescreen.ID]=" & [tblPrescreen.ID]
DoCmd.OutputTo acOutputReport, , acFormatPDF, "E:\reports\test" & [VendorName] & " Assessment Request.pdf", False, "", , acExportQualityPrint

CORLReq_AfterUpdate_Exit:
Exit Sub

CORLReq_AfterUpdate_Err:
MsgBox Error$
Resume CORLReq_AfterUpdate_Exit

End Sub
------------------------------------------------------------------------------


Report VBA for sending email


Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Dim strPath As String
Dim strFilter As String
Dim strFile As String
Dim stVendor As String
stVendor = "[VendorName]"
strPath = "E:\reports\test" & [VendorName] & " Assessment Request" 'Edit to your path
strFilter = ".pdf"
strFile = Dir(strPath & strFilter)

If strFile <> "" Then
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

With MailOutLook
.BodyFormat = olFormatRichText
.To = "testuser@gmail.com"
''.cc = ""
''.bcc = ""
.Subject = "Request for " & [VendorName]
.HTMLBody = "We are requesting an assessment for " & [VendorName] & ". Please let us know if there are any questions."
.Attachments.Add (strPath & strFile)
'.Send
.Display 'Used during testing without sending (Comment out .Send if using this line)
End With
Else
MsgBox "No file matching " & strPath & strFilter & " found." & vbCrLf & _
"Processing terminated."
Exit Sub 'This line only required if more code past End If
End If

End Sub

 

[Gasman]

Inspect each variable step by step and you should see your issue.
Your filename that you are trying to add has been built incorrectly.?

To me a path is something like "C:\temp\test\test2" and the file the local file name.
Then when you concatenate the two you get a valid full path filename.?

 

[theDBguy]

Hi. I agree. You seem to be using a lot of variables that may be causing some confusion. Try doing a Debug.Print strFile and verify it’s correct. If so, replace your code with simply this.

.Attachments.Add strFile

Not strPath & strFile.

 

[Barry McCocinner]

Gents,

Thank you both. Again, a second and third set of eyes with some helpful suggestions worked like a charm. Used a little from each, removed a few of the strings, made adjustments, and **poof** it's working.

Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Dim strPath As String
Dim stVendor As String
stVendor = "[VendorName]"
strPath = "E:\reports\test" & [VendorName] & " Assessment Request" 'Edit to your path

If strPath <> "" Then
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

With MailOutLook
.BodyFormat = olFormatRichText
.To = "testuser@gmail.com"
''.cc = ""
''.bcc = ""
.Subject = "Request for " & [VendorName]
.HTMLBody = "We are requesting an assessment for " & [VendorName] & ". Please let us know if there are any questions."
.Attachments.Add strPath
'.Send
.Display 'Used during testing without sending (Comment out .Send if using this line)
End With
Else
MsgBox "No file matching " & strPath & strFilter & " found." & vbCrLf & _
"Processing terminated."
Exit Sub 'This line only required if more code past End If
End If

End Sub

 

[theDBguy]

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[Gasman]

That should still not work as you have ".pdf" missing from the filename.?

You should have at least

Option Explicit

at the top of every code module.?

That way when you use a variable you have not declared, you will be warned.?

You are still using StrFilter yet you have removed the Dim statement for it.?
Also do not need stVendor as you do not use it.?

HTH

 

[The_Doc_Man]

Concur with Gasman - I have yet to find a case where "Option Explicit" was the wrong choice to make things work right (after a suitable and inevitable correction cycle).

 

[Barry McCocinner]

Gasman,

I actually have .pdf at the end of the file name. I must have somehow whacked it off when I cut and paste the code for the update.

I will use your advice and add Option Explicit to the beginning of the module.

Thanks again for the help