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

      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