Solved Run Time Error while attaching the PDF to the Email

[mrk777]

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).

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]

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]

.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]

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.

 

[mrk777]

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:

 

[Minty]

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]

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]

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.

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]

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]

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.

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]

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]

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]

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]

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