I have done something like this before within QlikView before.
There are two steps, export the image to a file, then send the file as an attachement using vbscript. Example code below (example is for Excel export, but image export would be similar, make sure to check the APIguide.qvw file for help). Also make sure to give most generous perms (System Access)to the macro or security will block sending mail.
Public Function SendMail (From,SendTo, Subject, Body, Attachment) ', Attachment, SendCC, SendBCC)
Set objMessage = CreateObject("CDO.Message") objMessage.Subject = Subject objMessage.From = From objMessage.To = SendTo objMessage.CC = SendCC objMessage.BCC = SendBCC objMessage.TextBody = Body 'msgbox Attachment if( not "" = Attachment) then objMessage.AddAttachment Attachment end if
result = ActiveDocument.Evaluate("$(thresholdTest)") 'msgbox result if( "Fail" = result) then
Set oShell = CreateObject("WScript.Shell") set fso = CreateObject("Scripting.FileSystemObject") path = oShell.CurrentDirectory & "\DataIntegrity.xls" If fso.FileExists(path) Then set excelFile = fso.GetFile(path) excelFile.Delete end if
set XLApp = CreateObject("Excel.Application") XLApp.Visible = True set obj = ActiveDocument.getSheetObject("MainTable") obj.sendToExcel
call sendmail("email@example.com", ActiveDocument.GetVariable("sendTo").GetContent.String, "FAIL: Data Integrity test for Internal QV reports failed.", _ "A total of "&ActiveDocument.Evaluate("$(failedDataSets)") & _ " out of "&ActiveDocument.Evaluate("$(totalDataSets)") & " datasets had discrepencies which exceeded the " & _ ActiveDocument.GetVariable("warningThreshold").GetContent.String & _ " threshold. See attached Excel doc for details for last 30 days. The full QV report is here: https://aaaaaa.com/DataIntegrity_ajax/", path) else call sendmail("firstname.lastname@example.org", ActiveDocument.GetVariable("sendTo").GetContent.String, "PASS: Data Integrity test for Internal QV reports passed.", _ "All discrepencies were below the " & ActiveDocument.GetVariable("warningThreshold").GetContent.String & _ " threshold. The full QV report is here: https://aaaaa.com/DataIntegrity_ajax/", "") end if
But can I call this function from QlikView Publisher ?
What I need is that I schedule the Excel or PDF generation and then distributing them via email. If I put the macro in the Open Document Event this will generate a document exporting everytime I open the document, which is not what I want.
I am sorry, it sounds more fancy than it is. I am using "control file" like "control flag". It is an empty file, but it's existence is tested by the QV load script or Macro, and used to make a decision of whether to export. Then the file is deleted. The file can be created by a scheduled task, and thus control the export from QV.
Yes. You will need to write macro script. For Excel, you just need to utilize the Excel Object Library and the QlikView API to paste the contents into an Excel workbook object as Eugene suggested. Then you can control Excel through the workbook object just as if you were writing macros directly in Excel.
I would use a variable as a flag to indicate whether these operations should be performed conditonally or not. Possibly the qvuser()? Maybe the time the qvw is accessed?
You can probably do the same with pdfs using any type of pdf export software.
There are a variety of custom ways to actually handle the email aspect of it.
Eugene Please help me. I am trying to use a section of your code in an Qlikview macro. Basically I need to open and paste the content of a straight table in the worksheet. Your sample does exactly that but it keeps failing on the first line. "Set XLApp = CreateObject("Excel.Application")"
set XLApp = CreateObject("Excel.Application")
XLApp.Visible = True
set obj = ActiveDocument.getSheetObject("CHEXL01")