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]

I have a lot of trouble building a decent report out of my dashboard as QlikView's built-in report function is a piece of cr**.

What I would like to do is simply "print out my dashboard". This is available via the "print worksheet" function in the file menu. However, I have no possibility to e.g. center or scale the entire worksheet in the printer menu - it comes out wrong. The dashboard I am working in is too complicated to be split into easier parts in an additional worksheet (which may then be put together to a report again). For example, I am using two graphs at the same level, one of which is a pie-chart. It is impossible to adjust its size correctly. 

Additionally, the result when printed into a PDF-file is too blurry to be used. 

Any ideas or suggestions how I might solve this? I love the solution in this thread, however it appears not to be a solution to get around the report function.

Best regards

Urs

[sanketkhunte]

Hi Everboy / Martin ,

Thank you for your reply.

I want little help here for Print functionality.

I am trying to do as your's mentioned in your block but still get some errors.

Would you please share with me Qlik View file in which print functionality working ... This will more helpful for me.

I am waiting you your valuable reply.

Regards

Sanket K.

[sanketkhunte]

Hi Everbody / Martin ,

Thank you for your reply.

I want little help here for Print functionality.

I am trying to do as your's mentioned in your block but still get some errors.

Would you please share with me Qlik View file in which print functionality working ... This will more helpful for me.

I am waiting you your valuable reply.

Regards

Sanket K.

[sanketkhunte]

Hi JochenAndries,

Thank you for your reply.

I want little help here for Print functionality.

I am trying to do as your's mentioned in your block but still get some errors.

Would you please share with me Qlik View file in which print functionality working ... This will more helpful for me.

I am waiting you your valuable reply .

Regards

Sanket K.

[sanketkhunte]

Hi Jagan,

Thank you for your Help.

I am trying with above code but i got the error.

below is the image I have attached - :

Please let me know what is the issue ?

Thanks

Sanket K.

[#7]

I "partly" got it to work now

What I would like to do is the following:

1) Reload the entire document

2) Export the reloaded document into PDF

3) Send the PDF via e-mail

What happens in the macro: the data is reloaded, the data is put into a PDF report and sent via e-mail. But: I do not receive the actual PDF-report based on the reloaded data, but the version before that (aka "the last version", not the latest).

Does anybody have an idea why this happens?

Attached my version of the script:

Sub Dagrapport

ActiveDocument.reload

ActiveDocument.save

vReport = "RP01"

vName = "Management Summary"

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

reportFile = "c:\temp\" & vName & " - " & Date &".pdf"                                         '

MyPrintPDFWithBullZip(reportFile)

ActiveDocument.GetApplication.Sleep 5000

zendDagrapport reportFile

End sub

FUNCTION MyPrintPDFWithBullZip (reportFile)

set obj = CreateObject("Bullzip.PDFSettings")

obj.PrinterName = "Bullzip PDF Printer"

obj.SetValue "Output" , reportFile

obj.SetValue "ConfirmOverwrite", "no"

obj.SetValue "ShowSaveAS", "never"

obj.SetValue "ShowSettings", "never"

obj.SetValue "ShowPDF", "never"

obj.SetValue "RememberLastFileName", "never"

obj.SetValue "RememberLastFolderName", "never"

obj.SetValue "ShowProgressFinished", "never"

obj.SetValue "ShowProgress", "yes"

obj.WriteSettings True

END FUNCTION

function zendDagrapport(reportFile)

Dim objEmail

Const cdoSendUsingPort = 2

Const cdoBasic = 1

Const cdoNTLM = 2                                                                                                                                  

Const SMTPServer = "<Server IP>"

Const userName = "<username>"

Const password = "<passwort>"

Const SMTPPort = 25

Const SMTPTimeout = 60

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/smtpserver") = SMTPServer

.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = SMTPPort

.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort

.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic

.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = userName

.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = password  

.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True

.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = SMTPTimeout

.Update

End With

objEmail.To = "<user e-mail>"

objEmail.From = "<user e-mail"

objEmail.Subject = "<Text> " & Date

objEmail.TextBody = "<Text Text Text>"

objEmail.AddAttachment reportFile

objEmail.Send

Set objFlds = Nothing

Set objConf = Nothing

Set objEmail = Nothing

end function

[#8]

That one I didn't get at all

[#9]

Cleared! It takes a while to write the PDF onto the disk so I had to insert a little halt to pause the script right before adding the attachment to the e-mail. It works fine now!

When printing the PDF I have added:

ReportName = "Management Summary"

ReportPath = "c:\temp\"

ReportFile = ReportPath & ReportName & " - " & Date & ".pdf"

When adding the PDF to the e-mail:

rem

ActiveDocument.GetApplication.Sleep 7500

objEmail.AddAttachment "c:\temp\Management Summary - " & Date & ".pdf"

As this is a daily report I wanted to add the actual date to every file.

Best

Urs

[#10]

Hi Jochen ,

I have been able to create PDF Reports using the macro that you have posted. Thank you very much

But i have one more question : Does every user need to install the 3rd party PDF printer (BullZip Printer or QlikViewPDF) on his/her system or just installing it on the server would do the job?

Thanks in Advance.

Sagar