Emailing A Workbook - Can't get code to work

[Garratt]

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).

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

[markf]

Apparently, Windows 2000 and XP use the new and more powerful mail component CDOSYS, which is used in your example by Ron de Bruin, and Windows XP drops the older CDONTS altogether.

This may seem simple, but as well as applying your remote SMTP server's name are you applying the commented lines in the example by removing their leading apostrophe?

Your remote SMTP server will probably require a Username/Password, having read the MSDN page(*) supplied by Ron de Bruin's article, and another webpage(**), whilst I have little experience, I assume you will need:


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") = "mail.example.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

' Use if your SMTP server requires just an Account Name
' .Item("http://schemas.microsoft.com/cdo/configuration/smtpaccountname") = "My Name"

' Use if your SMTP server requires a Username/Password
' .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "domain\username"
' .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"

.Update
End With

* http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_iconfiguration_interface.asp
** http://www.experts-exchange.com/Databases/MS_Access/Q_20796278.html

[Garratt]

That worked, thanks. I didn't dig deep enough on that one.

[Iat]

Garratt,
Can you share your code on how you get it to work? I am looking for alternative to send email from within Excel. Unfortunately with the SP3, Outlook prevents other applications from send email on behalf of Outlook.

Thanks in advance,

PhiL

[RobDog888]

FYI: You could still use CDONTS if you are using Office XP or 2003.
CDO 1.21 is included in the full installation or if it is selected
during the custom install.