11

QlikView Scheduled Reports – Sending Excel via Email

In company I work for higher management often requires specific data delivered to their mailbox every morning. They are not interested in interaction, using filters and “playing” with data.  They just want their predefined Excel ready when they will turn on PC.

Of course great solutions like Qlik’s new acquisition: NPrinting exist to do such things, but there is a way to do it even with Qlikview Personal Edition. Let’s jump right into it!

Qlikview Scheduled Reports: Preparation

For the sake of this post I have created Scheduler folder on drive C: and new QlikView document called Scheduler.qvw inside this folder.

You will of course want to send real data, I’ve just used Ctrl + Q Q shortcut (which generated some dimensions and expressions for me). I have also created simple Chart table using this data:QlikPad 03-03-15 at 09.28 PM

I have changed ID of this object to FOR_SCHEDULE. You may ask – Why? When there is only one char it may not be needed, but when you are preparing report, combined from larger amount of data objects, you can easily get lost seeing QV default object ID’s everywhere in macro code. To change object ID simply open its Properties (Ctrl + E, when object is selected) and change it on General tab)QlikPad 03-03-15 at 09.49 PM

Before we will create macro code, let’s create additional Sheet in the document, called Mail. It will be a configuration place for variables used in Script, as well as our Test Center. Let’s create all necessary variables now:

  • vExportReportFlag – this will be treated as macro enabler (it will be triggered only if this variable is set to 1)
  • SMTPServer – SMTP address (for Exchange version of the script)
  • vPath – Excel export destination folder
  • EmailTo – recipient’s email
  • EmailFrom – sender’s email
  • EmailSubject – email subject
  • EmailBody – email body (html can be used)

I have added those variables to Input box, which will make configuration easier (I will refer to those variables in macros):QlikPad 03-04-15 at 09.11 PM

Qlikview Scheduled Reports: Exporting data to Excel file

Our first task is to write “export table chart to Excel” macro. Open macro editor (Ctrl + M, or Tools -> Edit Module). We must configure it a little to proceed. We will use VBScript, so make sure this option is selected. Our macro requires System Access, so set it under “Requested Module Security“. You will need to set “Allow System Access” in “Current Local Security” as well. It should look like this:QlikPad 03-03-15 at 09.35 PMLet’s create code, which will export FOR_SCHEDULE chart to Excel. I have provided comments for every line, so it should be self-explanatory (there are two helper functions defined on the bottom – we will use them in the script too):

Let’s add a button with External, “Run Macro” action and test if procedure works fine. Button’s Actions properties should look like this:

QlikPad 03-04-15 at 08.13 PM

Now click the button and watch how XLSX file appears in folder described by vPath variable.

Qlikview Scheduled Reports: Email sending functions

Second task is to create macro, which will send previously exported excel file as an email attachment to chosen recipient. I will provide code for two scenarios. First – using exchange SMTP server and second – by simply using Gmail. You can copy both functions to Script Editor in QlikView and choose which one you want to use in final step.

1. Using Exchange server

Exchange configurations may vary in different companies. There may also be various limitations configured (for example it may not be possible to send email to external email address)

2. Using Google Gmail account

Please configure GmailUser & GmailPassword. I was of course not able to provide valid Gmail credentials 🙂

Qlikview Scheduled Reports: Automating whole process

We must now add one final procedure, which will execute all functions in right order and automate process:

You can create one more button now and test if everything works as intended.

Qlikview Scheduled Reports: Sending Email when report opens

Next step is adding a Trigger, which will run when document is opened. It will call our main GenerateMailReport procedure. To add it, go to Document Properties (Ctrl + Alt + D), Triggers tab. Select OnOpen from Document Event triggers and click Add Actions. Repeat same steps as we did previously for a button and add  TriggerEmailSend macro.

Save and close document.  Let’s see what will happen when you will open it again. Nothing happened? Well – that’s expected. You do not want the macro to trigger if you will open qvw file to amend it. vExportReportFlag set to 0 is not allowing macro scripts execution.

How to open this file and force macro execution? I really like using a small .bat file and utilize /v parameter. Simply create new file with .bat extension and paste below code:

You may need to adjust paths & report name to match your local setup

What is this doing? It is really simple!

  1. First part is just a Path to Qlikview desktop executable. (Notice the quotation)
  2. /v is passing a variable to our document. We are passing vExportReportFlag=1, so that our macro set in onOpen trigger executes
  3. Third part is just path to our report. (quotation here as well)

Qlikview Scheduled Reports: Configuring Scheduler

