Help with code to publish a PDF after creating (VBA)

[whirlwind147]

Hi,

I have the following code in an excel spreadsheet which when a button is clicked it attaches the range to an email. I don't want the email to be sent without the sender viewing it but I would like the PDF that is attached to open automatically so the user can check it before sending it as I've had people send the wrong things in the past. If I change the false to true after the OpenAfterPublish section it doesn't work at all. Can somebody tell me what I have to do to make this work?
Thanks in advance!
Graham

Sub mcrPDFQuote()
'Code to create PDF file
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
        "C:\Quote\" & Range("a742") & Range("a743") & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties _
        :=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
       
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strTo As Range
    Dim strSubject As Range
    Dim strBody As Range
    Dim wkSht As Worksheet
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    Set strTo = Range("a744")
    Set strSubject = Range("a745")
    Set strBody = Range("a740")
        
    On Error Resume Next
    With OutMail
        .To = strTo
        .CC = ""
        .BCC = ""
        .Subject = strSubject
        .Body = vbNewLine & Range("a740") & vbNewLine & Range("a741")
        .Attachments.Add strPath & Range("a742") & Range("A743") & ".pdf"
        .display 'Change to send to go without checking
       
    End With
    On Error GoTo 0
 
    Set OutMail = Nothing
    Set OutApp = Nothing
        Kill strPath & Range("a742") & Range("a743") & ".pdf"
End Sub

 

[daverunt]

Hi

I'm using Excel 2013 with Adobe Reader X1 installed and just the first bit of code and it works for me - opening the PDF if set to True.

 

[whirlwind147]

Hi and thanks for your reply.
I did some testing a while back and I couldn't have had some fields filled in because it didn't work. I changed it to true as well just now and it does open up the pdf but because the file is open the kill line on the second to last line of the code doesn't work. I get an error box come up the says "Run-time error '70': Permission Denied" and when I click Debug it highlights that kill line.
I assume because I have the kill line in there I won't be able to view the file because it can't get deleted as it's open and in use? Or is there a workaround that you know of?

 

[daverunt]

I don't know an easy way to close a pdf file once open... maybe someone on the forum does.

You could pop up a message box just instructing the user to close the pdf file before pressing the MsgBox OK button and allowing the macro to proceed. Or use a delay.