Attempting to Attach PDF file in Excel and Email but keep getting Error

I have the following VBA Code where I'm trying to attach a PDF file via Excel and everything runs fine until it gets to the .Attachments.Add line of code.

I'm getting the following error:

   Run-time error '-2147024894 (80070002)':
   Automation error
   The system cannot find the file specified.

But if I F8 (Step) through the code, the path/filename is correct.

Any clues?????

Thanks,

gdunn59

VBA Code:
Private Sub cmdEmailReport_Click()
Dim strPrevMnth As String
Dim strCurrMnth As String
Dim strPathName As String
Dim strRptName As String
Dim strEMRecipients As String
Dim strEMCCRecipients As String
Dim strEMSubject As String
Dim strEMBody As String
Dim strEMAttach As String

'Dates for Report Name
strPrevMnth = Format(DateSerial(Year(Date), Month(Now), 0), "MMM DD, YYYY")
strCurrMnth = Format(Now() - 1, "MMM DD, YYYY")


'Email Recipients
strEMRecipients = "gdunn59@abc.com"
strEMCCRecipients = "gdunn59@abc.com"

'Email Subject
strEMSubject = "Attorney Monthly Hours Report"

'Email Body
strEMBody = "Please find attached the Attorney Monthly Hours Report as of 3/8/2016."

'Path and Report Name
'strPathName = "H:\NRTEcho\ACTIVE\gdunn59\Atty Monthly Hours Report (WIP - Automate Email)\"
strPathName = "H:\NRTEcho\ACTIVE\gdunn59\"

strRptName = "Attorney Fiscal YTD Hrs Report Through " & strPrevMnth & " as of " & strCurrMnth & " - Full" & ".pdf"
'strRptName = "Attorney Fiscal YTD Hrs Report Through " & strPrevMnth & " as of " & strCurrMnth & " - Full"

'Report
strEMAttach = strPathName & strRptName

'Send Report via Email
Set Mail_Object = CreateObject("Outlook.Application")
Set Mail_Single = Mail_Object.CreateItem(0)
With Mail_Single
 .Subject = strEMSubject
 .To = strEMRecipients
 .cc = strEMCCRecipients
 .BCC = ""
 .Body = strEMBody
 .Attachments.Add (strEMAttach)
 
 .send
 End With
debugs:
'If Err.Description "" Then MsgBox Err.Description
End Sub

Open in new window

LVL 1
gdunn59Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick67Commented:
First see if it exists

Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(strEMAttach) Then
    .Attachments.Add (strEMAttach)
End if


That looks after the error.
Then, if you don't get an attachment, you'll need to explore why the attachment file is not found to exist -- timing, still locked, exclusive access etc.
gdunn59Author Commented:
Nick67,

I'm at least not getting the error now.  It sends the email, which it was doing before but it is not attaching the file.

When I look at the folder where the file is located, it doesn't show the extension (.pdf) in the folder.  Does that matter?

Although if I right-click on the file and look at the properties, it shows it's a PDF.

I even made a copy of another excel file and renamed it to Text.xlsx, and put that in my code as the filename, and it still just skips over the .Attachments.Add (strEMAttach) line of code.

Thanks,

gdunn59

Here is the Code:
Option Explicit
Private Sub cmdEmailReport_Click()
Dim Mail_Object As Object
Dim Mail_Single As Object
Dim strLastDayPrevMnth As String
Dim strCurrDate As String
Dim strPathName As String
Dim strRptName As String
Dim strEMRecipients As String
Dim strEMCCRecipients As String
Dim strEMSubject As String
Dim strEMBody As String
Dim strEMAttach As String

'Dates for Report Name
strLastDayPrevMnth = Format(DateSerial(Year(Date), Month(Now), 0), "MMM DD, YYYY")
strCurrDate = Format(Now() - 1, "MMM DD, YYYY")

'Email Recipients
strEMRecipients = "groper@bhfs.com"
strEMCCRecipients = "groper@bhfs.com"

'Email Subject
strEMSubject = "Attorney Monthly Hours Report"

'Email Body
strEMBody = "Please find attached the Attorney Monthly Hours Report as of 3/8/2016." & vbCrLf & vbCrLf & "Thanks," & vbCrLf & vbCrLf & "Gina Roper"

'Path and Report Name
'strPathName = "H:\NRTEcho\ACTIVE\GROPER\Atty Monthly Hours Report (WIP - Automate Email)\"
strPathName = "H:\NRTEcho\ACTIVE\GROPER\"

strRptName = "Attorney Fiscal YTD Hrs Report Through " & strLastDayPrevMnth & " as of " & strCurrDate & " - Full" & ".pdf"
'strRptName = "Attorney Fiscal YTD Hrs Report Through " & strLastDayPrevMnth & " as of " & strCurrDate & " - Full"

'Report
'strEMAttach = strPathName & strRptName
strEMAttach = "H:\NRTEcho\ACTIVE\GROPER\Attorney Fiscal YTD Hrs Report Through " & strLastDayPrevMnth & " as of " & strCurrDate & " - Full" & ".pdf"

