Step out of the dark – automatically generate and send PDF reports
As you can imagine, being able to produce PDF reports of your shiny new Qlikview dashboards is very important. After all, you want to be able to share the insights that you find with a large group of people. But what happens if each of those people needs to see a different report? Do you manually apply filters on your dashboard, manually pull the PDF and manually email it to the recipients?
If the answer is yes, then you’re probably finding the process tedious, time consuming and generally very boring. But, what can you do? Well, there are plenty of ways to automate the production and processing of PDF files, such as the NPrinting service available from Qlik – but that service is very expensive, so what’s the alternative?
Well, this paves the way for my next challenge in Qlikview. I am working on a macro to automate the production of PDF reports and the subsequent email of those reports. I have been working from various sources to make this possible, including the Qlikview community. As yet, I have not found a code that works consistently, however, by merging a few of the examples that I have seen on the community, I seem to have generated a bit of code that works some of the time – I have yet to figure out why it doesn’t work every time, but it’s certainly a good start.
My latest output is below – as I work through the problems, I will update my post with the latest, working code.
vReport = "Report1"
vName = "Report1"
ActiveDocument.PrintReport(vReport), "Bullzip PDF Printer", false
reportFile = "reportpath" & vName &".pdf"
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"
Const cdoSendUsingPort = 2
Const cdoAnonymous = 0
Const cdoBasic = 1
Const cdoNTLM = 2
SMTPServer = "smtp.gmail.com"
Const SMTPPort = 465
Const SMTPTimeout = 60
Set objEmail = CreateObject("CDO.Message")
Set objConf = objEmail.Configuration
Set objFlds = objConf.Fields
Set objMsg = CreateObject("CDO.Message")
Set msgConf = CreateObject("CDO.Configuration")
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "emailaddress"
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = 1
objMsg.To = "recepient"
objMsg.From = "sender"
objMsg.Subject = "Test send with Gmail account"
objMsg.HTMLBody = "HTML/Plain text message."
objMsg.Sender = "Mr. Name"
Set objMsg.Configuration = msgConf
Set objMsg = nothing
Set msgConf = nothing
' msgbox ("Test Mail Sent")
Image used under creative commons