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,

in a previous post of brianjmclean there is a QVD file as example (reportexporter.qvd).

If you could change that file would be perfect!

thanks a lot

regards

[#3]

I really can't get it to work, it must be of the lacking registry settings.

Does anyone know how to solve it? (maybe put them there manualy?)

[#4]

Hi,

first of all thanks for your post.

there a just one more thing left not done.

i had test Dagrapport macro, but its stop at line "ActiveDocument.PrintReport(vReport), "Bullzip PDF Printer", false 'Printreport"

and no error inf send back.

BullzipPDF is already installed but cant download GhostScript self ast last,i download a other GhostScript self.

and it is 32 bit machine

GhostScript , version is 9.0

BullzipPDF , version is 8.2（i guess....）

Anyone got any ideas?

Thanks for your help !

one more question

code said: vReport = "QVreportname" 'Set report

QVreportname, this parameters its qv repot title or ID?

its really can print a report to pdf?

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

[#5]

vReport is the QlikView-Name of the report (Like: RP01, RP02,...)

[#6]

thanks jochenandres

but how to make it work?

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

stop at this line when i run the macro, and not error info bake to me.

[#7]

Hi,

I think it just works with the old qv-pdf-printer i mentioned above. i also tried the bullzip and didn't get it running.

cheers,

martin

[chris_johnson]

Hi,

Not trying to sound like a salesperson but there is always a product called NPrinting (www.nprinting.com) which will do all of this and a lot more, QlikView reports out to PDF, Excel and image files, scheduling of reloads, distribution of reports based on criteria, user filtering..........

Obviously though you would have to pay for it but it's a balance between how much time you want to invest into doing it yourself and buying something that does it already. Just thought it might be worth a look for people who wanted a fully featured product over having to develop and maintain something themselves.

Regards,

Chris

[#9]

hi Em Vau

i tested your code ,  installation the QlikViewPDF also.

and set this before print

Set WSHNetwork = CreateObject("WScript.Network")

    WSHNetwork.SetDefaultPrinter "QlikViewPDF"

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

whatever in second parameter  even null

im useing  QV10

really make me crazy .......

[#10]

Hi,

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

you should the name of the installed PDF Printer instead of the Bullzip PDF Printer,e.g.

ActiveDocument.PrintReport(vReport), "QlikViewPDF", false (depending on the name of the printer in your printer overview)

Cheers,

Martin

[#11]

thank you Em Vau

i instead thos both in my company PC. and tested almost all kind of code haha.....

much restrict on company PC  i think.....

so , i will try more times on my own pc.

thanks for your help

wait for my good news