Solved Run Time Error while attaching the PDF to the Email (1 Viewer)

mrk777

Member
Local time
Tomorrow, 01:59
Joined
Sep 1, 2020
Messages
60
Hi Team, I use the below code to attach the Access Report in the email. It was working fine earlier, but now I'm getting Run Time Error "cannot find this file verify the path"

I'm facing the issue when the attachment is added (Attachments.add filename).

Code:
Private Sub Send_Mail_Click()


Dim Rptname As String

Rptname = Me.cmb_ReportName.Value

Dim oApp As New Outlook.Application
Dim oEmail As Outlook.MailItem
Dim fileName As String, todayDate As String
Dim Subjectline As String, s As String, i As Long, j As Long
Dim Bodyline As String
Dim UserNames As String

'Export report in same folder as db with date stamp
todayDate = Format(Date, "MMDDYYYY")

If Left(Rptname, 3) = "Rpt" Then
    fileName = Replace(Trim(Mid(Rptname, 5, 100)), "_", " ") & " Report - " & todayDate & ".pdf"
    i = InStr(Rptname, "_")
    j = InStrRev(Rptname, "_")
    s = Mid(Rptname, i + 1, j - i - 1)
    Subjectline = s
Else:
    fileName = Rptname & " Report - " & todayDate & ".pdf"
End If

If Rptname = "Rpt_Overall_Summary" Or Rptname = "Rpt_Summary_Overview" Then
    i = InStr(Rptname, "_")
    'j = InStrRev(Rptname, "_")
    s = Mid(Rptname, i + 1)
    s = Replace(s, "_", " ")
    Subjectline = s
End If

On Error Resume Next
    UserNames = CreateObject("excel.application").UserName
On Error GoTo 0

If Form_Navigator_Form.FilterbyYear.Value <> "" Then
    Subjectline = "F&A OE - " & Subjectline & " - " & Form_Navigator_Form.FilterbyYear.Value
    Bodyline = "Hi Team," & vbNewLine & vbNewLine & _
            "Hope you are doing good and safe." & vbNewLine & vbNewLine & _
            "Attached is the " & s & " - Report - " & Form_Navigator_Form.FilterbyYear.Value & "." & vbNewLine & vbNewLine & _
            "Thank you." & vbNewLine & vbNewLine & _
            "Regards," & vbNewLine & _
            UserNames
Else:
    Subjectline = "F&A OE - " & Subjectline & " - " & "Inception till date"
    Bodyline = "Hi Team," & vbNewLine & vbNewLine & _
            "Hope you are doing good and safe." & vbNewLine & vbNewLine & _
            "Attached is the " & s & " - Report - Inception till date." & vbNewLine & vbNewLine & _
            "Thank you." & vbNewLine & vbNewLine & _
            "Regards," & vbNewLine & _
            UserNames
End If

DoCmd.OutputTo acReport, Rptname, acFormatPDF, fileName, False

'Email the results of the report generated
Set oEmail = oApp.CreateItem(olMailItem)
With oEmail
    .Recipients.Add Me.txt_MailIDs
    .Subject = Subjectline 'Me.txt_MailSubject 'Trim(Mid(Rptname, 5, 100)) & " Report - Dated - " & Format(Date, "MM/DD/YYYY")
    .Body = Bodyline
    .Attachments.Add fileName 'I'M FACING ISSUE HERE'
    '.Send
    .display
End With

MsgBox "Report has been successfully drafted. Request you to please check and send." & vbNewLine & vbNewLine & "Thank you", vbInformation + vbOKOnly, "EMAIL STATUS"
'DoCmd.Close acForm, Me.Name


End Sub

Please help.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:29
Joined
Feb 28, 2001
Messages
27,001
The first thing to do is verify that your variable FILENAME on that attachments line is in fact what you want. Put a breakpoint on that line of code, then open the Immediate window when it breaks. Use DEBUG.PRINT FILENAME to see what file you are actually telling it to attach.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 20:29
Joined
Sep 12, 2006
Messages
15,614
.Attachments.Add fileName 'I'M FACING ISSUE HERE'

