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)

[djallelsadki]

Hello,

Very good work,

I want to ask you, how can i change the output file name into a variable values taht takes two or three fields,

var=field1&field2

i want this file name : var.pdf

can you help me ?

[#3]

Hi Jochenandries,

I was eagerly searching for Exporting the QVW file into PDF. And finally found it:) Thanks for making it.

But I need to revisit the code and make it suitable for my requirement. I want to ask one thing.  If this script is written in Edit module. How we can trigger this by an external event.

My requirement:

1. Report should get refreshed in Qlikview enterprise mangement console (QEMC)

2. Export the QV sheets to a single PDF file.

3. Send the PDF file as an attachment to the receipents list.

Manual run of the macro will export and send the QV report output in PDF, But It has to be triggered automatically once the QV report is refreshed. How can we acheive this ?? Could you please throw some light on this,

Thanks

Vinod

[#4]

I don't use the QEMC...

I have it running to update the QV-database-files, but I don't use for my apps.

What I do :

In JIT (a task scheduler) I programmed a schedule to open the QlikView-file and execute 1 or more macro's at opening.

In the macro he reloads the document, makes the PDF's, mail them around, saves the document and closes QlikView.

It's a dirty solution, but it works, and it's cheap !!!

[#5]

I am getting error

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

what is the reason

please help

Regards

Pramod Kumar

[#6]

First of all, is BullzipPDF installed ?

Second : Is it a 32- or 64-bit machine ?

[#7]

BullzipPDF is already installed

it is 32 bit machine

[#8]

On the left, are the security settings set to SYSTEM ?

[#9]

thanks jochenandres

Now , its working

Regards

Pramod Kumar

[#10]

You're welcome