Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Outlook VB not working
Our PC at work when bad and was replaced witn a new one that is supposed be
exactly the same, but now I am getting a runtime error 429. when I debug it highlights this line Set OutApp = CreateObject("Outlook.Application") do you have any idea of what is wrong. It worked great until they replace our PC. I have included the code below. We are using 2003 Option Explicit Sub Mail_Sheet_Outlook_Body() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim rng As Range Dim OutApp As Object Dim OutMail As Object With Application .EnableEvents = False .ScreenUpdating = False End With Set rng = Nothing Set rng = ActiveSheet.UsedRange Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = " .CC = "" .BCC = "" .Subject = Range("B14") .HTMLBody = RangetoHTML(rng) .Send End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Outlook VB not working
Here is the FunctionModule that I didn't include earlier.
Option Explicit Function RangetoHTML(rng As Range) ' Changed by Ron de Bruin 28-Oct-2006 ' Working in Office 2000-2007 Dim fso As Object Dim ts As Object Dim TempFile As String Dim TempWB As Workbook TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 'Copy the range and create a new workbook to past the data in rng.Copy Set TempWB = Workbooks.Add(1) With TempWB.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial xlPasteValues, , False, False .Cells(1).PasteSpecial xlPasteFormats, , False, False .Cells(1).Select Application.CutCopyMode = False On Error Resume Next .DrawingObjects.Visible = True .DrawingObjects.Delete On Error GoTo 0 End With 'Publish the sheet to a htm file With TempWB.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=TempWB.Sheets(1).Name, _ Source:=TempWB.Sheets(1).UsedRange.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With 'Read all data from the htm file into RangetoHTML Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.readall ts.Close RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _ "align=left x:publishsource=") 'Close TempWB TempWB.Close savechanges:=False 'Delete the htm file we used in this function Kill TempFile Set ts = Nothing Set fso = Nothing Set TempWB = Nothing End Function Function GetBoiler(ByVal sFile As String) As String Dim fso As Object Dim ts As Object Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2) GetBoiler = ts.readall ts.Close End Function "tankerman" wrote: Our PC at work when bad and was replaced witn a new one that is supposed be exactly the same, but now I am getting a runtime error 429. when I debug it highlights this line Set OutApp = CreateObject("Outlook.Application") do you have any idea of what is wrong. It worked great until they replace our PC. I have included the code below. We are using 2003 Option Explicit Sub Mail_Sheet_Outlook_Body() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim rng As Range Dim OutApp As Object Dim OutMail As Object With Application .EnableEvents = False .ScreenUpdating = False End With Set rng = Nothing Set rng = ActiveSheet.UsedRange Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = " .CC = "" .BCC = "" .Subject = Range("B14") .HTMLBody = RangetoHTML(rng) .Send End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Outlook VB not working
Hi tankerman
Maybe a stupid question but is Outlook installed The code is not working with Outlook Express If you use O 2007 try this Run Office Diagnostics. Office ButtonExcel Options...Resources -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "tankerman" wrote in message ... Here is the FunctionModule that I didn't include earlier. Option Explicit Function RangetoHTML(rng As Range) ' Changed by Ron de Bruin 28-Oct-2006 ' Working in Office 2000-2007 Dim fso As Object Dim ts As Object Dim TempFile As String Dim TempWB As Workbook TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 'Copy the range and create a new workbook to past the data in rng.Copy Set TempWB = Workbooks.Add(1) With TempWB.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial xlPasteValues, , False, False .Cells(1).PasteSpecial xlPasteFormats, , False, False .Cells(1).Select Application.CutCopyMode = False On Error Resume Next .DrawingObjects.Visible = True .DrawingObjects.Delete On Error GoTo 0 End With 'Publish the sheet to a htm file With TempWB.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=TempWB.Sheets(1).Name, _ Source:=TempWB.Sheets(1).UsedRange.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With 'Read all data from the htm file into RangetoHTML Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.readall ts.Close RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _ "align=left x:publishsource=") 'Close TempWB TempWB.Close savechanges:=False 'Delete the htm file we used in this function Kill TempFile Set ts = Nothing Set fso = Nothing Set TempWB = Nothing End Function Function GetBoiler(ByVal sFile As String) As String Dim fso As Object Dim ts As Object Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2) GetBoiler = ts.readall ts.Close End Function "tankerman" wrote: Our PC at work when bad and was replaced witn a new one that is supposed be exactly the same, but now I am getting a runtime error 429. when I debug it highlights this line Set OutApp = CreateObject("Outlook.Application") do you have any idea of what is wrong. It worked great until they replace our PC. I have included the code below. We are using 2003 Option Explicit Sub Mail_Sheet_Outlook_Body() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim rng As Range Dim OutApp As Object Dim OutMail As Object With Application .EnableEvents = False .ScreenUpdating = False End With Set rng = Nothing Set rng = ActiveSheet.UsedRange Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = " .CC = "" .BCC = "" .Subject = Range("B14") .HTMLBody = RangetoHTML(rng) .Send End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Outlook VB not working
There are several people that use this computer, each logs on with their own
PIC code. All of the ones that use the notification is having the same problem. We can email the notification without the code by selecting email from the task bar and it emails fine that way but it is so much more convenient to just use the bottom to send it. We are using MS Outlook 2003 SP2 I think it probably has something to do with the way this computer is set up because this problem did not appear until we switched PC. This code has worked for over a year without any problems until now. "Ron de Bruin" wrote: Hi tankerman Maybe a stupid question but is Outlook installed The code is not working with Outlook Express If you use O 2007 try this Run Office Diagnostics. Office ButtonExcel Options...Resources -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "tankerman" wrote in message ... Here is the FunctionModule that I didn't include earlier. Option Explicit Function RangetoHTML(rng As Range) ' Changed by Ron de Bruin 28-Oct-2006 ' Working in Office 2000-2007 Dim fso As Object Dim ts As Object Dim TempFile As String Dim TempWB As Workbook TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 'Copy the range and create a new workbook to past the data in rng.Copy Set TempWB = Workbooks.Add(1) With TempWB.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial xlPasteValues, , False, False .Cells(1).PasteSpecial xlPasteFormats, , False, False .Cells(1).Select Application.CutCopyMode = False On Error Resume Next .DrawingObjects.Visible = True .DrawingObjects.Delete On Error GoTo 0 End With 'Publish the sheet to a htm file With TempWB.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=TempWB.Sheets(1).Name, _ Source:=TempWB.Sheets(1).UsedRange.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With 'Read all data from the htm file into RangetoHTML Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.readall ts.Close RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _ "align=left x:publishsource=") 'Close TempWB TempWB.Close savechanges:=False 'Delete the htm file we used in this function Kill TempFile Set ts = Nothing Set fso = Nothing Set TempWB = Nothing End Function Function GetBoiler(ByVal sFile As String) As String Dim fso As Object Dim ts As Object Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2) GetBoiler = ts.readall ts.Close End Function "tankerman" wrote: Our PC at work when bad and was replaced witn a new one that is supposed be exactly the same, but now I am getting a runtime error 429. when I debug it highlights this line Set OutApp = CreateObject("Outlook.Application") do you have any idea of what is wrong. It worked great until they replace our PC. I have included the code below. We are using 2003 Option Explicit Sub Mail_Sheet_Outlook_Body() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim rng As Range Dim OutApp As Object Dim OutMail As Object With Application .EnableEvents = False .ScreenUpdating = False End With Set rng = Nothing Set rng = ActiveSheet.UsedRange Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = " .CC = "" .BCC = "" .Subject = Range("B14") .HTMLBody = RangetoHTML(rng) .Send End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Outlook VB not working
Check out the info here
http://www.rondebruin.nl/mail/problems.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "tankerman" wrote in message ... There are several people that use this computer, each logs on with their own PIC code. All of the ones that use the notification is having the same problem. We can email the notification without the code by selecting email from the task bar and it emails fine that way but it is so much more convenient to just use the bottom to send it. We are using MS Outlook 2003 SP2 I think it probably has something to do with the way this computer is set up because this problem did not appear until we switched PC. This code has worked for over a year without any problems until now. "Ron de Bruin" wrote: Hi tankerman Maybe a stupid question but is Outlook installed The code is not working with Outlook Express If you use O 2007 try this Run Office Diagnostics. Office ButtonExcel Options...Resources -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "tankerman" wrote in message ... Here is the FunctionModule that I didn't include earlier. Option Explicit Function RangetoHTML(rng As Range) ' Changed by Ron de Bruin 28-Oct-2006 ' Working in Office 2000-2007 Dim fso As Object Dim ts As Object Dim TempFile As String Dim TempWB As Workbook TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 'Copy the range and create a new workbook to past the data in rng.Copy Set TempWB = Workbooks.Add(1) With TempWB.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial xlPasteValues, , False, False .Cells(1).PasteSpecial xlPasteFormats, , False, False .Cells(1).Select Application.CutCopyMode = False On Error Resume Next .DrawingObjects.Visible = True .DrawingObjects.Delete On Error GoTo 0 End With 'Publish the sheet to a htm file With TempWB.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=TempWB.Sheets(1).Name, _ Source:=TempWB.Sheets(1).UsedRange.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With 'Read all data from the htm file into RangetoHTML Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.readall ts.Close RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _ "align=left x:publishsource=") 'Close TempWB TempWB.Close savechanges:=False 'Delete the htm file we used in this function Kill TempFile Set ts = Nothing Set fso = Nothing Set TempWB = Nothing End Function Function GetBoiler(ByVal sFile As String) As String Dim fso As Object Dim ts As Object Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2) GetBoiler = ts.readall ts.Close End Function "tankerman" wrote: Our PC at work when bad and was replaced witn a new one that is supposed be exactly the same, but now I am getting a runtime error 429. when I debug it highlights this line Set OutApp = CreateObject("Outlook.Application") do you have any idea of what is wrong. It worked great until they replace our PC. I have included the code below. We are using 2003 Option Explicit Sub Mail_Sheet_Outlook_Body() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim rng As Range Dim OutApp As Object Dim OutMail As Object With Application .EnableEvents = False .ScreenUpdating = False End With Set rng = Nothing Set rng = ActiveSheet.UsedRange Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = " .CC = "" .BCC = "" .Subject = Range("B14") .HTMLBody = RangetoHTML(rng) .Send End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Outlook VB not working
Hi Ron, we have used this code in our EXCEL sheets (we have 4 excel sheets we
send several times a day) for over a year and it is a life saver I really appreciate you allowing it's use. The old saying "when all else fails read the instruction" well on my list of thing to check or do was 'detect and repair' I just done a 'detect and repair' on my EXCEL and now it is working just fine. I should have tried that before i came to the forum but you live and learn to step back and look before you leap. Thank you for your quick responce and advise. "tankerman" wrote: Our PC at work when bad and was replaced witn a new one that is supposed be exactly the same, but now I am getting a runtime error 429. when I debug it highlights this line Set OutApp = CreateObject("Outlook.Application") do you have any idea of what is wrong. It worked great until they replace our PC. I have included the code below. We are using 2003 Option Explicit Sub Mail_Sheet_Outlook_Body() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim rng As Range Dim OutApp As Object Dim OutMail As Object With Application .EnableEvents = False .ScreenUpdating = False End With Set rng = Nothing Set rng = ActiveSheet.UsedRange Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = " .CC = "" .BCC = "" .Subject = Range("B14") .HTMLBody = RangetoHTML(rng) .Send End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel working in with Outlook | New Users to Excel | |||
Reference Outlook in Excel2003, but not working in Excel2000. | Excel Programming | |||
Working with Outlook and Excel from Access | Excel Programming | |||
email from cells to outlook - code not working | Excel Programming | |||
Excel Macro working with Outlook | Excel Programming |