topic Re: Mail Configuration in QlikView

Mail Configuration

Thu, 27 Aug 2015 07:02:48 GMT

Hi All,

I need to send data in excel format through Mail.

Anyone help me with the configuration.

Regards,

Keerthi KS

Re: Mail Configuration

gautik92 — Thu, 27 Aug 2015 07:09:52 GMT

you have to use macros for that have a look at this

QlikView Macros – Useful collection | Lucian Cotea

Re: Mail Configuration

mohammadkhatimi — Thu, 27 Aug 2015 07:16:40 GMT

Hie...

This is what I have :

1. Install a PDF-printer

I installed "Bullzip PDF Printer" on the machine where the report runs.

=> Google is your friend

2. Make your QVW and a report, write down the reportname (RPxx)

3. In QV, enter the following script :

Sub zendrapport
ActiveDocument.reload
vReport = "RP02" 'Set report
vName = "transactierapportPDP" 'Name of output pdf
ActiveDocument.PrintReport(vReport), "Bullzip PDF Printer", false 'Printreport
reportFile = "c:\PDFs\Operations\" & vName &".pdf" 'Setting outputname
MyPrintPDFWithBullZip(reportFile) 'Call pdf printer
ActiveDocument.GetApplication.Sleep 5000
mailrapport
ActiveDocument.Save
ActiveDocument.GetApplication.Sleep 5000
ActiveDocument.GetApplication.Quit
End sub

This script has a connection to 2 other script, just copy/paste them and change some parameters

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 mailrapport()
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-address of your 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 = "username@domain.com" 'Email Recipient
objEmail.From = "REPORTS@domain.com" 'Email Sender
objEmail.Subject = "The name of the report" ' Subject
objEmail.TextBody = "some text in the body of the mail" 'Text Body
objEmail.AddAttachment "c:\PDFs\Operations\transactierapportPDP.pdf" ' Attachement
objEmail.Send
Set objFlds = Nothing
Set objConf = Nothing
Set objEmail = Nothing
end function

Make sure that security is set to SYSTEM, both settings.

Hope it works for you !!!

Re: Mail Configuration

Thu, 27 Aug 2015 07:17:12 GMT

Hi Gautham,

I am doing it for the first time. I never worked on server .

Can you please provide me in detail. What has to be done if i need to send data in excel format.

Thanks,

Keerthi KS

Re: Mail Configuration

jagan — Thu, 27 Aug 2015 07:18:45 GMT

Hi,

Check this link it has sample code, you can implement the same in your dashboard.

Useful Qlikview Macros

Regards,

Jagan.

Re: Mail Configuration

Thu, 27 Aug 2015 07:38:56 GMT

Hi Jagan,

Where do i run these scripts ?

Regards,

Keerthi KS

Re: Mail Configuration

jagan — Thu, 27 Aug 2015 08:06:19 GMT

Hi,

You need to create a macro using in Edit Module or Ctrl + K and trigger it on button click or wherever you required.

Regards,

jagan.

Re: Mail Configuration

Anonymous — Thu, 27 Aug 2015 08:08:37 GMT

It wud be Ctrl+M to edit the module..

Re: Mail Configuration

Thu, 27 Aug 2015 09:05:06 GMT

HI,

Export to Excel

FUNCTION ExcelExport(objID) set obj = ActiveDocument.GetSheetObject( objID ) w = obj.GetColumnCount if obj.GetRowCount>1001 then h=1000 else h=obj.GetRowCount end if Set objExcel = CreateObject("Excel.Application") objExcel.Workbooks.Add objExcel.Worksheets(1).select() objExcel.Visible = True set CellMatrix = obj.GetCells2(0,0,w,h) column = 1 for cc=0 to w-1 objExcel.Cells(1,column).Value = CellMatrix(0)(cc).Text objExcel.Cells(1,column).EntireRow.Font.Bold = True column = column +1 next c = 1 r =2 for RowIter=1 to h-1 for ColIter=0 to w-1 objExcel.Cells(r,c).Value = CellMatrix(RowIter)(ColIter).Text c = c +1 next r = r+1 c = 1 next END FUNCTION SUB CallExample ExcelExport( "CH01" ) END SUB

What are the changes should i do here. I am getting error "expected end"

If you have any sample qvw please attach it. it would be very helpful

Regards,

Keerthi KS