Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Script for Exporting and Sending email

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.

1 Solution

Accepted Solutions
Not applicable

Re: Script for Exporting and Sending email

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

85 Replies
Not applicable

Script for Exporting and Sending email

Is your problem solved ?

Not applicable

Script for Exporting and Sending email

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.

Not applicable

Script for Exporting and Sending email

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

next

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!

pgalvezt
Valued Contributor

Script for Exporting and Sending email

hola Gabriela.

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

Thanks.

Not applicable

Script for Exporting and Sending email

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.

Not applicable

Re: Script for Exporting and Sending email

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

Not applicable

Script for Exporting and Sending email

Many thank Jochen Andries,

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

Thanks again,

Tai.

Not applicable

Re: Script for Exporting and Sending email

I hope it works for you !!

Jochen

Not applicable

Re: Script for Exporting and Sending email

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

I have a gmail account....

Thank you,

Roby

Community Browser