topic Re: Sending QV object by e-mail using Macro in QlikView

Sending QV object by e-mail using Macro

linoyel — Sun, 13 Apr 2014 12:12:47 GMT

Hey all,

I need your help in Macro issue.

I need an e-mail with a QV document certain objects to be sent daily.

I understand it's possible to write Marco that does it, but the only option with examples I've found meanwhile is creating a button that runs Macro. This is not a good solution in my case, cause I need the e-mail to be sent every day automatically (by schedule), without waiting that someone will press the button to send an e-mail...

Is there an outside-of-Qliqview option to write a VB script for sending e-mails?

If there is, please give me some examples to implement this (I have no idea of writing Macros..)

Thank you!

Re: Sending QV object by e-mail using Macro

Sun, 13 Apr 2014 13:02:17 GMT

Hi Linoy,

Hope this will help you.

http://community.qlik.com/docs/DOC-3772

Re: Sending QV object by e-mail using Macro

fkeuroglian — Sun, 13 Apr 2014 13:49:38 GMT

Hi Linoy

One Solution could be create a qvw that have the macro to send the email, and then create a windows task that call this qvw and execute the macro.

the task would be automatic and you can set the hour and others configurations.

Tell me if you need something

Good luck

Fernando

Re: Sending QV object by e-mail using Macro

arsal_90 — Sun, 13 Apr 2014 14:43:03 GMT

Hi Linoy

Please find the attached word file and follow the mention steps in word you can achieve your goal.

Re: Sending QV object by e-mail using Macro

linoyel — Sun, 13 Apr 2014 14:50:18 GMT

Thank you for your help!

All answers were great and helped me a lot in understanding the subject.

I also found a nice explanation how to activate Macro code using triggers (instead of a button):

Qlikview Auto Reload Macro by RFB 78 - YouTube

Re: Sending QV object by e-mail using Macro

linoyel — Sun, 13 Apr 2014 14:55:00 GMT

Hi Fernando!

Thanks - that seems to be a great solution for me and I'll certainly try it.

Although, is it possible to write a Macro in one qvw that emails objects from another qvw?

What's the code for it?

Re: Sending QV object by e-mail using Macro

arsal_90 — Sun, 13 Apr 2014 14:58:33 GMT

1. Install Bullzip PDF Printer (I tried CutePDF, PDF X-change, PdfCreator,.. bullzip is the only one that works)

2. Macro :

Sub Dagrapport

vReport = “QVreportname” ‘Set report

vName = “reportname” ‘Name of output pdf

ActiveDocument.PrintReport(vReport), “Bullzip PDF Printer”, false ‘Printreport

reportFile = “reportpath” & vName &”.pdf” ‘Setting outputname

MyPrintPDFWithBullZip(reportFile) ‘Call pdf printer

ActiveDocument.GetApplication.Sleep 5000

zendDagrapport

End sub

FUNCTION MyPrintPDFWithBullZip (pdfOutputFile)

set obj = CreateObject(“Bullzip.PDFPrinterSettings”)

obj.SetValue “Output” , pdfOutputFile
obj.SetValue “ConfirmOverwrite”, “no”
obj.SetValue “ShowSaveAS”, “never”
obj.SetValue “ShowSettings”, “never”
obj.SetValue “ShowPDF”, “no”
obj.SetValue “RememberLastFileName”, “no”
obj.SetValue “RememberLastFolderName”, “no”
obj.SetValue “ShowProgressFinished”, “no”
obj.SetValue “ShowProgress”, “no”
obj.WriteSettings True

END FUNCTION

‘===========================================================================

function zendDagrapport()

Dim objEmail

Const cdoSendUsingPort = 2 ‘ Send the message using SMTP

Const cdoAnonymous = 0 ‘Do not authenticate

Const cdoBasic = 1 ‘basic (clear-text) authentication

Const cdoNTLM = 2 ‘NTLM

SMTPServer = “IP Mailserver”

Const SMTPPort = 25 ‘ Port number for SMTP

Const SMTPTimeout = 60 ‘ Timeout for SMTP in seconds

‘Sending mail

Set objEmail = CreateObject(“CDO.Message”)

Set objConf = objEmail.Configuration

Set objFlds = objConf.Fields

With objFlds

