Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
avinashelite

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
Creator III
Creator III

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
Author

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

avinashelite


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

krishnacbe
Partner - Specialist III
Partner - Specialist III

Hi

You can use VBS and batch scripts to send reports

HirisH_V7
Master
Master

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!”
jagan
Luminary Alumni
Luminary Alumni

Hi,

Check this link for sample macros for this

Useful Qlikview Macros

Regards,

Jagan.

vikasmahajan

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

Vikas

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.