Emailing A Workbook - Can't get code to work
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
