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

[#1]

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