Right, so we have everything. Last thing we need is to schedule when new XLS report will be generated and emailed. Windows Task Scheduler is an answer here. We will simply schedule, when server must open our little bat file.

  1. To open Windows Task Scheduler, click WIN + R and write taskschd.msc in Run command window.
  2. In the right Actions column click on “Create Task” and follow tabs to create it
  3. In Triggers tab you will set scheduler
  4. In Actions tab you will choose to open the .bat file
Note: I have tested both Exchange and Gmail versions of code. I was using Qlikview 11.20 SR10 Personal, running on Windows 8
That’s it! I hope you enjoyed this little tutorial. If you have any questions feel free to email me or leave a comment below.

In Future posts I will expand this topic and will tell you how to:

  • Create an Excel with multiple worksheets & objects
  • Send email to multiple recipients at once
  • Format Excel data using vbscript

Jakub Szurogajło

Self-motivated, certified QlikView developer and designer, currently working as Business Intelligence workgroup manager at Harman - Connected Services division.

11 Comments

  1. Please help me.
    which create the variables with the values?
    When I click the button it opens the editor of macros.
    Thank you.

    • First – sorry for so late response…

      Not sure if this is what you ask for, but you can press Ctrl+Alt+V for variable overview window, where you can define variables. You can do that in Script as well (LET & SET prefixes)

  2. Hi,
    Thank you for you great explanation.
    The first marco works excellent and the new file is located in the required folder.
    but for some reason when I’m adding the rest of the macro (SendGMail) the macro isn’t working.
    what i’m doing wrong?
    I really appriciate your help.
    thanks in adavnce

    ‘==============================================================
    ‘=============== Main Sub, which calls others ================
    ‘==============================================================
    Sub TriggerEmailSend
    ‘Script will work only if vExportReportFlag will be set to 1
    If (ActiveDocument.Variables(“vExportReportFlag”).GetContent.String=1) Then

    ‘Trigger xlsx creation function
    Call GenerateMailReport

    ‘Delay script execution by 5 seconds (give some time for attachment to get generated)
    ActiveDocument.GetApplication.Sleep 5000

    ‘Choose which method do you want to use (Gmail is default)
    Call SendGMail
    ‘Call SendExchange

    ‘We will set vExportReportFlag variable to 0, so that macro will not be triggered if we “normally” open file (for example to edit it)
    ActiveDocument.Variables(“vExportReportFlag”).SetContent “0”,true
    ‘After email is sent we will delete Excel file…
    DeleteFile ActiveDocument.Variables(“vPath”).GetContent.String
    ‘…Save qvw…
    ActiveDocument.Save
    ‘… and close it
    ActiveDocument.GetApplication.Quit
    End If
    End Sub

    ‘==============================================================
    ‘================= Generate Excel Attachment =================
    ‘==============================================================
    Sub GenerateMailReport

    ‘First we are reading from variable the path to a folder where Excel file will be saved:
    set v = ActiveDocument.Variables(“vPath”)
    vPath = v.GetContent.String

    ‘Initialize Excel object:
    set XLApp = CreateObject(“Excel.Application”)

    ‘below command hides Excel window. In other words you will not see Excel application showing up, everything will be done in the background
    ‘If you want to see what Excel is doing (during testing phase for example), change it from false to true.
    XLApp.Visible = false

    ‘Add a Workbook to Excel file:
    set XLDoc = XLApp.Workbooks.Add

    ‘Clear all filters (you may not need this line, remove it if you have pre-defined filters selection in report
    ‘In future posts I will show you how to select filters via macro.
    ActiveDocument.ClearAll True

    ‘Here we are telling Excel into which Worksheet we will paste QV data:
    set XLSheet = XLDoc.Worksheets(1)
    XLSheet.Activate

    ‘Tell QV which object to copy from (in our case it is a Chart object, which ID = “FOR_SCHEDULE”:
    set table=ActiveDocument.GetSheetObject(“FOR_SCHEDULE”)

    ‘Copy content of this object:
    table.CopyTableToClipboard true

    ‘And paste into Excel
    XLSheet.Paste

    ‘Let’s also change Sheet’s name to something more fancy:
    XLSheet.Name = “Report name”

    ‘And select A1 cell, just so that it looks better when opened:
    XLSheet.Range(“A1”).Select

    ‘Now me must save report to previously set destination folder,
    XLDoc.SaveAs vPath

    ‘Close Workbook
    XLDoc.close

    ‘And finally close whole Excel file
    XLApp.Quit

    End Sub

    ‘==============================================================
    ‘============== Helper functions defined below ===============
    ‘==============================================================

    ‘Read variable
    Public function getVariable(var)
    set v = ActiveDocument.Variables(var)
    getVariable = v.GetContent.String
    end function

    ‘Delete file
    Public Function DeleteFile(vfile)
    Set obj = CreateObject(“Scripting.FileSystemObject”) ‘Calls the File System Object
    obj.DeleteFile(vfile) ‘Deletes the file.
    End Function

    ‘==============================================================
    ‘==================== Email Setup – GMAIL ====================
    ‘==============================================================
    function SendGMail()
    Dim objEmail
    ‘ Send the message using SMTP
    Const cdoSendUsingPort = 2
    ‘Do authenticate
    Const cdoAuth = 1
    ‘SMTP server name
    Const SMTPServer = “smtp.gmail.com”
    ‘SMTP Port
    Const SMTPPort = 465
    ‘Gmail User name
    Const GmailUser = “***”
    ‘Gmail password
    Const GmailPassword = “***”

    ‘Sending mail
    Set objEmail = CreateObject(“CDO.Message”)
    Set objConf = objEmail.Configuration
    Set objFlds = objConf.Fields
    With objFlds
    ‘———————————————————————
    ‘ Gmail 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”) = cdoAuth
    .Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = SMTPPort
    .Item(“http://schemas.microsoft.com/cdo/configuration/sendusername”) = GmailUser
    .Item(“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = GmailPassword
    .Item(“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = 1
    .Update
    ‘———————————————————————
    End With

    ‘Email Recipient
    objEmail.To = getVariable(“EmailTo”)
    ‘Email Sender – gmail will still send from original email
    objEmail.From = getVariable(“EmailFrom”)
    ‘Subject
    objEmail.Subject = getVariable(“EmailSubject”)
    ‘Text Body
    objEmail.HTMLBody = getVariable(“EmailBody”)
    ‘ Attachment
    objEmail.AddAttachment getVariable(“vPath”)

    ‘Send email using above settings:
    objEmail.Send

    Set objFlds = Nothing
    Set objConf = Nothing
    Set objEmail = Nothing
    end function

    • Hello and welcome!

      Starting from the very beginning. Did you properly set two constants in this part:

      ‘Gmail User name
      Const GmailUser = “***”
      ‘Gmail password
      Const GmailPassword = “***”

      You must have valid Gmail account and set its login and password for this method to work.

      Regards!

  3. Great tutorial that has come in really handy. I had an additional hurdle which was no SMTP server access, but sending from a workstation where i have an outlook account. I managed to use code the looks something like:

    ===============================
    Set objOutlook = CreateObject(“Outlook.Application”)
    Set objMail = objOutlook.CreateItem(0)
    objMail.Display ‘To display message
    objMail.Recipients.Add (“recipient1@example.com”)
    objMail.Recipients.Add (“recipient2@example.com”)
    objMail.Recipients.Add (“recipient3@example.com”)
    objMail.Subject = “Mail Subject”
    objMail.Body = “This is Email Body”
    ‘objMail.Attachments.Add(“C:\Attachment\abc.jpg”) ‘Make sure attachment exists at given path. Then uncomment this line.
    ‘objMail.Send ‘I intentionally commented this line
    objOutlook.Quit
    Set objMail = Nothing
    Set objOutlook = Nothing
    =========================
    i wrapped this in a sub function and called it from the main TriggerEmailSend function. Works well (although you can only send from yourself in this case.)

    Also worth noting that to avoid Macro warning popups which might interrupt automated sending, you can disable these messages under Settings -> User Preferences -> Security and un-check the ‘Confirm Launch from Macro’ box. Once this is done, the document-open trigger works well and proceeds normally.

  4. Hello,

    Thank you for this great tutorial.
    I am having some trouble with the gmail access.
    The table was created, but the actual send did not go through.
    I received an email regarding Sign-in attempt prevented.
    It said:
    “Hi Radu,
    Someone just tried to sign in to your Google Account *** from an app that doesn’t meet modern security standards.”

    Is there another setting I can use?

    Thank you,
    Radu

    • Solved it after reading part 2. 🙂

      Can the script be used to send to more than one receipient in the To: field? Or to another email address in the CC field?

      The generate script cleares all selections. Where should I edit the script to define some selections to be made before the generation of the report?

      Radu

  5. I need are help, I known how to send one excel file, or how to export multiple object in one excel file with multiple sheet, but I don’t know how to send via email, specific sheet from one excel file.??
    My qvestion, how to send specific sheet. If I have in one excel sheets: “Sheet1”, “Sheet2”, “Sheet3”, “Sheet4”, how to send just “Sheet3” to specific recipient, and “Sheet4” to other specific recipient, etc ??

  6. Thank you – Kakub. This is a very nice posting.

    I tried use your script on my project. Each component works fine but it don’t work use the combine one.
    On the other hand, how to schedule this app to send email out?

    Wish you all the best in 2017!

    Best regards,
    Deng

Leave a Reply

Your email address will not be published. Required fields are marked *