Run-Time Error (1 Viewer)

Niroshana

New member
Local time
Tomorrow, 03:18
Joined
Jul 10, 2020
Messages
5
The following code I use to send email with a report as an attachment. But it gives the error message "Cannot find the file, Verify the path & file name are correct"
I checked file name (LETTEREMAI) is correct. Please help me to solve the issue.

Private Sub Command29_Click()

Dim emailTo As String
Dim emailCc As String
Dim emailSubject As String
Dim emailText As String
Dim fileName As String


Dim outApp As New Outlook.Application
Dim outMail As Outlook.MailItem
Dim outlookStarted As Boolean


On Error Resume Next
Set outApp = GetObject("Outlook.Application")
On Error GoTo 0
If outApp Is Nothing Then
Set outApp = CreateObject("Outlook.Application")
outlookStarted = True
End If


emailTo = Me.SD_EMAIL 'active form data
emailCc = Me.SD_CCEM 'active form data
emailSubject = "Renewal of Service Agreement -" & Me.S_RNO 'active form data

emailText = "Dear Sir" & vbCrLf & vbCrLf & _
"Please refer the letter is attached herewith" & vbCrLf & vbCrLf & _
"Regards" & vbCrLf & _
"Purchasing Division"

fileName = "Service Agreement-" & Me.S_RNO & ""
DoCmd.OutputTo acReport, "LETTEREMAIL", acFormatPDF, fileName, False


Set outMail = outApp.CreateItem(olMailItem)
outMail.To = emailTo
outMail.Subject = emailSubject
outMail.Body = emailText
outMail.Attachments.Add fileName
outMail.Send


If outlookStarted Then
outApp.Quit
End If

Set outMail = Nothing
Set outApp = Nothing




End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:48
Joined
Sep 21, 2011
Messages
10,316
You need the FULLPATH for the filename when attaching. :(
You have used a default path when outputtng file?

So go and look and see where it has been saved?, likely the same folder as your DB?
Also LETTERMAIL is not the name of the file, that is the report?. The file is called Service Agreement plus whatever that S_RNO holds.

Also please post code within code tags to keep indentation and spacing.
 

oleronesoftwares

Passionate Learner
Local time
Today, 14:48
Joined
Sep 22, 2014
Messages
1,113
DoCmd.OutputTo acReport, "LETTEREMAIL", acFormatPDF, fileName, False
You are trying to export a report called letteremail, access is not seeing the report, are you surethe spelling of the report name is correct?
Verify the path & file name are correct"
the full pathname of the file is required.
 

Niroshana

New member
Local time
Tomorrow, 03:18
Joined
Jul 10, 2020
Messages
5
Thanking you for your quick responses,
Images of my form and report menu are attached herewith for your reference.

 

Attachments

  • Form (2).jpg
    Form (2).jpg
    61.2 KB · Views: 143
  • Report (2).jpg
    Report (2).jpg
    31.4 KB · Views: 79

Gasman

Enthusiastic Amateur
Local time
Today, 22:48
Joined
Sep 21, 2011
Messages
10,316
Again, a picture not worth a thousand words. :( I can barely read those on my laptop?

Walk through the code with F8. I expect it to error where you add the attachment, not where you create the report, which @oleronesoftwares seems to think where the fault lies?

Post back where the error lies, or try my advice in my first reply?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:48
Joined
Feb 28, 2001
Messages
22,522
I want to amplify one statement here: You are using Outlook.Application methods here. That means you are running a copy of Outlook that is controlled by Access in a separate session/task. Which means they can have different "current default directories" when run this way. When you create the file, you use

Code:
fileName = "Service Agreement-" & Me.S_RNO & ""
DoCmd.OutputTo acReport, "LETTEREMAIL", acFormatPDF, fileName, False

This means that your file is ".\Service Agreement-" & Me.S_RNO - which is fine if you understand the implications of ".\" - which is to say "the file is created in the current default directory of the running utility" - which is Access.

Then you attach it using

Code:
outMail.Attachments.Add fileName

This says that your file is ".\Service Agreement-" & Me.S_RNO - which means "the file is in the current default direct directory of the running utility" - which is Outlook. This is why you should always include a formal drive/path combination when diddling with files like this.

If you don't mind a little bit of parsing, currentDB.Name is the fully qualified drive, path, name, and type of the open database. (If it is a split FE/BE case, it is the FE file's full specification.)

Code:
FilePath = Left$( currentDB.Name, InStrRev( currentDB.Name, "/" ) )

Then you can concatenate the file path into the file name variable before you try to actually use it, and you will KNOW without doubt where that file was created and thus where you can find it later. Note that you might have to use "\" rather than "/" depending on some setting or another that I don't recall at the moment. Whatever your system uses as a file-path separator is what you need to use.
 

Users who are viewing this thread

Top Bottom