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 Sagar,

the pdf-printer needs to be installed on the machine where the qv.exe is running which takes care about the export and creation of the files. so usually only on the server.

Martin

[#3]

Thanks Martin.

That really makes sense.Will try your suggestion and keep you posted.

[hopkinsc]

Hi All,

i am trying this script but i keep getting the save dialog prompt for me to choose where to save the report instead of it picking it up from the script.

can anyone see what i am doing wrong?

Sub GeneratePDF()

        vReport = "RP01"

        vName = "Report" 'Name of output pdf

               ActiveDocument.PrintReport (vReport), "Bullzip PDF Printer", False 'Printreport

               ReportPath = "C:\QlikView\Reports\"

               ReportFile = ReportPath & vName & ".pdf" 'Setting outputname

               MyPrintPDFWithBullZip(reportFile)

End Sub

[Anonymous]

Hello everybody,

I've been using this code since almost 2 or 3 years,  But now it doesn't work anymore since i've reinstalled my computer (Windows 7 Pro 64 bits, same than before) .

When i use it in Qlikview editor, no problem. But when i use it in IE (IE 11), i get this message : "ActiveX component can't create object:: 'WScript.Network' ".

I checked and tested different parameters in IE security settings to allow activex execution

Here is part of my code:

Set WSHNetwork = CreateObject("WScript.Network")

' ========== Imprimante par defaut

Set oShell = CreateObject("WScript.Shell" )

sRegVal = "HKCU\Software\Microsoft\Windows NT\CurrentVersion\Windows\Device"

sDefault = ""

On Error Resume Next

sDefault = oShell.RegRead(sRegVal)

sDefault = Left(sDefault ,InStr(sDefault, "," ) - 1)

On Error Goto 0

GetDefaultPrinter = sDefault

Anyone an Idea ???

Thanks a lot for your help

[#6]

Hi,

I am trying to make work the part of the code, when QlikView generates and saves the report to pdf. It seems it is processing, yet there is no file saved in the target folder. Does somebody have idea, what might be wrong? The VBScript is triggered as action of a button in my document.

Sub DailyReport

vReport = "C:\QLIQVIEW\daily_activation.qvw" 'Set report

vName = "DailyActivation" 'Name of output pdf

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

reportFile = "C:\Reports\" & vName &".pdf" 'Setting outputname

MyPrintPDFWithBullZip(reportFile) 'Call pdf printer

ActiveDocument.GetApplication.Sleep 5000

End sub

FUNCTION MyPrintPDFWithBullZip (pdfOutputFile)

set obj = CreateObject("Bullzip.PDFSettings")

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

[hopkinsc]

Hi, i am not sure if this will help but here is the part of script that i use to get the pdf generated and saved.

I use PDFCreator though not Bullzip..

Set PDFCreator = CreateObject("PDFCreator.clsPDFCreator")

ReportID = "RP02" 'Set report  

vName = "Monthly Report" &".pdf" 'Name of output pdf

PDFCreator.cStart("/NoProcessingAtStartup")

PDFCreator.cOption("UseAutosave") = 1 ' Enable autosave

PDFCreator.cOption("UseAutosaveDirectory") = 1 ' Use a specific save directory

PDFCreator.cOption("AutosaveDirectory") = "C:\QlikView\Reports\"' Set autosave directory

PDFCreator.cOption("AutosaveFormat") = 0 ' Use PDF file type (.PDF extension)

PDFCreator.cOption("AutosaveFilename") = vName ' set the filename

PDFCreator.cPrinterStop = FALSE

ActiveDocument.PrintDocReport "RP02", "PDFCreator" 'Export to PDF File

Maybe that will help.

[#8]

Ok, Fixed it >> the value VReport was assigned with "RP01".

Curious thing is that when I actually was testing it with other object IDs representing ids of objects in the document like "CH07" for chart, the ActiveDocument.PrintReport function did not like this value.

[andrespa]

Hi Thierry,

I don't know if you're still looking for an answer to this problem. But it might be this:

You must have enable the option that allow you to create server objects.

Hope it helps.

Best regards,

Andrés

[#10]

Jochen Andries,

     Awsome work!

          I just have a doubt about the macro and the creation of the PDF file:

If I have section access and I wanted to get the view of the users via PDF to send to them, I would have to change the macro code to do authentications? And if this is the solution, the change that i would have to make would be at that part of the code:

Const cdoSendUsingPort = 2 ' Send the message using SMTP
Const cdoAnonymous = 0 'Do not authenticate
Const cdoBasic = 1 'basic (clear-text) authentication

If it's not in that part of the macro, how can I chage it so that when the PDF is created it'll get the restrictions from section access?

[#11]

Thanks for share the setup!!