Email question - Ron deBruin

[Steph]

Hi Ron. I grabbed the below code from your website regarding e-mailing a
sheet from a workbook as an atachment using the CDO method. Grabbing the
sheet and saving it to a location works great. But when the atachment is
e-mailed, the sheet looks strange, and I get an excel error when I try to
open the attachment
"Errors were detected in file 'C:\folder\filename\.xls'
The following is a list of repairs:

Damage to the file was so extensive that repairs were not possible. Excel
attempted to recover your formulas and values, but some data may have been
lost or corrupted."

Any idea why it's doing that? Thanks!

Sub CDO_Send_ActiveSheet()' This example use late binding, you don't have to
set a reference' You must be online when you run the sub Dim iMsg As
Object
Dim iConf As Object
Dim WB1 As Workbook
Dim WB2 As Workbook
Dim WBname As String
' Dim Flds As Variant Application.ScreenUpdating = False
Set WB1 = ActiveWorkbook
ActiveSheet.Copy
'Other possibility's are
'Sheets("Sheet3").Copy
'Sheets(Array("Sheet1", "Sheet3")).Copy
Set WB2 = ActiveWorkbook WBname = "Part of " & WB1.Name & " " &
Format(Now, "dd-mm-yy h-mm-ss") & ".xls"
WB2.SaveAs "C:/" & WBname
' It will save the new file with the ActiveSheet in C:/ with a Date and
Time stamp
WB2.Close False Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration") ' iConf.Load -1
' CDO Source Defaults
' Set Flds = iConf.Fields
' With Flds
'
..Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
'
..Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "Fill
in your SMTP server here"
'
..Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
' .Update
' End With 'Check out the Tips section if you want to change the
..To and .TextBody
With iMsg
Set .Configuration = iConf
.To = "(e-mail address removed)"
.CC = ""
.BCC = ""
.From = """Ron"" <[email protected]>"
.Subject = "This is a test"
.TextBody = "Hi there"
.AddAttachment "C:/" & WBname
' You can add any file you want with this line .AddAttachment
"C:/Test.txt"
.Send
End With Kill "C:/" & WBname 'If you not want to delete the file
you send delete this line
Set iMsg = Nothing
Set iConf = Nothing
Set WB1 = Nothing
Set WB2 = Nothing
Application.ScreenUpdating = True
End Sub

 

[Ron de Bruin]

Hi Steph

Have you remove this line in your test macro
..TextBody = "Hi there"

If you do that you can't open the file
This is a bug in CDO

 

[Steph]

That did it. Thanks Ron!!!

Hi Steph

Have you remove this line in your test macro
.TextBody = "Hi there"

If you do that you can't open the file
This is a bug in CDO

 

[Ron de Bruin]

You can use this also if you don't want to have text in the body

..TextBody = ""