topic Re: email from QlikView in QlikView

email from QlikView

datanibbler — Tue, 10 Feb 2015 14:07:11 GMT

Hi,

this is a rather old and already-much-discussed feature. Still: I have the following dilemma:

- I have both QVS and RtM (a third-party tool to automate reports) running on the company server.

- This server, for very understandable reasons, does not and will not have Outlook or any other emailing program installed.

- I can set up in any QlikView_app an alert with emails - those emails will be triggered upon Reload on the server, but only
   be sent once I open the app locally. I can, however, do that by simply putting it in the "Startup" folder, so it will be
   started in the morning once I boot up my laptop.
   I can also set for this alert a condition that can never be true, so it will fire daily and send those emails.
   <=> I cannot add any attachments to those emails.

Can any of you suggest to me a possible way around this dilemma?

Thanks a lot!

Best regards,

DataNibbler

Re: email from QlikView

marcus_sommer — Tue, 10 Feb 2015 14:28:11 GMT

Hi DataNibbler,

you could use a windows-feature "cdo.message" with vbs - have a look on this example:

http://community.qlik.com/message/296673#296673

- Marcus

Re: email from QlikView

datanibbler — Tue, 10 Feb 2015 15:11:40 GMT

Hi Marcus,

that looks helpful. This code begins with that line "SET objEmail ...", right? So I don't have to process all of that discussion?

That is a code that I could put into a "module" in a QlikView_app, right?

The code uses some email_function on the Microsoft_servers, right?

That would not necessarily have to run on the server then - depending on the times of day when this reporting would be required, I could have it run on my own laptop. Well, strictly speaking, I'd be a bit astonished if the Server_admins were to allow a program to automatically access the Internet, without direct interaction at the time - but since I know from experience that they do allow QlikView_apps to run command lines, it's probable.

I will try it out.

Re: email from QlikView

marcus_sommer — Tue, 10 Feb 2015 15:32:14 GMT

Hi DataNibbler,

