0 Replies Latest reply: Apr 26, 2017 12:44 AM by Monica Geller RSS

    How to automatically send report as attachment from Qlikserver through Email

    Monica Geller

      Hi,

       

      I need to send report as an excel attachment.

      I am using below macro code.

       

       

      Sub VehicleStock
      on error resume next
      SET Doc = ActiveDocument
      'Assign Inv_year field to FieldName, because the report generates for each Inv_year
      SET f = Doc.Variables("vInc_Country22")

      DIM Var1
      Var1="VehicleStock"
      FieldName = f.GetContent.STRING 

      Doc.Fields(FieldName).Clear

      SET Field = Doc.Fields(FieldName).GetPossibleValues(100000)
      For i=0 to Field.Count-1
      Doc.Fields(FieldName).SELECT Field.Item(i).Text




      Doc.GetApplication.WaitForIdle
      Path = "E:\BSC_Test\Application\ExcelTask\"&Var1&"_"&Field.Item(i).Text&".xls"
      ' Path = "E:\Qllikview\BSC dashboard\Incadea Excel\"&Field.Item(i).Text&".xls"
      ' Path = "E:\Qllikview\BSC dashboard\" &Field.Item(i).Text &"_" &Field1.Item(j).Text &"_" &Field2.Item(k).Text &".xls"
      set obj = Doc.GetSheetObject("CH2662")
      obj.ExportBiff Path
      Next



      'adressat=inputbox("Please insert the receivers email address")
      'timestamp1 = Timer
      'Timestamp="-"&Year(now())&"-"&month(now())&"-"&day(now())&"--"&hour(now())&"-"&minute(now())&"-"&second(now())
      Dim objEmail
      Const cdoSendUsingPort = 2
      Const cdoBasicAuth =1
      Const cdoTimeout = 60
      Const cdoNTLM = 2
      Const mailServer = "154.53.30.234"     -->>it is my smtp server IP
      Const SMTPport =25                             -->>it is my smtp port no
      'msgbox "email is sending22"
      mailusername = "abc@companydomain.com"
      mailpassword = "abc123"
      mailto ="xyz@companydomain.com"
      mailSubject = "Title"&Timestamp
      mailBody = "Attached the actual Report"
      Set objEmail = CreateObject("CDO.Message")
      Set objConf = objEmail.Configuration
      Set objFlds = objConf.Fields

      'msgbox "email is sending"
      With objFlds
      objConf.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
      objConf.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = mailServer
      objConf.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = SMTPport
      objConf.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = TRUE
      objConf.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = cdoTimeout
      objConf.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasicAuth
      objConf.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = mailusername
      objConf.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = mailpassword
      objConf.Update
      End With
      objEmail.To = mailto
      objEmail.From = mailusername
      objEmail.Subject = mailSubject
      objEmail.TextBody = mailBody
      objEmail.AddAttachment "E:\BSC_Test\Application\ExcelTask\VehicleStock_1.xls"
      objEmail.AddAttachment "E:\BSC_Test\Application\ExcelTask\VehicleStock_2.xls"
      objEmail.AddAttachment "E:\BSC_Test\Application\ExcelTask\VehicleStock_3.xls"
       



      objEmail.Send
      Set objFlds = Nothing
      Set objConf = Nothing
      Set objEmail = Nothing
      'timestamp2 = Timer 
      'MsgBox("The E-Mail to"&" "&adressat&""&" was sent."&" "&"Sending took "&" "&Round((timestamp2-timestamp1)/60, 2)&" "&"min .")

      End Sub

       

      Please find screenshot of code as well.

       

      Thanks in advance!!