Step out of the dark – automatically generate and send PDF reports

By Business Intelligence
Qlikview automated reporting

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.

Sub Pullrpt
vReport = "Report1"
vName = "Report1"
ActiveDocument.PrintReport(vReport), "Bullzip PDF Printer", false
reportFile = "reportpath" & vName &".pdf"
ActiveDocument.GetApplication.Sleep 5000
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

function zendPullrpt()
Dim objEmail
Const cdoSendUsingPort = 2
Const cdoAnonymous = 0
Const cdoBasic = 1
Const cdoNTLM = 2
SMTPServer = ""
Const SMTPPort = 465
Const SMTPTimeout = 60
'Sending mail
Set objEmail = CreateObject("CDO.Message")
Set objConf = objEmail.Configuration
Set objFlds = objConf.Fields
With objFlds
Set objMsg = CreateObject("CDO.Message")
Set msgConf = CreateObject("CDO.Configuration")

msgConf.Fields.Item("") = 2
msgConf.Fields.Item("") = ""
msgConf.Fields.Item("") = 465
msgConf.Fields.Item("") = 1
msgConf.Fields.Item("") = "emailaddress"
msgConf.Fields.Item("") = "password"
msgConf.Fields.Item("") = 1

End With
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")
end function

Image used under creative commons

Tagged under: