topic Script for Exporting and Sending email in QlikView

Script for Exporting and Sending email

Mon, 11 Jul 2011 04:29:27 GMT

Hi Everybody,

Please help me the script to export the Qlikview documents into Excell/PDF file and the script to send email in Qlikview environment.

Many thanks,

Tai.

Script for Exporting and Sending email

Mon, 29 Aug 2011 13:22:24 GMT

Is your problem solved ?

Script for Exporting and Sending email

Mon, 29 Aug 2011 16:11:01 GMT

Hi Andries,

Currently, I have some solutions but I don't know the best method to solve this problem. I will show you my solutions anh please help me find the best way?

S1: I use ExportBiff method to export data of QV Objects. But this methods just work on table and not work on objects are Chart.

S2: I use CopyTableToClipboard to export data of objects. This method base on the ActiveX Object of MS Office. I am not sure this solution will be the best way in the QV development.

Many thanks for your help.

Script for Exporting and Sending email

Mon, 29 Aug 2011 17:48:39 GMT

Hi,

You can make a report and send it in PDF with QlikView publisher and the PDF distribution add-on, if ypu don't have it you can use a macro to store the report as PDF and then send it by email, the code below does that:

Sub GeneratePDF

set val1=ActiveDocument.Fields("MAIL").GetPossibleValues

vReport = "RP01"

vName = "Report" 'Name of output pdf

reportFile = "C:\QlikViewApps\Productivo\PDF\" & vName &".pdf" 'Setting outputname

for f=0 to val1.Count-1

ActiveDocument.Fields("MAIL").select val1.Item(f).Text

printReportPDF "RP01", reportFile

ActiveDocument.GetApplication.Sleep 5000

SendEmail val1.Item(f).Text, reportFile

End sub

function printReportPDF(oReport, pdfOutputFile) 'Works only with QlikViewPDF version 7.52

Set WSHShell = CreateObject("WScript.Shell")

WSHShell.RegWrite "HKCU\Software\QlikViewPDF\OutputFile", pdfOutputFile, "REG_SZ"

WSHShell.RegWrite "HKCU\Software\QlikViewPDF\BypassSaveAs", "1", "REG_SZ"

'QV Print

ActiveDocument.PrintReport oReport, "QlikViewPDF", false

set WSHShell = nothing

end function

Sub SendEmail (mail,file)

'set port

Const cdoSendUsingPort = 2

Set objCDOMail = CreateObject("CDO.Message")

Set objConf = CreateObject("CDO.Configuration")

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

objConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "198.186.1.41" 'server IP

objConf.Fields.Update

Set objCDOMail.configuration = objConf

objCDOMail.To = mail

objCDOMail.From = "test@mail" 'from mail

objCDOMail.Subject = "QV mail"

strHTML = "QV mail test" 'body

objCDOMail.HTMLBody = strHTML

'attached a file:

rem ** let QV sleep for 0,5 seconds **

ActiveDocument.GetApplication.Sleep 7500

objCDOMail.AddAttachment file

rem ** let QV sleep for 5 seconds **

ActiveDocument.GetApplication.Sleep 7500

objCDOMail.Send

Set objCDOMail = Nothing

Set objConf = Nothing

end sub

Hope this helps!

Regards!

Script for Exporting and Sending email

pgalvezt — Mon, 29 Aug 2011 18:46:09 GMT

hola Gabriela.

Could you please put some example with the code that you mentionate in your last answer?

Thanks.

Script for Exporting and Sending email

Tue, 30 Aug 2011 04:33:32 GMT

Thank for your help.

It is pretty with my desired.

But I have a small issue that I don't know is the Register Output file maybe not work. The PDF printer always get the default file name when it print the report.

So, dose the PDF printer do not use the registry information when it print document?

Do you have any ideas for this problem?

By the way, once again thanks your script, it give my a light for QV documents exporting.

Thanks,

Tai.

Re: Script for Exporting and Sending email

Tue, 30 Aug 2011 05:04:45 GMT

OK,

This is what I have :

1. Install a PDF-printer

I installed "Bullzip PDF Printer" on the machine where the report runs.

=> Google is your friend

2. Make your QVW and a report, write down the reportname (RPxx)

3. In QV, enter the following script :

Sub zendrapport

ActiveDocument.reload

vReport = "RP02" 'Set report

vName = "transactierapportPDP" 'Name of output pdf

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

reportFile = "c:\PDFs\Operations\" & vName &".pdf" 'Setting outputname

MyPrintPDFWithBullZip(reportFile) 'Call pdf printer

ActiveDocument.GetApplication.Sleep 5000

mailrapport

ActiveDocument.Save

ActiveDocument.GetApplication.Sleep 5000

ActiveDocument.GetApplication.Quit

End sub

This script has a connection to 2 other script, just copy/paste them and change some parameters

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 mailrapport()

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-address of your 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 = "username@domain.com" 'Email Recipient

objEmail.From = "REPORTS@domain.com" 'Email Sender

objEmail.Subject = "The name of the report" ' Subject

