When I use CDO to send email, the method works fine with Excel 2003
(although the rows of text are somewhat spaced further apart than the
original). But my Excel 2007, when receiving my CDO generated email
radically restructures the page. (Hidden password protected columns
become visible; words on rows are repositioned somewhat similar to
word wrap; etc.) The macro I use in my Excel 2003 to generate the
email was adapted from one Ron Debruin has on his website. I can only
assume I've neglected to address something of significance. The
adapted macro follows:

Private Sub Worksheet_Change(ByVal TargetCell As Range)
If Worksheets("DataBase").Range("R347").Value = 0 And Worksheets
("Record").Range("C21").Value 0 Then
Dim iMsg As Object
Dim iConf As Object
Dim ws As Worksheet
Dim Sourcewb As Workbook
Dim Flds As Variant
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
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") = Worksheets("Registration").Range("J38")
.Item("http://schemas.microsoft.com/cdo/configuration/
smtpserverport") = 25
.Update
End With
Set Sourcewb = ThisWorkbook
For Each ws In Sourcewb.Worksheets
If ws.Range("AF50").Value Like "?*@?*.?*" Then
With iMsg
Set .Configuration = iConf
.To = ws.Range("AF50").Value
.From = Worksheets("Record").Range("AC52")
.Subject = "The " & Worksheets("Record").Range
("C5") & " Project"
.HTMLBody = RangetoHTML(ws.UsedRange)
.Send
End With
Set iMsg = Nothing
End If
Next ws
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End If
End Sub

Any help would be much appreciated. Thanks.

Michael

Hi Michael

Not notice this, can you send me a example file private and tell me what is wrong


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Michael Lanier" wrote in message
...
When I use CDO to send email, the method works fine with Excel 2003
(although the rows of text are somewhat spaced further apart than the
original). But my Excel 2007, when receiving my CDO generated email
radically restructures the page. (Hidden password protected columns
become visible; words on rows are repositioned somewhat similar to
word wrap; etc.) The macro I use in my Excel 2003 to generate the
email was adapted from one Ron Debruin has on his website. I can only
assume I've neglected to address something of significance. The
adapted macro follows:

Private Sub Worksheet_Change(ByVal TargetCell As Range)
If Worksheets("DataBase").Range("R347").Value = 0 And Worksheets
("Record").Range("C21").Value 0 Then
Dim iMsg As Object
Dim iConf As Object
Dim ws As Worksheet
Dim Sourcewb As Workbook
Dim Flds As Variant
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
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") = Worksheets("Registration").Range("J38")
.Item("http://schemas.microsoft.com/cdo/configuration/
smtpserverport") = 25
.Update
End With
Set Sourcewb = ThisWorkbook
For Each ws In Sourcewb.Worksheets
If ws.Range("AF50").Value Like "?*@?*.?*" Then
With iMsg
Set .Configuration = iConf
.To = ws.Range("AF50").Value
.From = Worksheets("Record").Range("AC52")
.Subject = "The " & Worksheets("Record").Range
("C5") & " Project"
.HTMLBody = RangetoHTML(ws.UsedRange)
.Send
End With
Set iMsg = Nothing
End If
Next ws
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End If
End Sub

Any help would be much appreciated. Thanks.

Michael