Problem with macro to email file using CDO

A

AmyTaylor

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
 
P

Patrick Molloy

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top