Qlik Community

QlikView Publisher

Discussion Board for collaboration on QlikView Publisher.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Not applicable

Sending reports as email

How to send reports as email in QlikView from a company mail. I want to automate this using macros. Please help

8 Replies

Re: Sending reports as email

I would suggest you to go with the NPriting tool because managing the email system use macro for a large set of people would be painful and it may cause performance issue with that.

NPrinting is a Qlik owned product and its design to automate the export feature to all the formats and to automate the mailing system, its very dynamic .

NPrinting Quick Start Guide -Vizubi: Office and HTML Report Creation and Distribution SoftwareCreate...

asgardd2
Contributor III

Re: Sending reports as email

Hello!

You can use vbscript like this :

(change empty values usrname,e-mail, password to yours)

Function SendMail()


Dim objEmail

Const cdoSendUsingPort = 2 ' Send the message using SMTP
Const CdoReferenceTypeName = 1
Const cdoAnonymous = 1 'Do not authenticate
Const cdoBasic = 1 'basic (clear-text) authentication
Const cdoNTLM = 2 'NTLM


'SMTPServer = getVariable("emailSMTPServer")

'Const SMTPServer = "10.5.0.9" 'SMTP Server ID
Const SMTPServer = "10.3.0.10"
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/sendemailaddress") = "" 'your e-mail
.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") = cdoBasic
.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
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoAnonymous
'user name
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "" 'username!
' .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "aartikoya@capvent.com"

'Account Password
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "" 'password!
' .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "aarti"
.Update
'---------------------------------------------------------------------
End With

' objEmail.To = getVariable("emailTo") ' Email Recipient
' objEmail.From = getVariable("emailFrom") ' Email Sender
' objEmail.Subject = getVariable("emailSubject") ' Subject
' objEmail.TextBody = getVariable("emailBody") ' Text Body
' objEmail.AddAttachment getVariable("emailAttachment") ' Attachement
'
'
' objEmail.Send

With objEmail
Set .Configuration = objConf
.BodyPart.Charset = "utf-8" 
.From = "test@test.com"'gestori@iproject.ru
'.To="aalexandrov@invproject.ru"
.To = "test@test.com"'_dm_shops_all@invproject.ru,ANikolaevskij@invproject.ru,
.cc = ""'
.Subject = "Sales Report"
.AddAttachment "D:\Files\sales01.xls"
.HTMLBody = "<html>Good morning!<br /><br /></html>"


'.AddRelatedBodyPart "D:\Qlikview\Executive_Dashboard.png", CdoReferenceTypeName
.Send
End With




Set objFlds = Nothing
Set objConf = Nothing
Set objEmail = Nothing




end Function

But, the best way i think, is create powershell script and access to qlikview application as COM-object and export data from qlikview object to excel or pdf files. (learn APIguide.qvw application - this application install with qlikview) . Or use NPrinting software.

Not applicable

Re: Sending reports as email

Thank you. But is it possible to do with macros?

Re: Sending reports as email


its possiable using macro but it will have performance impact and for any changes you need to change the code and rewrite the macro again

Partner
Partner

Re: Sending reports as email

Hi

You can use VBS and batch scripts to send reports

HirisH_V7
Honored Contributor

Re: Sending reports as email

Hi,

check this creating this macro,you can see the comments and change their as per Your Requirement,

Sub SendReport

ActiveDocument.Sheets("SH01").Activate 'edit the sheet 

'ActiveDocument.reload

set objExcel=ActiveDocument.GetSheetObject("CH01") 'edit the sheet object

strDate = Year(Date()) &"-"& month(date()) &"-"& Day(Date()) 

filepath="D:\Excel_"&strDate&".xls"   'edit the location of file

objExcel.ExportBiff filepath

ActiveDocument.GetApplication.Sleep 5000

SendGMail

ActiveDocument.Save

set objExcel=nothing

set strDate=nothing

set filepath=nothing

End Sub

Sub SendGMail()

MsgBox "Report Exported Successfully"

strDate = Year(Date()) &"-"& month(date()) &"-"& Day(Date())  

filepath="D:\Excel_"&strDate&".xls"

Set objMsg = CreateObject("CDO.Message")

Set msgConf = CreateObject("CDO.Configuration")

'Server Configuration

msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"

msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "vikas@gmail.com" 'type your mail id

msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "**********" 'Type your acccount Password

msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = 1

msgConf.Fields.Update

'End With

objMsg.To = "To mail id" ' type to mail id

objMsg.From = "xxxs@gmail.com" 'type from mail id

objMsg.Subject = "Test mail"

objMsg.HTMLBody = "QlikView Test Mail"

'objMsg.AddAttachment "D:\Image.jpg"

objMsg.AddAttachment   filepath    ' Attachement object

objMsg.Sender = "Mr. Name"

Set objMsg.Configuration = msgConf

' Send

objMsg.Send

Msgbox("Email Sent Successfully")

' Clear

Set objMsg = nothing

Set msgConf = nothing

End Sub

Hope this helps in sending reports through mail,

PFA,

Hirish

HirisH
“Aspire to Inspire before we Expire!”
MVP & Luminary
MVP & Luminary

Re: Sending reports as email

Hi,

Check this link for sample macros for this

Useful Qlikview Macros

Regards,

Jagan.

vikasmahajan
Esteemed Contributor

Re: Sending reports as email

Macros are not the best practice please explore with N-Printing macros impact performance problems,

Vikas