you need the full path and file name. You can't assume you can use the project folder, for instance.
note that you can't rename the attachment either. It will show as the filename from the folder, without the path.

try adding this somewhere
application.followhyperlink filename

that will show you if there are any issues with the file.
 

mrk777

Member
Local time
Tomorrow, 01:59
Joined
Sep 1, 2020
Messages
60
I don't know for some reason, it is now working in my system and when I try the same in the other system I'm getting the below error.

1615991848615.png
 

mrk777

Member
Local time
Tomorrow, 01:59
Joined
Sep 1, 2020
Messages
60
you need the full path and file name. You can't assume you can use the project folder, for instance.
note that you can't rename the attachment either. It will show as the filename from the folder, without the path.

try adding this somewhere
application.followhyperlink filename

that will show you if there are any issues with the file.
I have tried this as well. I end up getting the below error:
1615992274122.png
 

Minty

AWF VIP
Local time
Today, 20:29
Joined
Jul 26, 2013
Messages
10,355
As @gemma-the-husky Suggested try using a specific full file path rather than relying on wherever Access decides to plonk the stored PDF.
 

mrk777

Member
Local time
Tomorrow, 01:59
Joined
Sep 1, 2020
Messages
60
As @gemma-the-husky Suggested try using a specific full file path rather than relying on wherever Access decides to plonk the stored PDF.
I'm not picking the file from any location/path, I'm trying to attach the report which is existing in the Current DB file.
 

Minty

AWF VIP
Local time
Today, 20:29
Joined
Jul 26, 2013
Messages
10,355
I'm not picking the file from any location/path, I'm trying to attach the report which is existing in the Current DB file.
That was exactly my point, force it to a specific place that you are in control of.
You aren't specifying the complete path anywhere.

Code:
Dim strPath as String

strPath = CurrentApplication.Path & "\"

....
Rest of your code
....

DoCmd.OutputTo acReport, Rptname, acFormatPDF, strPath & fileName, False

....
More of your Code
....

  .Attachments.Add  strPath & fileName
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 20:29
Joined
Sep 12, 2006
Messages
15,614
As @Minty demonstrated - to add an attachment to an email, you have to create a disk file that you want to attach, and then attach that disk file.
 

mrk777

Member
Local time
Tomorrow, 01:59
Joined
Sep 1, 2020
Messages
60
That was exactly my point, force it to a specific place that you are in control of.
You aren't specifying the complete path anywhere.

Code:
Dim strPath as String

strPath = CurrentApplication.Path & "\"

....
Rest of your code
....

DoCmd.OutputTo acReport, Rptname, acFormatPDF, strPath & fileName, False

....
More of your Code
....

  .Attachments.Add  strPath & fileName
I have tried this on all my systems @Minty, it's working perfectly. But it's creating the report in that folder. Is there any way that we can attach the report, but not create the report in the specific path/folder?
 

Minty

AWF VIP
Local time
Today, 20:29
Joined
Jul 26, 2013
Messages
10,355
No - If you think about it - it's a File attachment, you have to create it, to be able to attach it.
You can easily delete the file after the email is sent?
 

mrk777

Member
Local time
Tomorrow, 01:59
Joined
Sep 1, 2020
Messages
60
No - If you think about it - it's a File attachment, you have to create it, to be able to attach it.
You can easily delete the file after the email is sent?
As of now, it is not that necessary, if it is needed, I'll try.

Thank you so much for the help. :)
 

Minty

AWF VIP
Local time
Today, 20:29
Joined
Jul 26, 2013
Messages
10,355
You are welcome, we are all glad to have helped.
Good luck with the rest of your project.

BTW - should you want to delete it simply add

Kill StrPath & Filename
 

mrk777

Member
Local time
Tomorrow, 01:59
Joined
Sep 1, 2020
Messages
60
You are welcome, we are all glad to have helped.
Good luck with the rest of your project.

BTW - should you want to delete it simply add

Kill StrPath & Filename
Sure, I’ll try this... thank you 😊
 

Users who are viewing this thread

Top Bottom