Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Achieved export to excel and sending it by mail, but after exporting to excel i want to zip it and send using macro

Hi All,

I am facing issues in coding for zipping an exported excel file using winzip before sending it over mail. Let me know if anyone finds a solution.

Below is the successful code for export to excel and then email.

Sub SendReport

ActiveDocument.Sheets("SH02").Activate

    ActiveDocument.reload

    set objExcel=ActiveDocument.GetSheetObject("TB01")

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

    filepath="C:\PDFs\Excel_"&strDate&".xls"

    objExcel.ExportBiff filepath

    ActiveDocument.GetApplication.Sleep 5000

    SendMail

    ActiveDocument.Save

    set objExcel=nothing

    set strDate=nothing

    set filepath=nothing

End Sub

Sub SendMail()

Dim objEmail

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

    filepath="C:\PDFs\Excel_"&strDate&".xls"

    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 = "SMTP Server Address"

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

    objEmail.From = "sender@domain.com"                        'Email Sender

    objEmail.Subject = "The name of the report"                        ' Subject

    objEmail.TextBody = "some text in the body of the mail"        'Text Body         

    objEmail.AddAttachment   filepath    ' Attachement

    objEmail.Send

    Set objFlds = Nothing

    Set objConf = Nothing

    Set objEmail = Nothing

End Sub

Regards,

Srikar