Mail Configuration

[#1]

Hi All,

I need to send data in excel format through Mail.

Anyone help me with the configuration.

Regards,

Keerthi KS

[gautik92]

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

QlikView Macros – Useful collection | Lucian Cotea

[mohammadkhatimi]

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 :

1. Sub zendrapport 
2.     ActiveDocument.reload 
3.     vReport = "RP02" 'Set report 
4.     vName = "transactierapportPDP" 'Name of output pdf 
5.     ActiveDocument.PrintReport(vReport), "Bullzip PDF Printer", false  'Printreport 
6.     reportFile = "c:\PDFs\Operations\" & vName &".pdf" 'Setting outputname 
7.     MyPrintPDFWithBullZip(reportFile) 'Call pdf printer 
8.     ActiveDocument.GetApplication.Sleep 5000 
9.     mailrapport   
10.     ActiveDocument.Save 
11.     ActiveDocument.GetApplication.Sleep 5000 
12.     ActiveDocument.GetApplication.Quit 
13. End sub 

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

1. FUNCTION MyPrintPDFWithBullZip (pdfOutputFile) 
2.  
3.     set obj = CreateObject("Bullzip.PDFPrinterSettings") 
4.     obj.SetValue "Output" , pdfOutputFile 
5.     obj.SetValue "ConfirmOverwrite", "no" 
6.     obj.SetValue "ShowSaveAS", "never" 
7.     obj.SetValue "ShowSettings", "never" 
8.     obj.SetValue "ShowPDF", "no" 
9.     obj.SetValue "RememberLastFileName", "no" 
10.     obj.SetValue "RememberLastFolderName", "no" 
11.     obj.SetValue "ShowProgressFinished", "no" 
12.     obj.SetValue "ShowProgress", "no" 
13.     obj.WriteSettings True 
14.  
15. END FUNCTION  

1. function mailrapport() 
2.     Dim objEmail 
3.     Const cdoSendUsingPort = 2     ' Send the message using SMTP 
4.     Const cdoAnonymous = 0     'Do not authenticate 
5.     Const cdoBasic = 1         'basic (clear-text) authentication 
6.     Const cdoNTLM = 2         'NTLM 
7.     SMTPServer = "IP-address of your mailserver" 
8.     Const SMTPPort = 25                 ' Port number for SMTP 
9.     Const SMTPTimeout = 60              ' Timeout for SMTP in seconds 
10.     'Sending mail 
11.     Set objEmail = CreateObject("CDO.Message") 
12.     Set objConf = objEmail.Configuration 
13.     Set objFlds = objConf.Fields 
14.     With objFlds 
15.         '--------------------------------------------------------------------- 
16.            ' SMTP server details 
17.            .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort 
18.            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer 
19.            .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoAnonymous  
20.         .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = SMTPPort 
21.         .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False 
22.           .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = SMTPTimeout 
23.            .Update 
24.         '--------------------------------------------------------------------- 
25.     End With 
26.     objEmail.To = "username@domain.com"        'Email Recipient 
27.     objEmail.From = "REPORTS@domain.com"                        'Email Sender 
28.     objEmail.Subject = "The name of the report"                        ' Subject 
29.     objEmail.TextBody = "some text in the body of the mail"        'Text Body           
30.     objEmail.AddAttachment   "c:\PDFs\Operations\transactierapportPDP.pdf"    ' Attachement 
31.     objEmail.Send 
32.     Set objFlds = Nothing 
33.     Set objConf = Nothing 
34.     Set objEmail = Nothing 
35. end function 

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

Hope it works for you !!!

[#4]

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

[jagan]

Hi,

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

Useful Qlikview Macros

Regards,

Jagan.

[#6]

Hi Jagan,

Where do i run these scripts ?

Regards,

Keerthi KS

[jagan]

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.

[Anonymous]

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

[#9]

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