开发者

Excel VBA Error when adding attachments using CDO.Message "Object doesn't support this property or method"

开发者 https://www.devze.com 2023-01-07 01:49 出处:网络
VBA/Excel Macro Programming - I\'m trying to automate sending out an email with an Excel attachment.When I try and add an attachment, I get the following error: \"Object doesn\'t support this property

VBA/Excel Macro Programming - I'm trying to automate sending out an email with an Excel attachment. When I try and add an attachment, I get the following error: "Object doesn't support this property or method".

Does anyone have any ideas? It's not filename, filepath issues, the file exists, and the path is correct.

Dim wbOld As Workbook
Dim wbNew As Workbook
Dim sheet As Variant
Dim tempFilePath As String
Dim tempFullFileName As String
Dim sErr As String
Dim vSheet As Variant

'Handle Excel screen changes and events
With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

' Set Outlook to send emails
'Set olApp = New Outlook.Application

'Set this workbook to active
Set wbOld = ActiveWorkbook

'Copy sheets from the the old active workbook
wbOld.Sheets(SheetsToEmail).Copy

'Create copy destination workbook
Set wbNew = ActiveWorkbook

'Merge styles from the new workbook into the existing workbook.
wbNew.Colors = Workbooks(wbOld.Name).Colors

'Save the new workbook, mail it and finally delete it
tempFilePath = Environ$("temp") & "\"
tempFullFileName = tempFilePath & NewWorkbookName & ".xlsm"

'In case the workbook already exists, kill it.
'Kill tempFullFileName

'Save
With wbNew
    .SaveAs tempFullFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    .Close SaveChanges:=False
End With

sch = "http://schemas.microsoft.com/cdo/configuration/"
Set cdoConfig = CreateObject("CDO.Configuration")
cdoConfig.Fields.Item(sch & "sendusing") = 2
cdoConfig.Fields.Item(sch & "smtpserver") = "server.smtp.address"
cdoConfig.Fields.Update


Set cdoMessage = CreateObject("CDO.Message")
Set cdo开发者_开发技巧Message.Configuration = cdoConfig

cdoMessage.From = "test@email.com"
cdoMessage.To = "receive_Test@email.com"
'cdoMessage.CC = Send_CC
'cdoMessage.BCC = Send_BCC
cdoMessage.Subject = EmailSubject
cdoMessage.Textbody = EmailBody
cdoMessage.AddAttachement "C:\documents and settings\userAccount\Local%20Settings\Temp\GCCS%20Automated%20Dashboard%20-%202010.xlsm"

cdoMessage.Send
Set cdoMessage = Nothing
Set cdoConfig = Nothing

'Delete new
Kill tempFullFileName

'Set focus on old workbook
Set wbOld = ActiveWorkbook

Send_TO = Replace(Send_TO, ";", vbCrLf)

'Enable application events and draw
'With Application
'     .ScreenUpdating = True
'     .EnableEvents = True
'End With

'Clean up
Set wsNew = Nothing
'Set olApp = Nothing
'Set olMail = Nothing
Exit Sub


You spelled Attachment incorrectly.

0

精彩评论

暂无评论...
验证码 换一张
取 消