objEmail.TextBody = "some text in the body of the mail" 'Text Body

objEmail.AddAttachment "c:\PDFs\Operations\transactierapportPDP.pdf" ' Attachement

objEmail.Send

Set objFlds = Nothing

Set objConf = Nothing

Set objEmail = Nothing

end function

Make sure that security is set to SYSTEM, both settings.

How to execute ?

I have the Dutch version running, so I'll try to translate :

Go to the Document Properties, next to security there's a tab 'Reactieveroorzakers' (=things to do when opening the document,...)

Add an action (or more) (at opening the document)

example : first select a field or several fields so a selection for your report is made.

Second add 'External - Execute Macro' and type in the name of the macro (in my case : zendrapport)

At the tab security, highlight everything, so the macro starts without questions.

Save the document with a name.

Leave QlikView, now in Windows :

I use JIT scheduler, but it can be done with Scheduled Tasks also. Make a schedule to open the QlikView-document.

What will hapen ?

The document will open,

The document will be reloaded,

The selections will be made,

The document will be printed as PDF (with the name and path provided in the macro),

The created PDF will be mail (with the settings provided in the mail-macro),

The document will be saved and closed.

Hope it works for you !!!

I have 54 documents running on the server. This macro saved us 15.000 euro !!

Jochen

Script for Exporting and Sending email

Tue, 30 Aug 2011 07:07:09 GMT

Many thank Jochen Andries,

Your scripts are very nice, it completely solve my problems.

Thanks again,

Tai.

Re: Script for Exporting and Sending email

Tue, 30 Aug 2011 07:56:14 GMT

I hope it works for you !!

Jochen

Re: Script for Exporting and Sending email

Thu, 27 Oct 2011 12:52:55 GMT

Hi, I have a problem with "objEmail.Send", but I don't know why . . . .Help me!

I have a gmail account....

Thank you,

Roby

Re: Script for Exporting and Sending email

eliran — Thu, 27 Oct 2011 12:56:09 GMT

Hi Roby,

What seems to be the problem?

Eliran.

Re: Script for Exporting and Sending email

Thu, 27 Oct 2011 12:57:18 GMT

I had the same isue with one qvd (I have about 50 automated qvd-reports).

In the module-editor, are both security-settings set to system ?

In documentsettings, tab 'security', I enabled everything.

Jochen

Re: Script for Exporting and Sending email

Thu, 27 Oct 2011 13:19:25 GMT

I can't connect with server . . . The only defference with Jochen script are smtpusessl = True and data for autentication . . .

@Jochen both secure-settings se to system

Re: Script for Exporting and Sending email

eliran — Thu, 27 Oct 2011 13:43:03 GMT

Hi,

Here are my settings for sending mail from gmail:

Sub SendGMail() 
  ' Object creation
  Set objMsg = CreateObject("CDO.Message")
  Set msgConf = CreateObject("CDO.Configuration")
  ' Server 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") = "YourMail"
  msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "YourPassword"
  msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = 1
 msgConf.Fields.Update 
  ' Email
  objMsg.To = "Whoever"
  objMsg.From = "YourMail"
  objMsg.Subject = "Subject"
  objMsg.HTMLBody = "Body"
  objMsg.Sender = "YourName"
' If you have attachment 
          Path ="Report Path"
          FileName="ReportFileName"
          objMsg.AddAttachment Path&FileName
  Set objMsg.Configuration = msgConf
  ' Send
  objMsg.Send
  ' Clear
  Set objMsg = nothing
  Set msgConf = nothing
End Sub

I hope it helps,

Eliran.

Re: Script for Exporting and Sending email

Thu, 27 Oct 2011 14:05:40 GMT

Ok, I resolve. At job I can't use gmail and in my my mail server I not have to autenticate me.

Thank you all

Script for Exporting and Sending email

Fri, 28 Oct 2011 01:50:35 GMT

Hi,

I used this code in my application and it worked fine in a 32bit windows xp computer but when I run my application on windows 7 64bit i got a message "ActiveX Component can't create object: 'Bullzip.PDFPrinterSettings' "

why and how can i solve this??

thank you!

Script for Exporting and Sending email

Fri, 28 Oct 2011 10:29:07 GMT

In which row of the macro?

Script for Exporting and Sending email

Anonymous — Tue, 20 Dec 2011 21:29:25 GMT

Thank you very much Jochen, this macro works perfectly for me under my win7 32 bits operating system with qv10. Now, i'll try to execute it from some trigger in the server...

i'll report advance.

Script for Exporting and Sending email

Wed, 21 Dec 2011 06:08:53 GMT

You're welcome !!

Indeed, as long as you work with a 32bit Windows, all goes well. I can't find the solution for the 64bit-problem 😞

I installed a virtual-32bit-machine to make the magic happening.

Script for Exporting and Sending email

udaya_kumar — Wed, 28 Dec 2011 06:06:48 GMT

hi jochen,

Is it possible to export qlikview objects to powerpoint dynamically and send mail to others?

and the sent ppt or pptx file should work even in their system.

Is it possible to do that? can you help me ?