topic Macro to export AND mail report as PDF in QlikView

Macro to export AND mail report as PDF

Fri, 15 Oct 2010 13:37:14 GMT

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)

Re: Macro to export AND mail report as PDF

djallelsadki — Mon, 09 May 2011 13:31:51 GMT

Hello,

Very good work,

I want to ask you, how can i change the output file name into a variable values taht takes two or three fields,

var=field1&field2

i want this file name : var.pdf

can you help me ?

Macro to export AND mail report as PDF

Thu, 27 Oct 2011 11:43:00 GMT

Hi Jochenandries,

I was eagerly searching for Exporting the QVW file into PDF. And finally found it:) Thanks for making it.

But I need to revisit the code and make it suitable for my requirement. I want to ask one thing. If this script is written in Edit module. How we can trigger this by an external event.

My requirement:

1. Report should get refreshed in Qlikview enterprise mangement console (QEMC)

2. Export the QV sheets to a single PDF file.

3. Send the PDF file as an attachment to the receipents list.

Manual run of the macro will export and send the QV report output in PDF, But It has to be triggered automatically once the QV report is refreshed. How can we acheive this ?? Could you please throw some light on this,

Thanks

Vinod

Macro to export AND mail report as PDF

Thu, 08 Dec 2011 15:58:49 GMT

I don't use the QEMC...

I have it running to update the QV-database-files, but I don't use for my apps.

What I do :

In JIT (a task scheduler) I programmed a schedule to open the QlikView-file and execute 1 or more macro's at opening.

In the macro he reloads the document, makes the PDF's, mail them around, saves the document and closes QlikView.

It's a dirty solution, but it works, and it's cheap !!!

Macro to export AND mail report as PDF

Thu, 29 Dec 2011 04:41:02 GMT

I am getting error

ActiveX component can't create object: 'Bullzip.PDFPrinterSettings'

what is the reason

please help

Regards

Pramod Kumar

Macro to export AND mail report as PDF

Thu, 29 Dec 2011 05:57:11 GMT

First of all, is BullzipPDF installed ?

Second : Is it a 32- or 64-bit machine ?

Macro to export AND mail report as PDF

Thu, 29 Dec 2011 07:14:57 GMT

BullzipPDF is already installed

it is 32 bit machine

Macro to export AND mail report as PDF

Thu, 29 Dec 2011 07:23:21 GMT

On the left, are the security settings set to SYSTEM ?

Macro to export AND mail report as PDF

Thu, 29 Dec 2011 08:26:09 GMT

thanks jochenandres

Now , its working

Regards

Pramod Kumar

Macro to export AND mail report as PDF

Thu, 29 Dec 2011 08:27:11 GMT

You're welcome

Macro to export AND mail report as PDF

Fri, 27 Jan 2012 11:30:28 GMT

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

Re: Macro to export AND mail report as PDF

Thu, 09 Feb 2012 12:12:41 GMT

Em Vau

I'm havng a similar issue on WIn Server.

Anyone got any ideas?

Brian

Macro to export AND mail report as PDF

Anonymous — Wed, 15 Feb 2012 13:44:02 GMT

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 !

Macro to export AND mail report as PDF

Wed, 15 Feb 2012 13:54:41 GMT

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

Re: Macro to export AND mail report as PDF

Wed, 15 Feb 2012 14:00:44 GMT

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

Macro to export AND mail report as PDF

Anonymous — Thu, 16 Feb 2012 10:08:18 GMT

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

Macro to export AND mail report as PDF

Thu, 05 Apr 2012 08:18:23 GMT

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!

Macro to export AND mail report as PDF

Tue, 22 May 2012 14:59:09 GMT

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 )

Macro to export AND mail report as PDF

Mon, 28 May 2012 13:10:49 GMT

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

Macro to export AND mail report as PDF

Anonymous — Tue, 29 May 2012 12:15:10 GMT

Hi,

What kind of file do you want as example ?