Problem with macro to email file using CDO
 

Dear all, I have taken some code I found on Ron DeBruins website, but
when I run it it flashes up "object required".
Any suggestions as to why this happens ?
FYI I am running the macro from a network location.
The to, from and subject lines are based on the values in A1, A2 and
A3.
Thanks in advance
Amy xx

Sub CDO_Mail_Every_Worksheet_File()
Dim iMsg As Object
Dim iConf As Object
Dim ws As Worksheet
Dim wb As Workbook
Dim WBname As String
' Dim Flds As Variant
Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets
If ws.Range("a1").Value Like "?*@?*.?*" Then
ws.Copy
Set wb = ActiveWorkbook
'' ChDir "S:\Files\temporary folder"
WBname = "S:\Files\temporary folder\ " & ws.Name & ".xls"
wb.SaveAs WBname
wb.Close False
Set wb = Nothing
Set iMsg = CreateObject("CDO.Message", "S:\scott\temporary
folder\" & ws.Name & ".xls")
With iMsg
Set .Configuration = iConf
..To = ws.Range("a1").Value
..From = s.Range("a2").Value
..Subject = "Sheet: " & ws.Name
..AddAttachment WBname
..TextBody = ws.Range("a3").Value
..Fields("urn:schemas:mailheader:X-Priority") = 1
..Send
End With
Set iMsg = Nothing
Kill WBname
End If
Next ws

Set iConf = Nothing
Application.ScreenUpdating = True
End Sub


--
AmyTaylor
------------------------------------------------------------------------
AmyTaylor's Profile: http://www.excelforum.com/member.php...o&userid=20970
View this thread: http://www.excelforum.com/showthread...hreadid=519648

Problem with macro to email file using CDO
 

this is what I use....

Private Sub SendMailCDOCacheConf(aTo, Subject, TextBody, aFrom)
Const cdoSendUsingPort = 2
Dim Conf

Set Conf = CreateObject("CDO.Configuration")

With Conf.Fields
.item("http://schemas.microsoft.com/cdo/configuration/sendusing") =
cdoSendUsingPort
.item("http://schemas.microsoft.com/cdo/configuration/smtpserver") =
"bhmail1.rivagecapital.com"
.Update
End With


Dim Message 'As New CDO.Message

'Create CDO message object
Set Message = CreateObject("CDO.Message")
With Message
'Set cached configuration
Set .Configuration = Conf

'Set email adress, subject And body
.To = aTo
.Subject = Subject
.TextBody = TextBody

'Set sender address If specified.
If Len(aFrom) 0 Then .From = aFrom

'Send the message
.send
End With
End Sub




"AmyTaylor" wrote:


Dear all, I have taken some code I found on Ron DeBruins website, but
when I run it it flashes up "object required".
Any suggestions as to why this happens ?
FYI I am running the macro from a network location.
The to, from and subject lines are based on the values in A1, A2 and
A3.
Thanks in advance
Amy xx

Sub CDO_Mail_Every_Worksheet_File()
Dim iMsg As Object
Dim iConf As Object
Dim ws As Worksheet
Dim wb As Workbook
Dim WBname As String
' Dim Flds As Variant
Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets
If ws.Range("a1").Value Like "?*@?*.?*" Then
ws.Copy
Set wb = ActiveWorkbook
'' ChDir "S:\Files\temporary folder"
WBname = "S:\Files\temporary folder\ " & ws.Name & ".xls"
wb.SaveAs WBname
wb.Close False
Set wb = Nothing
Set iMsg = CreateObject("CDO.Message", "S:\scott\temporary
folder\" & ws.Name & ".xls")
With iMsg
Set .Configuration = iConf
.To = ws.Range("a1").Value
.From = s.Range("a2").Value
.Subject = "Sheet: " & ws.Name
.AddAttachment WBname
.TextBody = ws.Range("a3").Value
.Fields("urn:schemas:mailheader:X-Priority") = 1
.Send
End With
Set iMsg = Nothing
Kill WBname
End If
Next ws

Set iConf = Nothing
Application.ScreenUpdating = True
End Sub


--
AmyTaylor
------------------------------------------------------------------------
AmyTaylor's Profile: http://www.excelforum.com/member.php...o&userid=20970
View this thread: http://www.excelforum.com/showthread...hreadid=519648