'Send Report via Email
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
Set Mail_Object = CreateObject("Outlook.Application")
Set Mail_Single = Mail_Object.CreateItem(0)
With Mail_Single
 .Subject = strEMSubject
 .To = strEMRecipients
 .cc = strEMCCRecipients
 .BCC = ""
 .Body = strEMBody

If fs.FileExists(strEMAttach) Then
   .attachments.Add (strEMAttach)
End If

 .send
 
End With

End Sub

Open in new window

Nick67Commented:
When I look at the folder where the file is located, it doesn't show the extension (.pdf) in the folder.  Does that matter?

The real, actual filename will matter greatly.
if you have a file named
c:\temp\test
that is a text file but missing the extension
and you attempt to attach this path
"c:\temp\test.txt"
It will fail because that path does not exist

But I think you have a syntax issue
In my code I have
Dim objOutlookAttach As Outlook.Attachment
and then later I have
Set objOutlookAttach = .Attachments.Add(AttachmentPath)

You are late binding, so try
Dim objOutlookAttach As object

and then

If fs.FileExists(strEMAttach) Then
     Set objOutlookAttach =   .attachments.Add (strEMAttach)
End If
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

gdunn59Author Commented:
Nick,

I tried your last solution, and it still will not attach the attachment.

I'm not getting any errors, it just skips over the line of code:  Set objOutlookAttach =   .attachments.Add (strEMAttach)

I've tried changing the path where the file is, adding the extensions so they show in the folder.  I'm at a loss as to why it won't attach the file.

Do you have any more clues/options????

Thanks,

gdunn59
Nick67Commented:
it just skips over the line of code:
Which means that the path embodied in strEMAttach does not represent a valid path and file name.
fs.FileExists(strEMAttach)  is coming back false

Msgbox it to confirm that
Msgbox fs.FileExists(strEMAttach)

If it comes back false, let's give it a file with a simple name we KNOW exists
Create the following file
c:\temp\test.txt
Then make strEMAttach = "c:\temp\test.txt" just before the FileExists call.
Run the code and see if it attaches.

If it does, then the code that creates strEMAttach is not creating the string you think it is.
If it doesn't then something else is going on.
gdunn59Author Commented:
Nick67,

I've Stepped through the Code and checked the variable strEMAttach and it is exactly what it should be.

Attached is a sample of the spreadsheet which contains an Email Report button.

Here is the Code behind the Email Report button:
Option Explicit
Sub cmdEmailReport_Click()
Dim Mail_Object As Object
Dim Mail_Single As Object
Dim strLastDayPrevMnth As String
Dim strCurrDate As String
Dim strPathName As String
Dim strRptName As String
Dim strEMRecipients As String
Dim strEMCCRecipients As String
Dim strEMSubject As String
Dim strEMBody As String
Dim strEMAttach As String

'Dates for Report Name
strLastDayPrevMnth = Format(DateSerial(Year(Date), Month(Now), 0), "MMM DD, YYYY")
strCurrDate = Format(Now() - 2, "MMM DD, YYYY")

'Email Recipients
strEMRecipients = "gdunn59@bhfs.com"
strEMCCRecipients = "gdunn59@bhfs.com"

'Email Subject
strEMSubject = "Attorney Monthly Hours Report"

'Email Body
strEMBody = "Please find attached the Attorney Monthly Hours Report as of 3/8/2016." & vbCrLf & vbCrLf & "Thanks," & vbCrLf & vbCrLf & "Jane"

'Path and Report Name
'strPathName = "H:\NRTEcho\ACTIVE\gdunn59\Atty Monthly Hours Report (WIP - Automate Email)\"
strPathName = "U:\DN\Accounting\Reports\KT\Atty Monthly Hours Report\201602 Atty Hrs Report\"

'strRptName = "Attorney Fiscal YTD Hrs Report Through " & strLastDayPrevMnth & " as of " & strCurrDate & " - Full" & ".pdf"
strRptName = "Attorney Fiscal YTD Hrs Report Through " & strLastDayPrevMnth & " as of " & strCurrDate & " - Full"

'Report
strEMAttach = strPathName & strRptName
'strEMAttach = "U:\DN\Accounting\Reports\Kevin\Atty Monthly Hours Report\201602 Atty Hrs Report\Attorney Fiscal YTD Hrs Report Through " & strLastDayPrevMnth & " as of " & strCurrDate & " - Full" & ".pdf"

'Send Report via Email
Dim objOutlookAttach As Object
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
Set Mail_Object = CreateObject("Outlook.Application")
Set Mail_Single = Mail_Object.CreateItem(0)
With Mail_Single
 .Subject = strEMSubject
 .To = strEMRecipients
 .cc = strEMCCRecipients
 .BCC = ""
 .Body = strEMBody

If fs.FileExists(strEMAttach) Then
   .attachments.Add (strEMAttach)
End If

'If fs.FileExists(strPathName) Then
'      Set objOutlookAttach = .attachments.Add(strEMAttach)
' End If
 
 .send
 
End With

End Sub

Open in new window

Test_EE--as-of-3-11-2016----Email.xlsx
Nick67Commented:
This code worked to create and send a dummy text file
:) Which you may get by email shortly!

