Print to PDF and email from Excel

[kgkev]

I have created an invoice from a SQL data source and I plan to have it emailed as a PDF to the customer.

The bit I am stuck on is printing the PDF to a predefined location with a file name generated from the sheet.

Using Excel 2000 and attempting to do the job with CutePDF.

If I record the macro I get

    Sheets("invoice").Select
    Application.ActivePrinter = "CutePDF Writer on CPW2:"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
        "CutePDF Writer on CPW2:", Collate:=True

So this does not see anything that happens with in the CutePDF controls.


Any ideas on the best way of solving this one?

 

[Ruddles]

Have you tried Google? I just put in cutepdf vba and got 16,000 hits!

This suggests it's possible: http://www.excelforum.com/excel-programming/718392-print-with-cutepdf-to-variable-folder.html. You'd have to replace the fie lines which referred to Word with your Excel-specific lines.

If that's no good, could you allow CutePDF to put the file wherever it wanted, then move it using VBA to wherever you wanted it to be?

 

[kgkev]

Have you tried Google?

Could you not say that to every single poster on here?

I came to MrExcel.com because I respect the views and knowledge of the members here.

The post you refer to is over a year old. perhaps better options have surfaced since.

Currently using a sendkeys solution

ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
    "CutePDF Writer on CPW2:", Collate:=True

    Newhour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 2
    filename = Sheets("Data").Range("O3").Value
    filename = filename & " - " & Sheets("Data").Range("A1").Value
    filename = filename & " - " & Sheets("Data").Range("C3").Value
    filepath = "\\ih-serv\company\invoices\" & username & ".pdf"
    
    waittime = TimeSerial(Newhour, newMinute, newSecond)
    Application.Wait waittime

    SendKeys filepath & "{ENTER}", False
    Application.Wait waittime
    SendKeys "{ENTER}", False

However this triggers my AntiVirus. Might have to look at buying Office 2007 or Acrobat.

 

[Ruddles]

Could you not say that to every single poster on here?

I use it very sparingly, in fact, and it was a genuine question rather than a criticism, but it's nice to know you follow my posts so avidly! To be fair you didn't say you'd tried Google. Some people seem not to have heard of it and use the forum as their first resort. Apologies if that's not the case here.

The link I posted appeared to provide a solution in spite of its age - otherwise I wouldn't have posted it. Are you saying it doesn't work?

I did also suggest allowing CutePDF to put the file wherever it wanted, then moving it using VBA. Is that worth considering or does that not actually overcome your issue?