Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How to send reports as email in QlikView from a company mail. I want to automate this using macros. Please help
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 .
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.
Thank you. But is it possible to do with macros?
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
Hi
You can use VBS and batch scripts to send reports
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
Macros are not the best practice please explore with N-Printing macros impact performance problems,
Vikas