Mail Sending Issue

[#1]

Hi All,

I have issue while sending mail.

I want  to send as mail with excel as attachment.

So I have export the report through below Script

sub SmartExport

Path = "E:\"

FileName = "Sample3.xlsx"

FullPath=Path&FileName

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = False

set XLDoc = XLApp.Workbooks.Add

ActiveDocument.GetSheetObject("CH03").CopyTableToClipboard true

XLDoc.Sheets(1).Paste()

XLDoc.Sheets(1).Name = "Export"

XLDoc.SaveAs Path & FileName

MsgBox("Success!")

sendMail FullPath

end sub        

And I have written script to send a mail like below, but it gives error

Sub sendMail

    Dim objEmail

    Dim strMailTo

    path="E:\Sample3.xlsx"

    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

    Const SMTPServer = "Smtp server Name"

    Const SMTPPort = 25                 ' Port number for SMTP

    Const SMTPTimeout = 60              ' Timeout for SMTP in seconds

        'Send 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 = "kabilan.kumarasamy@domain.com"

        objEmail.From = "kabilan.kumarasamy@domain.com"

        objEmail.Subject = "Attachment"

        objEmail.TextBody = "This is automatic maill don't reply"

                objEmail.AddAttachment Path

        objEmail.Send

        Set objFlds = Nothing

        Set objConf = Nothing

        Set objEmail = Nothing

    msgbox ("Mail Sent")

end sub

sub SmartExport

I get a error like below

Note:  I am gettting one dialog box , when open a excel file directly.

Thanks in advance....  

Regards,

Kabilan K.

[#2 · Accepted solution]

Hi,

I need to add following as marked below

sub SmartExport

Path = "E:\"

FileName = "Sample3.xlsx"

FullPath=Path&FileName

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = False

set XLDoc = XLApp.Workbooks.Add

ActiveDocument.GetSheetObject("CH03").CopyTableToClipboard true

XLDoc.Sheets(1).Paste()

XLDoc.Sheets(1).Name = "Export"

XLDoc.SaveAs Path & FileName

MsgBox("Success!")

XLDoc.Close

XLApp.Quit

sendMail FullPath

end sub      

Regards,

Kabilan K.