Welcome Guest, you are in: Login

QVSource: The QlikView & Qlik Sense API Connector (Docs)

Note that we also have a connector for downloading emails from IMAP or Pop3 accounts.


The Notification Connector allows you to easily send emails from your QlikView or Qlik Sense load script. This can be useful for example in reporting errors or sending a confirmation when a scheduled reload has completed.

The latest version of the connector (1.0.4) has some advanced features:
  • Supports up to 5 file attachments (totalling not more than 10MB in size. Contact us if you need more).
  • Delayed sending (you can request that the email is sent up to 2 minutes after the request. This is useful if you want to send the current QlikView application as an attachment after the remainder of the script has run and the application has saved).


Usage

To use the connector, first fill out the fields which contains information about your email account and SMTP server and also the contents of the email, you can then run the table:

Image

Once you have successfully tested an email you can move to the 'Connector Data' tab to get the load script to send the email, then copy the script somewhere at the start of your load script.

Using a file as the email body

See this page for notes on how you can define the contents of the email in an external file.

Using With GMail

Note that if you are sending via GMail you should set the SMTP server to 'smtp.gmail.com' and check the 'Use SSL' checkbox.

NOTE: It appears also that in order to store a copy of outgoing emails in your Gmail or Google Apps Sent folder, you need to log into your Gmail or Google Apps email Settings and click on the Forwarding and POP/IMAP tab and on the IMAP Access section ensure that IMAP is be enabled.

See this page for more information and this page for information on sending limits.

Advanced Script Example

The following is a more complete example script highlighting the connector in use.

A few key things worth noting:
  • The sending has been factored out into a separate QlikView function.
  • The email is sent to multiple recipients by separating the email addresses with a semicolon (;) character.
  • An additional function has been added to url encode the subject and message. This is because certain characters such as the # symbol will break the http request to QVSource. If you know that your subject and message contain only simple strings then you should not need this function.
  • The sending is configured to use a HTML email body. If you would like plain text simply include 'html=false' in the QVSource request.


//
// Set a default to email address at the application level
//
// NOTE: URL encoding should be done using UTF-8
//
let emailNotificationAddress = 'email1@domain1.com;email2@domain2.co.uk';