yes, try it at first with copy/paste from this code, uncomment the attachment, change the From & To part and change the server-name/server-ip with them from your exchange-server (you could find themin your outlook-account properties if you press change account - and it should work.

More examples and explanations could you find here: http://www.paulsadowski.com/wsh/cdo.htm

- Marcus

Re: email from QlikView

datanibbler — Tue, 10 Feb 2015 15:59:29 GMT

Hi,

ok, I have copied your code and pasted it into a module in my app.

What did you mean by "uncomment the attachment"? What should I uncomment?

I changed the From and the To parts and for the >> Path&File << (attachment) I added an actual filepath (incl. filename and ending).

When I click on "Test", the first message that I get reads "Expected statement" with just that filepath highlighted.

I used the path to a file on my Desktop, the line now reads

>> objEmail.AddAttachment C:\Users\friedrich.hofmann\Desktop\Staplerkosten_56LFS.pdf <<

I guess I also have to change something about those config lines, but I'll go about it step by step as I don't have anything much in the way of experience with vbs ...

Thanks a lot!

Best regards,

DataNibbler

Re: email from QlikView

marcus_sommer — Tue, 10 Feb 2015 16:08:15 GMT

Hi,

I meant with uncomment the attachment excactly this:

'objEmail.AddAttachment Path & File

simply to check if the mailing itself worked. If the attachment-statement failed then because the attachment or the path didn't exists or the access rights are wrong or it contained any chars which needed to wrapped into double-quotes - most often a PathFile sould be look like "C:\SomeFolder\AnyFile.FileExtension".

- Marcus

Re: email from QlikView

datanibbler — Tue, 10 Feb 2015 16:19:24 GMT

Ah. Thanks!

Well, I'll uncomment it then and try again.

Now the message is "The transport failed to connect to the server." and the very last line >> objEmail.Send << is highlighted.

As to the servername, I did what you suggested in my Outlook program (strangely, what it showed me there is slightly different from what IT once told me to set up in the QMC, but that results in the very same message, I tried)

and I put that inside the double quotes in the line

>> objEmail.Configuration.Fields.Item(config & "smtpserver") <<

I guess I have to activate again the lines after that (about the authentication) - although the email_server_settings in the QMC include no kind of authentication?

I have set both of the dropdown boxes on the left side to "access external files"

So what do you think could be the issue this time?

P.S.: I had a look at this paulsadowski.com - there are some advanced examples of things I am not thinking of yet, but no specific explanation of this basic part as far as I could see.

Re: email from QlikView

marcus_sommer — Tue, 10 Feb 2015 16:58:54 GMT

For me it worked with these default-settings. Maybe your settings are customized in some way. If you have already mail-settings in qmc and/or client try it with them. If this don't work ask your server-guys for the settings (especially with authentication) and if they have some securitiy settings (firewall, group policies ...) which could block this. If this not really helped then it will be a rather unpleasant trial and error game - maybe you will find here more: https://www.google.de/?gws_rd=ssl#q=cdo.message%2C+The+transport+failed+to+connect+to+the+server

- Marcus

Re: email from QlikView

datanibbler — Wed, 11 Feb 2015 08:10:59 GMT

Thanks Marcus!

I'll try.

I'm sure the server_admins have some security settings in place on the server - there's a reason they don't want Outlook on the Server and for the same reason they will not be extremely willing to help me set up a macro to send email from the server ...

I will have to try, though.

Thanks a lot for helping!

Best regards,

DataNibbler

Re: email from QlikView

datanibbler — Wed, 11 Feb 2015 09:05:57 GMT

Hi Marcus,

I have now entered the settings IT gave me for the QMC in that line about the SMTP_Server.

In the QMC, there is no authentication (it is set to "use Distribution Service Account") - the DistributionService is being run by a dedicated "QlikView_user" that has a username and password set. => So I am not sure if the parameter 1 in that line about "smtpauthenticate" is right and whether I need the following lines.

Maybe I can contact that Paul Sadovski about it?

Worth a shot, I guess.

Best regards,

DataNibbler

Re: email from QlikView

marcus_sommer — Wed, 11 Feb 2015 09:46:51 GMT

Hi DataNibbler,

I run the most tasks with my own domain-account and therefore I don't need authentication for this vbs-mailing or for the mail-settings within the user-properties. In the qmc I use also my account with my credentials (our server-user could only run the server itself).

I would try it at first local without and with credentials - for this there are special cdo-settings:

http://www.paulsadowski.com/wsh/cdo.htm

'Your UserID on the SMTP server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusername") = "youruserid"

'Your password on the SMTP server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "yourpassword"

If all this isn't successful then it could be helpful to ask someone with more experience with this topic like Paul Sadowski.

- Marcus

Re: email from QlikView

datanibbler — Wed, 11 Feb 2015 10:30:44 GMT

Hi Marcus,

ok, forget it for the moment. I'm going to do that together with an external Qlik_consultant we have. There is still one day of a Workshop open which we already paid. We're going to do it then.

Just one question:

I would like to get an ebook on the topic of VBS to advance my own skills in that respect - which makes me wonder, do I have to mind a difference like that between "Java" and "Javascript" - which are two completely different things which I learned AFTER having bought a book 😉

Re: email from QlikView

marcus_sommer — Wed, 11 Feb 2015 10:46:35 GMT

Hi DataNibbler,

there isn't a ebook available about qlikview-vbs or one which contained a significant part. But for vbs you will find various possibilities like this: http://xgangand.free.fr/adm/files/books/scripting/microsoft_vbscript_step_by_step.pdf - I hope it's legal published.

But to work through such book will be a lot of effort and it's not specific to qlikview. Therefore I think it's better to read the short chapter within the help and then look into the APIGuide.qvw - there you could learn a lot about the object-model within qlikview and there are also many examples available.

- Marcus

Re: email from QlikView

datanibbler — Wed, 11 Feb 2015 10:51:59 GMT

Ah,

so the vbs that is used in QlikView is different from or a part of the whole of VBS?

I will have a look at that qvw - where can I find that?

You are right in looking at the relation between the effort and the good it will do - I am sure knowing VBS would be helpful, but I do have to look at the time I have to spend - although, while there is no real management_focus on QlikView here, I am m.o.l. free to make my own agenda and decide what is worth spending time on.

Thanks a lot!

Best regards,

DataNibbler

Re: email from QlikView

marcus_sommer — Wed, 11 Feb 2015 11:06:31 GMT

Hi DataNibbler,

most of the qv-vbs implementation will be quite the same like the origin vbs with a few exeptions like error-handling (is descripted in the help) but it is of course adjusted to the qv-object.model.

You will find the APIGuide.qvw in your install-folder by automation but within the community there are also versions available: http://community.qlik.com/docs/DOC-7448

- Marcus

Re: email from QlikView

datanibbler — Wed, 11 Feb 2015 11:14:33 GMT

Thanks a lot!

And if you don't mind, please upload the help_file from your client (you have a current one, I take it?) Mine is very outdated, so I don't quite trust that what's written in my help_file is really up to date.

Re: email from QlikView

marcus_sommer — Wed, 11 Feb 2015 12:02:14 GMT

My version is older than this one from the link above - it seems that in QV 11 this app isn't more available anymore. Maybe someone else has a newer version?

- Marcus

Re: email from QlikView

marcus_sommer — Wed, 11 Feb 2015 12:04:11 GMT

There is: http://community.qlik.com/message/268795#268795