At work (Excel97) I do not seem to have the "outlook" reference. Then only way I've found to email a worksheet is by using the primitive "activeworkbook.Mailer.xxxxx". It works OK but is missing options I need, mainly adding text to the message body with the attachment. The only thing you have control over is the the subject line and who the email is sent to.

I'd really just be happy if I could get something working that would let me put text in the message body along with the attachment. Even in Excel XP at home I don't see that "outlook" reference that most email examples refer to.


If at all possible there's this code I found and would love to use but I can not get it working. It would save me some time because supposedly it works without and email client active. It always fails on ".send" with an SMTP failure. In the code I trying adding my home SMTP server address but had no luck (I still get a SMTP failure)

This is exactly what I added in: "smtp.sbcglobal.yahoo.com"
(my own home email)

The error looks like "The message could not be sent to the SMTP server.... the server reponse was not available."

http://www.rondebruin.nl/cdo.htm#Workbook

I took this example and un-commented the SMTP section and added in my home SMTP server from above (I'm at home now and using a copy of Excel97).

Code:
Sub CDO_Send_Workbook()
' 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 wb As Workbook
    Dim WBname As String
    '    Dim Flds As Variant
 
    Application.ScreenUpdating = False
    Set wb = ActiveWorkbook
    ' Or use this Set wb = ThisWorkbook
    WBname = wb.Name & " " & Format(Now, "dd-mm-yy h-mm-ss") & ".xls"
    wb.SaveCopyAs "C:/" & WBname
    ' It will save a copy of the file in C:/ with a Date and Time stamp
 
    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 = "[email protected]"
        .CC = ""
        .BCC = ""
        .From = """Ron"" <[email protected]>"
        .Subject = "This is a test"
        .TextBody = "This is the body text"
        .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 wb = Nothing
    Application.ScreenUpdating = True
End Sub