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 Em Vau

im not success......

tested in QV10 and 11, all the same problem...stop at ..ActiveDocument.PrintReport(vReport), "QlikViewPDF", false

the attachment is my example.

Thanks for your Help.

[#3]

Hi,

in your example is no report, did you forget it or might this be the reason?

Martin

[#4]

hi.

   yes, i find out point from other people example.

  our people here, call the charts is report  too...

  so i always put charts name on the  parameters,thats why the code stop there all the time....

  Now,  the code work fine.

  Thanks everyone, thanks for your help.

[#5]

Works on XP 32bit, but when you step to 64-bit server I get this error:

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

Can you help me please

Juan

[#6]

Hi Em Vau

I use QV11 on Windows 7 64bit OS.I installed QlikViewPDF printer

I copy pasted your code in macro, whose execution stops at

Set WSHNetwork = CreateObject("WScript.Network")

Just to clarify, I am not on any office network. What is wrong with the statement?

[#7]

Hi Em Vau,Please treat my point as closed. Thanks - KSP

I

[#8]

I use a Windows Server 2008 R2 Enterprise SP1 64-bit

someone please help me configure

Regards

Juan

[rfigueroa]

Hi all, I've found the solution for 64 bits systems, it turns out that Bullzip have changed the ProgID in their COM object:

In MyPrintPDFWithBullZip change the line

set obj = CreateObject("Bullzip.PDFPrinterSettings")

for:

set obj = CreateObject("Bullzip.PdfSettings")

And you must add the next line:

obj.PrinterName = YourPrinterNameOnWindows

That's all.

I have this working on Windows server 2011 x64 & QlikView Server v11.0.11291 x64

Printer Driver is: Bullzip PDF Printer 9.0.0.1437

Bullzip PDF COM Object Help: http://www.biopdf.com/guide/pdf_api.php

BTW, thank you Jochen for such piece of code!!!

HPH

Roberto.

[#10]

Thanks for your answer.

Remember, use the Report ID (NOT Report Name) in:

ActiveDocument.PrintReport(vReport), "Bullzip PDF Printer", false

A greeting.

[#11]

Thanks a lot for this post.

It works really well (almost the printing function, I have not try to send e-mails yet).

I install the old QV pdf printers and there is no more problems.

Tanks