topic Re: Exporting reports into excel and share the same file with users through the email! in QlikView

Exporting reports into excel and share the same file with users through the email!

buzzy996 — Thu, 09 Jul 2015 11:41:40 GMT

Hi ,

can any one help me on this?

I can able to export any chart object into excel through the macro, but how to share the same with users by configuring the email options from qlikview server or qlikview desktop?

NOTE: We don't have publisher and nprinting components right now to do the same.

Re: Exporting reports into excel and share the same file with users through the email!

ashfaq_haseeb — Thu, 09 Jul 2015 11:47:06 GMT

Hi,

check this posts

Script for Exporting and Sending email

https://community.qlik.com/message/260815#260815

Note: macros cannot be triggered via Qlikview server.

You have to use windows task scheduler to do the job.

Regards

ASHFAQ

Re: Exporting reports into excel and share the same file with users through the email!

Anonymous — Thu, 09 Jul 2015 12:00:55 GMT

Hi,

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

Re: Exporting reports into excel and share the same file with users through the email!

buzzy996 — Fri, 10 Jul 2015 16:17:22 GMT

hi,

can any one help me here,the below code I'm using to export my sheet object into excel and send to email!,

but some how I'm not received whichever the sheet object exported to excel through email!?

sub ExcelFile

strDate = CDate(Date)

strDay = DatePart("d", strDate)

strMonth = DatePart("m", strDate)

strYear = DatePart("yyyy", strDate)

If strDay < 10 Then

strDay = "0" & strDay

End If

If strMonth < 10 Then

strMonth = "0" & strMonth

End If

GetFormattedDate = strMonth & "-" & strDay & "-" & strYear

Path = "D:\ExportFiles\"

FileName = "Test_" & GetFormattedDate & ".xlsx"

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = true

set XLDoc = XLApp.Workbooks.Add

ActiveDocument.GetSheetObject("TB01").CopyTableToClipboard true

XLDoc.Sheets(1).Paste()

XLDoc.Sheets(1).Rows("1:3000").EntireRow.AutoFit

XLDoc.SaveAs Path & FileName

ActiveDocument.GetApplication.Sleep 5000

call mailrapport

ActiveDocument.Save

ActiveDocument.GetApplication.Sleep 5000

ActiveDocument.GetApplication.Quit

end sub

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 ="xxx.domain.com"

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 = "myemail@domain.com" 'Email Recipient

objEmail.From = "myemail_admin(having admit rights where i'm exeuting this code)@domain.com" 'Email Sender

objEmail.Subject = FileName ' Subject

objEmail.TextBody = FileName 'Text Body

objEmail.AddAttachments "D:\ExportFiles\Test_07-10-2015.xlsx" ' Attachement

objEmail.SEND

Set objFlds = Nothing

Set objConf = Nothing

Set objEmail = Nothing

end function

Note:my server ip is already amended on my smpt email server!