Sub urlEncode(str)
 
    let str=replace(str, '%', '%25'); // should be first
    let str=replace(str, '#', '%23');
    let str=replace(str, ' ', '%20');
    let str=replace(str, '$', '%24');
    let str=replace(str, '&', '%26');
    let str=replace(str, '+', '%2B');
    let str=replace(str, ',', '%2C');
    let str=replace(str, '/', '%2F');
    let str=replace(str, '\', '%5C');
    let str=replace(str, ':', '%3A');
    let str=replace(str, ';', '%3B');
    let str=replace(str, '=', '%3D');
    let str=replace(str, '?', '%3F');
    let str=replace(str, '@', '%40');
    let str=replace(str, '[', '%5B');
    let str=replace(str, ']', '%5D');
    let str=replace(str, '>', '%3E');
    let str=replace(str, '<', '%3C');
    let str=replace(str, chr(10), '%0A'); 	// Line feed.
    let str=replace(str, chr(39), '%27');	// 39 Apostrophe
     
    call=str;
         
End sub

//
// Reusable function to send email.
// Note that the from address is hardcoded but could also be split out into a variable.
//
sub sendEmail(subject, msg, to)

	call urlEncode(msg);
	call urlEncode(subject);
	
	NotificationConnector_SendEmail:
	LOAD
		sendemail_status,
		sendemail_result,
		sendemail_filesattached
	FROM
	[http://localhost:5555/QVSource/NotificationConnector/?table=SendEmail&subject=$(subject)&message=$(msg)&to=$(to)&from=myemail@mydomain.com&format=qvx]
	(qvx);
	
 	//drop table NotificationConnector_SendEmail; // Comment this out if you need to debug and see the results of the send attempt.
	
end sub

//
// Use following syntax to send an email
//
call sendEmail('Test Subject', 'This is a test body with # characters that should be html encoded.<br />Here is a new line with <strong>bold string</strong>.', '$(emailNotificationAddress)');

Attaching Files

Version 1.0.4 of this connector introduces the ability to attaching up to 5 files to each email totalling not more than 10MB (contact us if you feel you need the ability to attach more and/or larger files).

To achieve this you can simply include fileAttachment[1-5]=[your local file path] in the request as illustrated below:


NotificationConnector_SendEmail:
LOAD
	status,
	result,
	filesattached
FROM
[http://localhost:5555/QVSource/NotificationConnector/?table=SendEmail&subject=$(subject)&message=$(msg)&to=$(to)&from=myemail@mydomain.com&fileAttachment1=c:\dir\file.txt&fileAttachent2=c:\dir\file2.txt&format=qvx]
(qvx);

Note that QVSource must have access to these files so you might need to give the account which QVSource is running under permission to read from the file locations.

Delayed Execution

You can delay the sending of the email by up to 2 minutes by including a 'delayInSeconds=VALUE' in your request. This might be useful for example if you wish to send the current QlikView application which is loading after the reload and saving has completed.

The following script illustrates how this might be achieved. Note that this load statement will execute immediately and leave the email 'queued' for sending at a later point in time as specified by the delayInSeconds parameter. If you set a value for the delayInSeconds parameter greater that 120 (i.e. 2 minutes) it will be automatically set to 120.


let attachment = DocumentPath();
call urlEncode(attachment);

NotificationConnector_SendEmail:
LOAD
	status,
	result,
	filesattached
FROM
[http://localhost:5555/QVSource/NotificationConnector/?table=SendEmail&subject=$(subject)&message=$(msg)&to=$(to)&from=myemail@mydomain.com&fileAttachment1=$(attachment)&format=qvx&delayInSeconds=60]
(qvx);

In this example we use the QlikView 'DocumentPath()' function to get the path of the currently reloading document.

We also use the urlEncode function for completeness although this should not be necessary.

Troubleshooting

If you find that the connector will not send the email despite being sure that you have entered the correct details (this is best tested using a separate email application to verify it works independently) then, if you are running a Virus checker, please try disabling it temporarily and trying to send the email via the connector once again.

If this does work then the Virus checker is preventing the email from being sent and you will need to find a way of configuring the virus checker to not block QVSource.

For example, one user had to uncheck the 'Block' option from the 'Prevent mass mailing worms from sending emails' in the 'Anti-virus Standard Protection' category of McAfee Virus Scanner.

Change Log

1.1.1 - 17/12/2015
  • Minor internal code refactoring.

1.1.0 - 24/05/2015
  • BREAKING CHANGE - The sendemail_status, sendemail_result and sendemail_filesattached are now named status, result and filesattached.
  • Upgraded to new UI style.
  • From field no longer mandatory (username will be used if not set).
  • Added 'Use SSL' input parameter.

1.0.7 - 23/05/2013
  • New cc input field added.

1.0.6 - 27/03/2013
  • Now writes a results table with error message (instead of html error page) if parameters are missing.

1.0.5 - 14/12/2012
  • Fixed bug where error was shown in status column even on successful send.

1.0.4 - 10/12/2012
  • Removed Twitter authentication UI and replaced with note recommending using a service like Twitter Mail to send Tweets from load script.
  • Up to 5 files can now be attached to an email using the parameters fileAttachment1=.....&fileAttachment2=...... etc.
  • Inputs now added for testing subject and message contents from UI.
  • Checkbox now added to manually select whether email is html (or plain text).
  • Multiple email addresses can now be specified (separated by ;).
  • Sending of email can now be delayed by up to 120 seconds.

1.0.3 - 04/07/2012
  • Added From email address field (previously the username was used which wasn't always an email address and so errored).
  • from=&to= now included in url in script but left empty. They can be set and if not the values from QVSource will be used.

1.0.2 - 07/06/2012
  • Port number can now be set using servername:portnumber.
  • To email address now taken from settings if not passed as URL parameter.

1.0.1 - 25/05/2012
  • Email body is now html.

1.0.0 - 19/11/2011
  • First non beta release.

0.9.1 - 18/8/2011
  • Machine name now added into from email name.

0.9.0 - 9/8/2011
  • Initial Release.
  Name Size
- Notification Connector.png 93.92 KB
- Send Email Data.png 41.00 KB
- Send Email Script.png 66.42 KB


(QVSource works with Qlik Sense as well as QlikView - See this page for notes.)
QVSource - The QlikView & Qlik Sense API Connector | © Copyright 2011 - 2016 Industrial CodeBox Ltd