VBA Print to PDF and Email - Debug

[03856me]

I have the following code written, the printing to a pdf file was working perfectly, then I added the email code. It works the first time but then I am getting the following error on the row underlined in purple. If I save and close the file, then go back in, it works again the 1st time only.

Run-time error '2147024894 (80070002)':
Cannot find this file. Verify the path and file name are correct
=================================================
Sub PrinttoPDF()
If Range("EffectiveDate") > "" Then
Answer = MsgBox("Do you want to complete p.o.? Yes will 1)post, 2)reset p.o.#, 3)print to pdf file", _
vbYesNo, "Complete")
If Answer = vbNo Then Exit Sub

ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"CutePDF Writer on CPW2:", Collate:=True
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
filename = "M:\POs All locations\" & ActiveSheet.Range("PrintName").Value & ".pdf"
SendKeys filename & "{Enter}", False

'=== EMAIL SECTION OF CODE ===
Dim Mail_Object, Mail_Single As Variant

Email_Subject = "See Attached NEW Purchase Order and Matrix"

nameList = Sheets("Template").Range("emailLinda").Value
Email_Send_To = nameList


Email_Cc = ""
Email_Bcc = ""
Email_Body = "Attach your MATRIX then SEND to Linda!"

Set Mail_Object = CreateObject("Outlook.Application")
Set Mail_Single = Mail_Object.CreateItem(o)

With Mail_Single
.Subject = Email_Subject
.To = Email_Send_To

.CC = Email_Cc
.BCC = Email_Bcc
.Body = Email_Body
.Attachments.Add ("m:\POs All locations\" & ActiveSheet.Range("PrintName").Value & ".pdf")
.Display
'.send
End With
' MsgBox "E-mail successfully sent"
Application.DisplayAlerts = False

'=== END OF EMAIL CODE ===

End If
End Sub
=========================================
thanks for your help

 

[Ruddles]

Immediately before the .Attachments.Add statement, add this line:-

MsgBox ActiveSheet.Name

This will tell you what the active sheet is before you try to get the filename out of it. You may find that it's correct the first time through but that the active sheet is changing after the first pass through the code.

 

[03856me]

Yes, thank you, that worked.

However, the message box comes up as expected with the sheetname and an "ok" button that the user is having to select to finish running the code. Is there a way to eliminate the required user action?

Or, another way to accomplish the same thing?

 

[Ruddles]

I'm sorry, I don't understand. The code I gave you merely displays the name of the active sheet on the screen each time you cycle around your code loop. You stated that your code worked first time through but not on subsequent loops. The MsgBox statement was merely an attempt to discover whether the cause of the problem was that the active sheet was changing for some reason after the first loop had been executed. It doesn't change the logic of your code in any way.

Okay, remove the MsgBox command and replace it with these three lines:-

Debug.Print Now()
Debug.Print "Sheet name = "; ActiveSheet.Name
Debug.Print "File name = "; "m:\POs All locations\" & ActiveSheet.Range("PrintName").Value & ".pdf"

The next time your code fails, you can check the contents of the Immediate window (Ctrl-G) for clues.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>