Macro to export AND mail report as PDF

[#1]

2 days... that's what took me to get it right !! Here's how I did it :

1. Install Bullzip PDF Printer (I tried CutePDF, PDF X-change, PdfCreator,.. bullzip is the only one that works)

2. Macro :

Sub Dagrapport
vReport = "QVreportname" 'Set report
vName = "reportname" 'Name of output pdf
ActiveDocument.PrintReport(vReport), "Bullzip PDF Printer", false 'Printreport
reportFile = "reportpath" & vName &".pdf" 'Setting outputname
MyPrintPDFWithBullZip(reportFile) 'Call pdf printer
ActiveDocument.GetApplication.Sleep 5000
zendDagrapport
End sub
FUNCTION MyPrintPDFWithBullZip (pdfOutputFile)
set obj = CreateObject("Bullzip.PDFPrinterSettings")
obj.SetValue "Output" , pdfOutputFile
obj.SetValue "ConfirmOverwrite", "no"
obj.SetValue "ShowSaveAS", "never"
obj.SetValue "ShowSettings", "never"
obj.SetValue "ShowPDF", "no"
obj.SetValue "RememberLastFileName", "no"
obj.SetValue "RememberLastFolderName", "no"
obj.SetValue "ShowProgressFinished", "no"
obj.SetValue "ShowProgress", "no"
obj.WriteSettings True
END FUNCTION
'===========================================================================
'===========================================================================
function zendDagrapport()
Dim objEmail
Const cdoSendUsingPort = 2 ' Send the message using SMTP
Const cdoAnonymous = 0 'Do not authenticate
Const cdoBasic = 1 'basic (clear-text) authentication
Const cdoNTLM = 2 'NTLM
SMTPServer = "IP Mailserver"
Const SMTPPort = 25 ' Port number for SMTP
Const SMTPTimeout = 60 ' Timeout for SMTP in seconds
'Sending mail
Set objEmail = CreateObject("CDO.Message")
Set objConf = objEmail.Configuration
Set objFlds = objConf.Fields
With objFlds
'---------------------------------------------------------------------
' SMTP server details
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoAnonymous
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = SMTPPort
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = SMTPTimeout
.Update
'---------------------------------------------------------------------
End With
objEmail.To = "to emailaddress" 'Email Recipient
objEmail.From = "from emailaddress" 'Email Sender
objEmail.Subject = "emailsubject" ' Subject
objEmail.TextBody = "emailbody" 'Text Body
objEmail.AddAttachment "link to file" ' Attachement
objEmail.Send
Set objFlds = Nothing
Set objConf = Nothing
Set objEmail = Nothing
' msgbox ("Test Mail Sent")
end function

I don't know how to post code in colour...
But it WORKS !!!!

My next step :

Make it work, so I have nothing to do anymore ! (I think I will use JIT and the commandline to open the QVW and execute the macro)

[#2]

Hi,

first of all thanks for your post.

I was trying that but the macro stops at the command "set obj = CreateObject("Bullzip.PDFPrinterSettings")" with the message "ActiveX component can't create object: 'Bullzip.PDFPrinterSettings'". My security level is set on "Allow system access", the machine is a Win2008R2 64bit.

Whyever the pdf-file nevertheless gets created (which doesn't help when the macro stops 🙂 ), even though with the default name and not the defined one.

Can you give me a hint?

Thanks,

Martin

[#3]

Em Vau

I'm havng a similar issue on WIn Server.

Anyone got any ideas?

Brian

[Anonymous]

Hi Everybody,

Unfortunately, i've got the same problem, the macro stops at the command "set obj = CreateObject("Bullzip.PDFPrinterSettings")" with the message "ActiveX component can't create object: 'Bullzip.PDFPrinterSettings'". My security level is set on "Allow system access", the machine is a Win7 64bit.

Thanks for your help !

[#5]

Hi guys,

sorry that it took me that long to answer. QV has indeed some issues with printing pdf on 64bit. a colleague of mine found a solution, based on previous knowledge from the community and some new combinations.

Shortly: it works with the OLD version of the pdfprinter which qliktech offers for download.

Before the whole printing starts:

    Set WSHNetwork = CreateObject("WScript.Network")

    WSHNetwork.SetDefaultPrinter "QlikViewPDF"

and later just use this function:

function printReportPDF(reportID, pdfOutputFile)

    Set WSHShell = CreateObject("WScript.Shell")

     WSHShell.RegWrite "HKCU\Software\QlikViewPDF\OutputFile", pdfOutputFile, "REG_SZ"

     WSHShell.RegWrite "HKCU\Software\QlikViewPDF\BypassSaveAs", "1", "REG_SZ"

    ActiveDocument.PrintDocReport reportID

    set WSHShell = nothing   

end function

That's it. Just remember to  use the OOOOOOLD Qlikview PDF Printer.

Martin

[#6]

Here we go... that's the printer that works.

[Anonymous]

So good !! Works Perfect.. Thanks to all.

Here is my code for those who need....:

Sub Dagrapport

Periode = ActiveDocument.GetVariable("vMoisSelection").GetContent.String

Set WSHNetwork = CreateObject("WScript.Network")

WSHNetwork.SetDefaultPrinter "QlikViewPDF"

vReport = "RP01" 'Set report

vName = "Expenses Per Department - "& Periode 'Name of output pdf

ActiveDocument.PrintReport(vReport), "QlikViewPDF", false 'Printreport

reportFile = "c:\temp\" & vName &".pdf" 'Setting outputname

printReportPDF vReport,reportFile

ActiveDocument.GetApplication.Sleep 5000

zendDagrapport reportFile

End sub

'===========================================================================

FUNCTION printReportPDF(reportID, pdfOutputFile)

    Set WSHShell = CreateObject("WScript.Shell")

     WSHShell.RegWrite "HKCU\Software\QlikViewPDF\OutputFile", pdfOutputFile, "REG_SZ"

     WSHShell.RegWrite "HKCU\Software\QlikViewPDF\BypassSaveAs", "1", "REG_SZ"

    ActiveDocument.PrintDocReport reportID

    set WSHShell = nothing   

END FUNCTION

'===========================================================================

function zendDagrapport(PDFOutputFile)

Dim objEmail

Const cdoSendUsingPort = 2 ' Send the message using SMTP

Const cdoAnonymous = 0 'Do not authenticate

Const cdoBasic = 1 'basic (clear-text) authentication

Const cdoNTLM = 2 'NTLM

SMTPServer = "IP mail Server"

Const SMTPPort = 25 ' Port number for SMTP

Const SMTPTimeout = 60 ' Timeout for SMTP in seconds

'Sending mail

Set objEmail = CreateObject("CDO.Message")

Set objConf = objEmail.Configuration

Set objFlds = objConf.Fields

With objFlds

'---------------------------------------------------------------------

' SMTP server details

.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort

.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer

.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoAnonymous

.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = SMTPPort

.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False

.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = SMTPTimeout

.Update

'---------------------------------------------------------------------

End With

MailResponsable = ActiveDocument.GetVariable("vResponsableDepartement").GetContent.String

objEmail.To = MailResponsable 'Email Recipient

objEmail.From = "qlikview@Company.fr" 'Email Sender

objEmail.Subject = "Expenses Monthly Report" ' Subject

objEmail.TextBody = "Please find enclosed the Expenses Monthly Report for your Department " 'Text Body

objEmail.AddAttachment PDFOutputFile ' Attachement

objEmail.Send

Set objFlds = Nothing

Set objConf = Nothing

Set objEmail = Nothing

' msgbox ("Test Mail Sent")

end function

[#8]

First of all thanks for the terrefic post! After implementing the script I ran into the following problem. Instead of mailing the reports automatically I want to give the users the possibility to choose a report and mail adress and hit a button to send the report. I've got this working fine within in the local client.

However when I use the Ajax client the pdf printer seems to be blocked (maybe because it pops up). The normal report printing function (in the top function bar) works fine. Did anybody implement this printing function with the use of a button or can anybody get it to work? Thanks!

[#9]

Hi!

I've been trying do use this but i dont have the key "HKCU\Software\QlikViewPDF\"

Using windows 2003 R2 64bit.

Any idea? (I've downloaded the ooooold printer )

[#10]

Hi samson_t,

can you give me a file of example?

i'm using Win7 64 bit and QVPdf ...but don't work it

best regards

gda

[Anonymous]

Hi,

What kind of file do you want as example ?