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