0 Replies Latest reply: Aug 22, 2012 12:20 PM by Srikar Chaduvu RSS

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

    Srikar Chaduvu

      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



          set objExcel=ActiveDocument.GetSheetObject("TB01")

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


          objExcel.ExportBiff filepath

          ActiveDocument.GetApplication.Sleep 5000



          set objExcel=nothing

          set strDate=nothing

          set filepath=nothing

      End Sub



      Sub SendMail()



      Dim objEmail

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


          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



          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


          Set objFlds = Nothing

          Set objConf = Nothing

          Set objEmail = Nothing



      End Sub