Formatting issue when sending e-mail via CDO to Excel 2007
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 |
Formatting issue when sending e-mail via CDO to Excel 2007
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 |
All times are GMT +1. The time now is 02:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com