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

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.

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

Select allOpen in new window

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

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

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.

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

Select allOpen in new window

Test_EE--as-of-3-11-2016----Email.xlsx

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

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

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