Script for Exporting and Sending email

[#1]

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.

[#2]

Is your problem solved ?

[#3]

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.

[#4]

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]

hola Gabriela.

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

Thanks.

[#6]

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.

[#7 · Accepted solution]

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

[#8]

Many thank Jochen Andries,

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

Thanks again,

Tai.

[#9]

I hope it works for you !!

Jochen

[#10]

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

I have a gmail account....

Thank you,

Roby