‘---------------------------------------------------------------------

‘ SMTP 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

objEmail.To = “to emailaddress” ‘Email Recipient
objEmail.From = “from emailaddress” ‘Email Sender
objEmail.Subject = “emailsubject” ‘ Subject
objEmail.TextBody = “emailbody” ‘Text Body

end function

Re: Sending QV object by e-mail using Macro

fkeuroglian — Sun, 13 Apr 2014 15:02:23 GMT

Linoy

You would export the object before?

You could export before the object or you could replicate the objects and put in the new qvw.

could be a copy of the first qvw that only the diferences is that have this macro send email pherhaps

Re: Sending QV object by e-mail using Macro

linoyel — Sun, 13 Apr 2014 15:08:07 GMT

The original qvw runs every hour (and it's very heavy, it runs for 25 mins) but I need to send a certain object from it only once a day (this specific object changes once a day as well).

Re: Re: Sending QV object by e-mail using Macro

fkeuroglian — Sun, 13 Apr 2014 15:14:25 GMT

Ok, perfect

You could export the objects to some local folder and then the new qwv wiit the macro will import and attached in the email

your macro will attach them

i send you the macro to send email with object attached

Re: Sending QV object by e-mail using Macro

linoyel — Tue, 15 Apr 2014 16:15:53 GMT

It seems to me we're getting closer

How do I export objects to a folder and how do I do it only once a day?...

Re: Re: Sending QV object by e-mail using Macro

fkeuroglian — Tue, 15 Apr 2014 16:32:21 GMT

Hi Linoy

i attach an example to export the object to a root folder in a pdf format.

Re: Sending QV object by e-mail using Macro

arsal_90 — Tue, 15 Apr 2014 17:19:51 GMT

Hi Fernando

Your work is awesome. But i have to ask one question can u elaborate Make Pdf is making pdf in which root drive. You mention C:\ but i can't find it there

Thanks & regards

Arsalan

Re: Re: Sending QV object by e-mail using Macro

fkeuroglian — Tue, 15 Apr 2014 17:32:44 GMT

Hi Muhammad, im glad that help you the document.

you can modified the root, by defect i put C: but you can change it in the edit module.

if your pdf doesnt create, please check that the option (i attached an image) are the same i show you

you have to allow acces to your sistem to write in c(or in another local disk)

please tell me if you need something

Fernando

Re: Re: Sending QV object by e-mail using Macro

arsal_90 — Tue, 15 Apr 2014 17:46:50 GMT

Hi Fernando

I change the root folder from C:\ to D:\. But it not save any pdf can u tell me which part of macro is saving PDF as i understand it's printRP01.

Find the attached pic

Re: Re: Re: Sending QV object by e-mail using Macro

arsal_90 — Tue, 15 Apr 2014 17:51:28 GMT

Hi Fernando

I change the root folder from C:\ to D:\. But it not save any pdf can u tell me which part of macro is saving PDF as i understand it's printRP01.

Find the attached pic

Re: Re: Sending QV object by e-mail using Macro

fkeuroglian — Tue, 15 Apr 2014 17:51:33 GMT

Muhammad

this sub is who save pdf

sub printRP01

printReportPDF "C:\18T.pdf"

ActiveDocument.GetApplication.Sleep 2000

ActiveDocument.PrintReport "RP01", "QlikViewPDF",False

ActiveDocument.GetApplication.Sleep 10000

end sub

in the example do not create you?

could you change the option that i put in the attached picture?

give me a print screen about your EDIT MODULE

Re: Sending QV object by e-mail using Macro

arsal_90 — Tue, 15 Apr 2014 17:55:03 GMT

Fernando

Have you find the image and i also allow system access

Re: Sending QV object by e-mail using Macro

arsal_90 — Tue, 15 Apr 2014 18:04:03 GMT

Hi Fernando

Have u checked the image.

Re: Re: Re: Re: Sending QV object by e-mail using Macro

fkeuroglian — Tue, 15 Apr 2014 18:24:32 GMT

Muhammad

i add to the example some new thing that let you export to JPG, could be that a permission an security problem and i want to know if you can export to jpg

if you can export , that means that you have the correct permision and security setings, and the problem is something the pdf editor and save.

tell me what happend

good luck

press the button IMG