Option Explicit
Sub cmdEmailReport_Click()
Dim Mail_Object As Object
Dim Mail_Single As Object
Dim strLastDayPrevMnth As String
Dim strCurrDate As String
Dim strPathName As String
Dim strRptName As String
Dim strEMRecipients As String
Dim strEMCCRecipients As String
Dim strEMSubject As String
Dim strEMBody As String
Dim strEMAttach As String

'Dates for Report Name
strLastDayPrevMnth = Format(DateSerial(Year(Date), Month(Now), 0), "MMM DD, YYYY")
strCurrDate = Format(Now() - 2, "MMM DD, YYYY")

'Email Recipients
strEMRecipients = "gdunn59@bhfs.com"
strEMCCRecipients = "gdunn59@bhfs.com"

'Email Subject
strEMSubject = "Attorney Monthly Hours Report"

'Email Body
strEMBody = "Please find attached the Attorney Monthly Hours Report as of 3/8/2016." & vbCrLf & vbCrLf & "Thanks," & vbCrLf & vbCrLf & "Jane"

'Path and Report Name
'strPathName = "H:\NRTEcho\ACTIVE\gdunn59\Atty Monthly Hours Report (WIP - Automate Email)\"
strPathName = "U:\DN\Accounting\Reports\KT\Atty Monthly Hours Report\201602 Atty Hrs Report\"

'strRptName = "Attorney Fiscal YTD Hrs Report Through " & strLastDayPrevMnth & " as of " & strCurrDate & " - Full" & ".pdf"
strRptName = "Attorney Fiscal YTD Hrs Report Through " & strLastDayPrevMnth & " as of " & strCurrDate & " - Full.txt"

'Report
strEMAttach = strPathName & strRptName
'strEMAttach = "U:\DN\Accounting\Reports\Kevin\Atty Monthly Hours Report\201602 Atty Hrs Report\Attorney Fiscal YTD Hrs Report Through " & strLastDayPrevMnth & " as of " & strCurrDate & " - Full" & ".pdf"

MsgBox strEMAttach


'Send Report via Email
'prep the attachment
Dim objOutlookAttach As Object

'filesystemobject
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
'create a dummy file
Dim oFile As Object
Set oFile = fs.CreateTextFile(strEMAttach)
oFile.WriteLine "test"
oFile.Close
Set oFile = Nothing
'dummy created

Set Mail_Object = CreateObject("Outlook.Application")
Set Mail_Single = Mail_Object.CreateItem(0)
With Mail_Single
 .Subject = strEMSubject
 .To = strEMRecipients
 .cc = strEMCCRecipients
 .BCC = ""
 .Body = strEMBody
 'see if the attachment exists
  If fs.FileExists(strEMAttach) Then
      Set objOutlookAttach = .attachments.Add(strEMAttach)
  Else
      MsgBox "A file at the path " & vbCrLf & strEMAttach & vbCrLf & " was not found"
  End If
 .send

End With

End Sub

Open in new window


Your code was trying to attach a file without a suffix (ie .pdf)
It may not like that.
Ensure that tell-me-no-lies is in place
no liesand ensure that your file has an extension.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gdunn59Author Commented:
Nick,

I tried your latest posting with the dummy test file and email and it worked.  So then I changed everything back to my actual files (PDF), and it didn't work again.

I feel so stupid.  I finally figured out what the issue was.

This line of code was doing the date format with a 2 digit day, i.e. 08, when the file actually only has 1 digit for the day, i.e. 8.

strCurrDate = Format(Now() - 3, "MMM DD, YYYY")

I changed that line of code to format with only digit for the day:

strCurrDate = Format(Now() - 3, "MMM D, YYYY")

Now it works like a charm.  I looked at it over and over and just didn't see it before.

Sorry about that, but thank you for all your time and persistence.

I will award you the points based off of your last posting, since that is what triggered everything for me to see the issue.

Thanks again,

gdunn59
Nick67Commented:
Good enough!
Sometimes the messages are tough to figure out, but when
fs.FileExists(SomePath)
comes back false, well it really does mean that SomePath isn't right

And until you test for existence, you have that bomb waiting there.
So it's all good.
Your code is now more robust, and it works.

What happens if the code runs on the 13th or later though?
strCurrDate = Format(Now() - 3, "MMM D, YYYY")
Will that return Mar 0, 2016; Mar 1, 2016; Mar 10, 2016,  or blow up?

Me, I'm partial to dd-mmm-yyyy
No one and nothing in the English speaking world ever gets that wrong.

Have a good weekend.

Nick67
gdunn59Author Commented:
Nick67,

As far as if the code runs on the 13th or later, I just put in that -3 to get the file for the Mar 8th that already existed.  In the future when it really happens, it will automatically pick up the current date for that variable, which is what I need.

Also, I did make a copy of the PDF and named it as follows, and it worked fine with the 1 digit format even though the date in the report was 2 digits:

Attorney Fiscal YTD Hrs Report Through Feb 29, 2016 as of Mar 11, 2016 - Full.pdf

Thanks,

gdunn59
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.