Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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