13

QlikView Scheduled Reports – Sending Excel via Email part 2

In last post I showed you how we can use even Personal edition of QlikView to schedule excel file generation and sending an email with it as an attachment. It was simplistic example. One table exported to one worksheet within an excel file and sent to only one recipient. Today I want to tell you how we can add multiple objects to Excel file,  how to format its content using VBScript and finally how to send it to multiple recipients. Well.. Let’s jump into it!

If you have not yet read part 1 of this post, please go and have a look, I will refer to it below
You can find QVW example on the bottom of this page

Qlikview Scheduled Reports: Multiple objects in one Excel file

First I want to show you how we can add another QV object into same Excel Sheet. I will create one more table:

QlikPad 03-11-15 at 08.24 PM

Now using very simple vb code addition we can add it to Excel Sheet (please note that I have changed this objects ID to FOR_SCHEDULE_AVG):

And done! Excel will look like this now:

QlikPad 03-11-15 at 08.24 PM 001

If we want to add data to another Excel’s sheet, we must add below code to the script (for the sake of this example, I have created third table and change its ID to FOR_SCHEDULE_MAX):

New sheet was added, as well as the content:

QlikPad 03-11-15 at 08.27 PM

This concludes this topic. You can add as many objects and sheets as you like using this method!

 

Qlikview Scheduled Reports: Data Formatting using VBScript

In this section I want just point out the possibilities. You can refer to API guide for detailed information. Other way is just recording macro in Excel and using its code in QlikView.

Below code will do various changes to Header Row, comments inside:

Qlikview Scheduled Reports: Final Excel Generation Script

Below I am pasting complete script, which generates Excel file with formatted header row in first sheet. Excel report consists of 3 charts in 2 Sheets.

Qlikview Scheduled Reports: Sending report to multiple recipients

Last thing I promised to cover is sending email to multiple recipients at once. First we will need list of those recipients. I have created simple inline table, but you can populate it hoverer you want:

LOAD * INLINE [
Name, Email
Kuba, Kuba@company.com
James, James@company.com
Jakub, Jakub@company.com
];

How will it work?:

Script will read list of all recipients, then will select them one by one in the loop. Variables used in email sending script will be automatically set during every loop iteration.

Changes to variables:

  1. I have included one more variable vName which will just store name of the person to which email is sent. Definition: 
    =GetFieldSelections(Name)
  2. Changed definition of EmailTo variable: 
    =Email
  3. Finally changes to EmailBody variable (little improvement to previous version, we want to personalize this message): 
    ='='Hello <b>$(vName)</b>,
    <br /><br />
    You can find report in the attachment.
    <br /><br />
    Have a great day!<br />
    QlikView team.'

Variables now look like that:

QlikPad 03-12-15 at 05.39 PM

And finally changes to script. We will add new Sub, responsible for sending emails:

Last change must be done in TriggerEmailSend Sub, which we previously used to directly trigger email Sending function. I will just paste its whole code for your convenience:

As a last thing, let’s have a look how does it look like in mailbox:

QlikPad 03-11-15 at 10.27 PM

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.

Jakub Szurogajło

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

13 Comments

  1. Hello Sir,
    first of all thank you for this article,
    today i tried to use your gmail script, i filled all variables with my gmail informations but each time i click on the Send Gmail button nothing happens then i got the module window opened ( as if i used Ctrl+M); any idea ?

  2. Hello Sir,
    i changed this settings but i’m getting the same results ?!
    i’m using QV Desktop 11 SR 11

    • Hello 🙂
      I am not sure if you have followed Part 1 of this tutorial, as one part of it is essential for this one to work. I’m talking about attachment. It must exist in this path: C:\Scheduler\Report.xlsx (default location in example file)

      If you do not need attachement try commenting this line in code:
      objEmail.AddAttachment getVariable(“vPath”)

      Like that:
      ‘objEmail.AddAttachment getVariable(“vPath”)

      Hope it works for you.

  3. Hello, Unfortunately I get the same result as Mambi. QV 11 SR 12
    tried to use
    smtp-relay.gmail.com
    smtp.gmail.com
    aspmx.l.google.com (to send e-mail only on @gmail.com)
    tried to comment attaching the file – same result

    Is it Ok trying to browse link http://schemas.microsoft.com/cdo/configuration/smtpserverport via browser to get
    The resource you are looking for has been removed, had its name changed, or is temporarily unavailable?
    Thanks a lot in advance

    • I have not tested this script for some time now. There might have been some changes on Google itself. Will try to check this and post an update on blog.

      • I’ve just tested gmail script and it works flawlessly. Things I’ve done:
        1. Created Scheduler folder on drive c:
        2. Clicked Generate button in qvw, to generate xls
        3. Opened module script
        4. Changed line 194 (filled gmail user using pattern: user_name@gmail.com
        5. Changed line 196 (provided password for the user set above)
        6. Manually set EmailTo variable, with valid email – so I can test if it script really works
        7. Clicked Send Gmail Button
        8. Properly received email with attachment on my email.

        Hope it will help you!

  4. how to send just sheet, or the value of the sheet “Expr Sum and Max” to recipient1, and just “Expt Max” to another recipient2?
    Please help

  5. Greetings,

    first of all, great stuff, it has helped me immensely with my work!

    I wanted to ask, how could the multiple recipient sending part be tweaked to make every recipient see other recipients just as in any other normal e-mail, where I would just copy/paste the addresses in the To and Cc fields?

    Thanks!

    • Hello Arthur,
      I’m really glad it helped you a bit!
      Answering your question: you can just feed EmailTo variable with emails divided by a comma. (tested it a second ago and it works)

  6. Jakub, I need are halp, for some reason when I’m adding the rest of the macro (Sending report to multiple recipients) the macro isn’t working.
    what i’m doing wrong?

    Mail sent only if I select example “James@company.com”, but not all recipient.

    See my code:
    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

    function SendExchange()
    Dim objEmail
    ‘ Send the message using SMTP
    Const cdoSendUsingPort = 2
    ‘Do not authenticate
    Const cdoAuth = 0
    ‘SMTP server name
    SMTPServer = getVariable(“SMTPServer”)
    ‘SMTP Port
    Const SMTPPort = 25
    ‘SMTP timeout (seconds)
    Const SMTPTimeout = 60

    ‘Sending mail
    Set objEmail = CreateObject(“CDO.Message”)
    Set objConf = objEmail.Configuration
    Set objFlds = objConf.Fields
    With objFlds
    ‘———————————————————————
    ‘ Exchange 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

    ‘Email Recipient
    objEmail.To = getVariable(“EmailTo”)
    ‘Email Sender
    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

    sub Mail
    ‘We want to make sure that email is sent to all recipients, let’s remove all filters:
    ActiveDocument.ClearAll false

    ‘Pick up all possible values from listbox containing Recipient names:
    set Recipients = ActiveDocument.GetSheetObject(“id_listbox”)
    Values=Recipients.GetPossibleValues

    ‘Loop which will execute for ever recipient:
    for Range = lbound(Values) to ubound(Values)
    ‘Select nth listbox item – equal to current loop iteration.
    ActiveDocument.Fields(“Name”).Select Values(Range)

    ‘Send email using chosen method:
    ‘Call SendGMail
    Call SendExchange
    next
    end sub

    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

    ‘This time we are calling another sub, which will send emails to all recipients
    Call Mail

    ‘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

Leave a Reply

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