Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Is it possible to e-mail a specific chart within QlikView to a distribution list? The end user wants to have a QlikView app that contains multiple charts and graphs. They want the ability to e-mail specific charts to specific end users. Wasn't sure if this was possible from within QlikView or if I'd have to export the data and manage it with another software application.
thanks,
rob
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.
'################################EMAIL-HANDLING FUNCTION#################################
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
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "aaaaaa.corp"
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objMessage.Configuration.Fields.Update
objMessage.Send
End Function
sub email()
ActiveDocument.ClearAll False
ActiveDocument.Fields("CheckPointStart").Select ">=" & FormatDateTime( ActiveDocument.Evaluate("today(1)-30"),2)
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
Application.Sleep(1000)
set XLApp = CreateObject("Excel.Application")
XLApp.Visible = True
set obj = ActiveDocument.getSheetObject("MainTable")
obj.sendToExcel
set XLDoc = XLApp.Workbooks.item(1)
' ActiveDocument.GetSheetObject("LB03").CopyTableToClipboard true
' XLApp.Worksheets(1).Range("A1").Select()
' XLApp.Worksheets(1).Paste()
XLDoc.SaveAs path
'msgbox "pause"
XLApp.Quit
Application.Sleep(1000)
call sendmail("a@aaaaaa.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("a@aaaaa.com", 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
ActiveDocument.ClearAll False
end sub
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.
'################################EMAIL-HANDLING FUNCTION#################################
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
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "aaaaaa.corp"
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objMessage.Configuration.Fields.Update
objMessage.Send
End Function
sub email()
ActiveDocument.ClearAll False
ActiveDocument.Fields("CheckPointStart").Select ">=" & FormatDateTime( ActiveDocument.Evaluate("today(1)-30"),2)
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
Application.Sleep(1000)
set XLApp = CreateObject("Excel.Application")
XLApp.Visible = True
set obj = ActiveDocument.getSheetObject("MainTable")
obj.sendToExcel
set XLDoc = XLApp.Workbooks.item(1)
' ActiveDocument.GetSheetObject("LB03").CopyTableToClipboard true
' XLApp.Worksheets(1).Range("A1").Select()
' XLApp.Worksheets(1).Paste()
XLDoc.SaveAs path
'msgbox "pause"
XLApp.Quit
Application.Sleep(1000)
call sendmail("a@aaaaaa.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("a@aaaaa.com", 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
ActiveDocument.ClearAll False
end sub
Thanks Eugene. I'll give it a try.
rob
This is great! Thanks for sharing.
Hi Eugene
I have tried you solution above. It all works fine untl I get to the "objMessage.Send" line, then it fails. My macro secrity is set to System access. I asume it is because of thesmtp port not allowing the e-mail through due to authorisation issues. I can send e-mail via this port normally via outlook. Is there any way of getting an error message back to see what the exact problem is?