Qlik Community

QlikView Documents

Documents for QlikView related information.

Automated Excel Export and Email Macro

Contributor III

Automated Excel Export and Email Macro

The intended purpose is to automate the exporting of an object to a predefined file location in Excel format unless the chart renders no data in which it would export a confirmation file in .txt format. For the Excel export portion, there exists the ability to iterate over a field's values within the dashboard for the exported file. Then, It would take the corresponding file and attach it to an email - sent via SMTP to a predefined recipient list. The sequence is cascaded through a series of files - primarily in order to trigger the execution without interfering with the existing (production) dashboard. This includes utilizing an additional qvw in order to start the .bat file which enables the scheduling within the QMC.

I've attached a .zip folder containing all the required documents to view the set up as intended. It can easily be adapted or taken apart to meet a variety of needs.

Labels (1)
Contributor III

I tried this feature with a button trigger. However it dint work in my server. Macro dint throw an error. Am I missing something?

Contributor III


Sorry for delay in my response....I was not aware that a comment had been made.

I may need more information to better understand your set up and assist in troubleshooting. But, generally speaking you should be able to execute the feature using a button trigger.

The button would need to contain two run macro actions in this order using the provided code.

Run Macro > xlsExport

Run Macro > MailReport

If you still have trouble, let me know and I'll try to help assist in troubleshooting.

Contributor III

Thank you. Appreciate your response.. I got it working

Not applicable

How set user and password for autenthicate user smtp?

Contributor III

In the macro "MailReport" there are several constants that control authentication. I've adapted this macro to enable the basic authentication method and included it below. Changes to the script are noted. Copy/paste script into the edit module area within qlikview and set the variables with your details.

For more general information, here is a link that you may find helpful:


SUB MailReport

    DIM objEmail

    DIM varDate

    DIM v

    SET v = ActiveDocument.Variables("vDate")

          varDate = v.GetContent.STRING

    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


'added for basic authentication setup

    CONST cdoUserName = xxxxx 'username for using basic auth

    CONST cdoPassword = xxxxx ' password for using basic auth


    SMTPServer = "[Insert Address]"

    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") = cdoBasic 'THIS WAS CHANGED FROM cdoAnonymous to cdoBasic

           .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = cdoUserName 'THIS PARAMETER WAS ADDED FOR BASIC AUTH SETUP

           .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = cdoPassword    'THIS PARAMETER WAS ADDED FOR BASIC AUTH SETUP

           .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




    objEmail.To = "[Insert Email]"        'Email Recipient

    'objEmail.Cc = "[Insert Email]"          'Carbon Copy Recipient

    objEmail.From = "[Insert Email]"                        'Email Sender

    objEmail.SUBject = "QlikView Automated Excel Report"                        ' Subject

    objEmail.TextBody = "QV Report - Excel File Attached. This email is an automated procedure generated by QlikView."        'Text Body         

    objEmail.AddAttachment   "c:\export-email-automation\ExcelReport_"&varDate&".xls"    ' Attachment


    SET objFlds = NOTHING

    SET objConf = NOTHING

    SET objEmail = NOTHING



Thanks Autopilot. It is really helpful

New Contributor II

Hi autopilot,

Your script is very usseful.

I adapt it to obtain differents Excel spreadsheets from a huge repport.

But, I have a problem when I schedule it via the qemc, ever using your method (a qvw that launchs a .bat, that then launchs the final qvw -your dashboard- by using a variable to activate the script) it doesn't work.

The qemc stays in his "running green triangle" and does nothing.

Do you know if there are a limitation that concerns the qemc in using your method?

I saw something concerning that the qemc is blocking the possibility to launch a qv.exe, ever via .bat files...

Contributor III

I don't think it matters....but what version of QV are you using?

Will you elaborate what you mean by "it doesn't work" / "...does nothing" and I can try to assist troubleshooting. At what point does it seem to fail......or I guess what I'm asking is - what does it do?

For a quick fix, you could always simply trigger the .bat held in the initial qvw on a schedule using windows scheduler which should work-around the issue your having with qmc.

New Contributor II

Hi autopilot,

I'm using qv server V10. (10.00.9061.7)

I create in the qemc a task associated with the qvw that launch the .bat (your Call_BAT_Application.qvw).

When I run the task, the task passes from the waiting status to the running status and stay there. I see that the .qvw generates a .log that indicates that the execution has started, and thats all. The only way to stop the task in the qemc is to abort the task.

For test purpose, I changed the .bat that executes this qvw (instead of the TriggerMacro.bat) and I executed a .bat that copy/paste a file... and this works fine... I guess that the problem is related to the fact that to execute QV.exe from the qemc in not allowed by Qlikview... Should you confirm this or has you tried this in another qemc?

If there is no other choice, I agree with you to workaround this via the windows scheduler.


New Contributor II

I answerd in other windows.

Version history
Revision #:
1 of 1
Last update:
‎01-28-2013 04:14 PM
Updated by: