|
Excel VBA toggle Outlook '03 Word email editor on/off
Hello,
I have checked Ron's website on how to email from Excel (using VBA) a range of cells using RangeToHTML function; (http://www.rondebruin.nl/mail/folder3/mail4.htm) and the code works. The caveat for the code is 'Use Microsoft Office Word 2003 to edit e-mail messages' has to be unchecked in order for the script to run properly. What I am attempting to do is build a function (aka check to see if ..) inside the code that Ron has developed to determine if the end user has that option selected or not. if they do have Word '03 set as their text editor is to turn that off, finish the code and then if needed to turn it back on (if that is Applicable or not). I have found a reference through the MSDN site (http://msdn.microsoft.com/en-us/libr...ffice.11).aspx) for 'IsWordMail' method but I am unable to get my code to return a True / False answer. I am getting 'Run-time error '424' Object required error msg when I attempt to run the below code. The debug puts me to my Dim statement for TestOutlookEditor. Below I have the code that I am working with (I have excluded the RangeToHTML() for space) Sub TestHTMLEmailEditor() ' This creates an email from Excel using Outlook 2003 . Dim TestHTMLString As String Dim TestHTMLString2 As String Dim rng As Range ' Test variable to check Outlook msg formatting .. currently not working Set TestOutlookEditor = OutlookApp(ActiveInspector.IsWordEditor) Set OutlookApp = CreateObject("Outlook.Application") Set MItem = OutlookApp.CreateItem(olMailItem) Set rng = Nothing On Error Resume Next 'Only the visible cells in the selection Set rng = Range("B4:C10") ' This is a test range of cells 'You can also use a range if you want 'Set rng = Sheets("YourSheet").Range("D4:D12").SpecialCells(x lCellTypeVisible) On Error GoTo 0 If rng Is Nothing Then MsgBox "The selection is not a range or the sheet is protected" & _ vbNewLine & "please correct and try again.", vbOKOnly Exit Sub End If ' Below is the section of code I am unable to get to work ' ********************** If TestEditor.IsWordEditor = True Then MsgBox ("Pass") ' confirmation from Excel if value is true Else MsgBox ("Failed") ' confirmation from Excel if value is False End If ' ********************* End of Test code TestHTMLString = "<font face=Arial<font size=2<color=#000000Hello Everyone,<br /<br /" & _ "<span style=background-color:#FF0000<font color=#FFFFFFRed</span" & _ "<font color=#4B0082 = Missed Due Date.<br / " & _ "<font color=#FF00FFTesting<br /" & _ "<font color=#000000<dir" & _ "<liLine <b2</b</dir<br /" & _ "Testing new line" TestHTMLString2 = "<font color=#FF00FFTesting next section <br /" With MItem .To = "" .CC = "" .Subject = "Test Email using HTML on " & Format(Now, "dddd mm/dd/yy") .htmlBody = TestHTMLString & RangetoHTML(rng) & TestHTMLString2 .Display ' This allows the email to show vs auto sending End With Set OutlookApp = Nothing Set MItem = Nothing End Sub |
Excel VBA toggle Outlook '03 Word email editor on/off
Ron,
Thank you for the quick reply and the RangeToHTML code in the first place; it has helped out a lot. If you omit the section of code that I can't get to work; (the If .. Then statement and comment out my Dim statement) your code works great. I am trying to program around the fact that I am not always the one that gets to execute the code and while I was testing the VBA script I would have the email that was generated not display the HTML correctly. I.E. TestHTMLString1 & 2 don't appear but the 'RangetoHTML' part does (assuming that there is data inside Range("B4:C10") in the first place.) Let me know if you manage to tackle this one, Thank you again for your time, "Ron de Bruin" wrote: Hi J Smith 555 The code from my site is working OK with this setting on an off This is the problem TestHTMLString & RangetoHTML(rng) & TestHTMLString2 It is on my list to test and find a good solution -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "J Smith 555" <J Smith wrote in message ... Hello, I have checked Ron's website on how to email from Excel (using VBA) a range of cells using RangeToHTML function; (http://www.rondebruin.nl/mail/folder3/mail4.htm) and the code works. The caveat for the code is 'Use Microsoft Office Word 2003 to edit e-mail messages' has to be unchecked in order for the script to run properly. What I am attempting to do is build a function (aka check to see if ..) inside the code that Ron has developed to determine if the end user has that option selected or not. if they do have Word '03 set as their text editor is to turn that off, finish the code and then if needed to turn it back on (if that is Applicable or not). I have found a reference through the MSDN site (http://msdn.microsoft.com/en-us/libr...ffice.11).aspx) for 'IsWordMail' method but I am unable to get my code to return a True / False answer. I am getting 'Run-time error '424' Object required error msg when I attempt to run the below code. The debug puts me to my Dim statement for TestOutlookEditor. Below I have the code that I am working with (I have excluded the RangeToHTML() for space) Sub TestHTMLEmailEditor() ' This creates an email from Excel using Outlook 2003 . Dim TestHTMLString As String Dim TestHTMLString2 As String Dim rng As Range ' Test variable to check Outlook msg formatting .. currently not working Set TestOutlookEditor = OutlookApp(ActiveInspector.IsWordEditor) Set OutlookApp = CreateObject("Outlook.Application") Set MItem = OutlookApp.CreateItem(olMailItem) Set rng = Nothing On Error Resume Next 'Only the visible cells in the selection Set rng = Range("B4:C10") ' This is a test range of cells 'You can also use a range if you want 'Set rng = Sheets("YourSheet").Range("D4:D12").SpecialCells(x lCellTypeVisible) On Error GoTo 0 If rng Is Nothing Then MsgBox "The selection is not a range or the sheet is protected" & _ vbNewLine & "please correct and try again.", vbOKOnly Exit Sub End If ' Below is the section of code I am unable to get to work ' ********************** If TestEditor.IsWordEditor = True Then MsgBox ("Pass") ' confirmation from Excel if value is true Else MsgBox ("Failed") ' confirmation from Excel if value is False End If ' ********************* End of Test code TestHTMLString = "<font face=Arial<font size=2<color=#000000Hello Everyone,<br /<br /" & _ "<span style=background-color:#FF0000<font color=#FFFFFFRed</span" & _ "<font color=#4B0082 = Missed Due Date.<br / " & _ "<font color=#FF00FFTesting<br /" & _ "<font color=#000000<dir" & _ "<liLine <b2</b</dir<br /" & _ "Testing new line" TestHTMLString2 = "<font color=#FF00FFTesting next section <br /" With MItem .To = "" .CC = "" .Subject = "Test Email using HTML on " & Format(Now, "dddd mm/dd/yy") .htmlBody = TestHTMLString & RangetoHTML(rng) & TestHTMLString2 .Display ' This allows the email to show vs auto sending End With Set OutlookApp = Nothing Set MItem = Nothing End Sub |
Excel VBA toggle Outlook '03 Word email editor on/off
Hi J Smith 555
I hope I have time soon but Excel 2007 eat all my time on this moment It is on top of the list -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "J Smith 555" wrote in message ... Ron, Thank you for the quick reply and the RangeToHTML code in the first place; it has helped out a lot. If you omit the section of code that I can't get to work; (the If .. Then statement and comment out my Dim statement) your code works great. I am trying to program around the fact that I am not always the one that gets to execute the code and while I was testing the VBA script I would have the email that was generated not display the HTML correctly. I.E. TestHTMLString1 & 2 don't appear but the 'RangetoHTML' part does (assuming that there is data inside Range("B4:C10") in the first place.) Let me know if you manage to tackle this one, Thank you again for your time, "Ron de Bruin" wrote: Hi J Smith 555 The code from my site is working OK with this setting on an off This is the problem TestHTMLString & RangetoHTML(rng) & TestHTMLString2 It is on my list to test and find a good solution -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "J Smith 555" <J Smith wrote in message ... Hello, I have checked Ron's website on how to email from Excel (using VBA) a range of cells using RangeToHTML function; (http://www.rondebruin.nl/mail/folder3/mail4.htm) and the code works. The caveat for the code is 'Use Microsoft Office Word 2003 to edit e-mail messages' has to be unchecked in order for the script to run properly. What I am attempting to do is build a function (aka check to see if ..) inside the code that Ron has developed to determine if the end user has that option selected or not. if they do have Word '03 set as their text editor is to turn that off, finish the code and then if needed to turn it back on (if that is Applicable or not). I have found a reference through the MSDN site (http://msdn.microsoft.com/en-us/libr...ffice.11).aspx) for 'IsWordMail' method but I am unable to get my code to return a True / False answer. I am getting 'Run-time error '424' Object required error msg when I attempt to run the below code. The debug puts me to my Dim statement for TestOutlookEditor. Below I have the code that I am working with (I have excluded the RangeToHTML() for space) Sub TestHTMLEmailEditor() ' This creates an email from Excel using Outlook 2003 . Dim TestHTMLString As String Dim TestHTMLString2 As String Dim rng As Range ' Test variable to check Outlook msg formatting .. currently not working Set TestOutlookEditor = OutlookApp(ActiveInspector.IsWordEditor) Set OutlookApp = CreateObject("Outlook.Application") Set MItem = OutlookApp.CreateItem(olMailItem) Set rng = Nothing On Error Resume Next 'Only the visible cells in the selection Set rng = Range("B4:C10") ' This is a test range of cells 'You can also use a range if you want 'Set rng = Sheets("YourSheet").Range("D4:D12").SpecialCells(x lCellTypeVisible) On Error GoTo 0 If rng Is Nothing Then MsgBox "The selection is not a range or the sheet is protected" & _ vbNewLine & "please correct and try again.", vbOKOnly Exit Sub End If ' Below is the section of code I am unable to get to work ' ********************** If TestEditor.IsWordEditor = True Then MsgBox ("Pass") ' confirmation from Excel if value is true Else MsgBox ("Failed") ' confirmation from Excel if value is False End If ' ********************* End of Test code TestHTMLString = "<font face=Arial<font size=2<color=#000000Hello Everyone,<br /<br /" & _ "<span style=background-color:#FF0000<font color=#FFFFFFRed</span" & _ "<font color=#4B0082 = Missed Due Date.<br / " & _ "<font color=#FF00FFTesting<br /" & _ "<font color=#000000<dir" & _ "<liLine <b2</b</dir<br /" & _ "Testing new line" TestHTMLString2 = "<font color=#FF00FFTesting next section <br /" With MItem .To = "" .CC = "" .Subject = "Test Email using HTML on " & Format(Now, "dddd mm/dd/yy") .htmlBody = TestHTMLString & RangetoHTML(rng) & TestHTMLString2 .Display ' This allows the email to show vs auto sending End With Set OutlookApp = Nothing Set MItem = Nothing End Sub |
Excel VBA toggle Outlook '03 Word email editor on/off
Hi Ron,
I also would be very interested to see a workaround to this. I'm trying to use your code as well for one of my projects. It would be great if there was a way to turn off the Word editor, run the code, then turn the Word editor back on. I'm a VBA newbie, however. I know just enough to make sense of other peoples' code, but not enough yet to write my own from scratch. So, thank you so much for sharing your genius with us... "Ron de Bruin" wrote: Hi J Smith 555 I hope I have time soon but Excel 2007 eat all my time on this moment It is on top of the list -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "J Smith 555" wrote in message ... Ron, Thank you for the quick reply and the RangeToHTML code in the first place; it has helped out a lot. If you omit the section of code that I can't get to work; (the If .. Then statement and comment out my Dim statement) your code works great. I am trying to program around the fact that I am not always the one that gets to execute the code and while I was testing the VBA script I would have the email that was generated not display the HTML correctly. I.E. TestHTMLString1 & 2 don't appear but the 'RangetoHTML' part does (assuming that there is data inside Range("B4:C10") in the first place.) Let me know if you manage to tackle this one, Thank you again for your time, "Ron de Bruin" wrote: Hi J Smith 555 The code from my site is working OK with this setting on an off This is the problem TestHTMLString & RangetoHTML(rng) & TestHTMLString2 It is on my list to test and find a good solution -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "J Smith 555" <J Smith wrote in message ... Hello, I have checked Ron's website on how to email from Excel (using VBA) a range of cells using RangeToHTML function; (http://www.rondebruin.nl/mail/folder3/mail4.htm) and the code works. The caveat for the code is 'Use Microsoft Office Word 2003 to edit e-mail messages' has to be unchecked in order for the script to run properly. What I am attempting to do is build a function (aka check to see if ..) inside the code that Ron has developed to determine if the end user has that option selected or not. if they do have Word '03 set as their text editor is to turn that off, finish the code and then if needed to turn it back on (if that is Applicable or not). I have found a reference through the MSDN site (http://msdn.microsoft.com/en-us/libr...ffice.11).aspx) for 'IsWordMail' method but I am unable to get my code to return a True / False answer. I am getting 'Run-time error '424' Object required error msg when I attempt to run the below code. The debug puts me to my Dim statement for TestOutlookEditor. Below I have the code that I am working with (I have excluded the RangeToHTML() for space) Sub TestHTMLEmailEditor() ' This creates an email from Excel using Outlook 2003 . Dim TestHTMLString As String Dim TestHTMLString2 As String Dim rng As Range ' Test variable to check Outlook msg formatting .. currently not working Set TestOutlookEditor = OutlookApp(ActiveInspector.IsWordEditor) Set OutlookApp = CreateObject("Outlook.Application") Set MItem = OutlookApp.CreateItem(olMailItem) Set rng = Nothing On Error Resume Next 'Only the visible cells in the selection Set rng = Range("B4:C10") ' This is a test range of cells 'You can also use a range if you want 'Set rng = Sheets("YourSheet").Range("D4:D12").SpecialCells(x lCellTypeVisible) On Error GoTo 0 If rng Is Nothing Then MsgBox "The selection is not a range or the sheet is protected" & _ vbNewLine & "please correct and try again.", vbOKOnly Exit Sub End If ' Below is the section of code I am unable to get to work ' ********************** If TestEditor.IsWordEditor = True Then MsgBox ("Pass") ' confirmation from Excel if value is true Else MsgBox ("Failed") ' confirmation from Excel if value is False End If ' ********************* End of Test code TestHTMLString = "<font face=Arial<font size=2<color=#000000Hello Everyone,<br /<br /" & _ "<span style=background-color:#FF0000<font color=#FFFFFFRed</span" & _ "<font color=#4B0082 = Missed Due Date.<br / " & _ "<font color=#FF00FFTesting<br /" & _ "<font color=#000000<dir" & _ "<liLine <b2</b</dir<br /" & _ "Testing new line" TestHTMLString2 = "<font color=#FF00FFTesting next section <br /" With MItem .To = "" .CC = "" .Subject = "Test Email using HTML on " & Format(Now, "dddd mm/dd/yy") .htmlBody = TestHTMLString & RangetoHTML(rng) & TestHTMLString2 .Display ' This allows the email to show vs auto sending End With Set OutlookApp = Nothing Set MItem = Nothing End Sub |
Excel VBA toggle Outlook '03 Word email editor on/off
Hi NewVisioUser
I will try to work on it this weekend -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "NewVisioUser" wrote in message ... Hi Ron, I also would be very interested to see a workaround to this. I'm trying to use your code as well for one of my projects. It would be great if there was a way to turn off the Word editor, run the code, then turn the Word editor back on. I'm a VBA newbie, however. I know just enough to make sense of other peoples' code, but not enough yet to write my own from scratch. So, thank you so much for sharing your genius with us... "Ron de Bruin" wrote: Hi J Smith 555 I hope I have time soon but Excel 2007 eat all my time on this moment It is on top of the list -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "J Smith 555" wrote in message ... Ron, Thank you for the quick reply and the RangeToHTML code in the first place; it has helped out a lot. If you omit the section of code that I can't get to work; (the If .. Then statement and comment out my Dim statement) your code works great. I am trying to program around the fact that I am not always the one that gets to execute the code and while I was testing the VBA script I would have the email that was generated not display the HTML correctly. I.E. TestHTMLString1 & 2 don't appear but the 'RangetoHTML' part does (assuming that there is data inside Range("B4:C10") in the first place.) Let me know if you manage to tackle this one, Thank you again for your time, "Ron de Bruin" wrote: Hi J Smith 555 The code from my site is working OK with this setting on an off This is the problem TestHTMLString & RangetoHTML(rng) & TestHTMLString2 It is on my list to test and find a good solution -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "J Smith 555" <J Smith wrote in message ... Hello, I have checked Ron's website on how to email from Excel (using VBA) a range of cells using RangeToHTML function; (http://www.rondebruin.nl/mail/folder3/mail4.htm) and the code works. The caveat for the code is 'Use Microsoft Office Word 2003 to edit e-mail messages' has to be unchecked in order for the script to run properly. What I am attempting to do is build a function (aka check to see if ..) inside the code that Ron has developed to determine if the end user has that option selected or not. if they do have Word '03 set as their text editor is to turn that off, finish the code and then if needed to turn it back on (if that is Applicable or not). I have found a reference through the MSDN site (http://msdn.microsoft.com/en-us/libr...ffice.11).aspx) for 'IsWordMail' method but I am unable to get my code to return a True / False answer. I am getting 'Run-time error '424' Object required error msg when I attempt to run the below code. The debug puts me to my Dim statement for TestOutlookEditor. Below I have the code that I am working with (I have excluded the RangeToHTML() for space) Sub TestHTMLEmailEditor() ' This creates an email from Excel using Outlook 2003 . Dim TestHTMLString As String Dim TestHTMLString2 As String Dim rng As Range ' Test variable to check Outlook msg formatting .. currently not working Set TestOutlookEditor = OutlookApp(ActiveInspector.IsWordEditor) Set OutlookApp = CreateObject("Outlook.Application") Set MItem = OutlookApp.CreateItem(olMailItem) Set rng = Nothing On Error Resume Next 'Only the visible cells in the selection Set rng = Range("B4:C10") ' This is a test range of cells 'You can also use a range if you want 'Set rng = Sheets("YourSheet").Range("D4:D12").SpecialCells(x lCellTypeVisible) On Error GoTo 0 If rng Is Nothing Then MsgBox "The selection is not a range or the sheet is protected" & _ vbNewLine & "please correct and try again.", vbOKOnly Exit Sub End If ' Below is the section of code I am unable to get to work ' ********************** If TestEditor.IsWordEditor = True Then MsgBox ("Pass") ' confirmation from Excel if value is true Else MsgBox ("Failed") ' confirmation from Excel if value is False End If ' ********************* End of Test code TestHTMLString = "<font face=Arial<font size=2<color=#000000Hello Everyone,<br /<br /" & _ "<span style=background-color:#FF0000<font color=#FFFFFFRed</span" & _ "<font color=#4B0082 = Missed Due Date.<br / " & _ "<font color=#FF00FFTesting<br /" & _ "<font color=#000000<dir" & _ "<liLine <b2</b</dir<br /" & _ "Testing new line" TestHTMLString2 = "<font color=#FF00FFTesting next section <br /" With MItem .To = "" .CC = "" .Subject = "Test Email using HTML on " & Format(Now, "dddd mm/dd/yy") .htmlBody = TestHTMLString & RangetoHTML(rng) & TestHTMLString2 .Display ' This allows the email to show vs auto sending End With Set OutlookApp = Nothing Set MItem = Nothing End Sub |
Excel VBA toggle Outlook '03 Word email editor on/off
Hi
Update: No good workaround on this moment but I will try more soon if I have more time. Will post in this thread if I have a good solution that I like. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi NewVisioUser I will try to work on it this weekend -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "NewVisioUser" wrote in message ... Hi Ron, I also would be very interested to see a workaround to this. I'm trying to use your code as well for one of my projects. It would be great if there was a way to turn off the Word editor, run the code, then turn the Word editor back on. I'm a VBA newbie, however. I know just enough to make sense of other peoples' code, but not enough yet to write my own from scratch. So, thank you so much for sharing your genius with us... "Ron de Bruin" wrote: Hi J Smith 555 I hope I have time soon but Excel 2007 eat all my time on this moment It is on top of the list -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "J Smith 555" wrote in message ... Ron, Thank you for the quick reply and the RangeToHTML code in the first place; it has helped out a lot. If you omit the section of code that I can't get to work; (the If .. Then statement and comment out my Dim statement) your code works great. I am trying to program around the fact that I am not always the one that gets to execute the code and while I was testing the VBA script I would have the email that was generated not display the HTML correctly. I.E. TestHTMLString1 & 2 don't appear but the 'RangetoHTML' part does (assuming that there is data inside Range("B4:C10") in the first place.) Let me know if you manage to tackle this one, Thank you again for your time, "Ron de Bruin" wrote: Hi J Smith 555 The code from my site is working OK with this setting on an off This is the problem TestHTMLString & RangetoHTML(rng) & TestHTMLString2 It is on my list to test and find a good solution -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "J Smith 555" <J Smith wrote in message ... Hello, I have checked Ron's website on how to email from Excel (using VBA) a range of cells using RangeToHTML function; (http://www.rondebruin.nl/mail/folder3/mail4.htm) and the code works. The caveat for the code is 'Use Microsoft Office Word 2003 to edit e-mail messages' has to be unchecked in order for the script to run properly. What I am attempting to do is build a function (aka check to see if ..) inside the code that Ron has developed to determine if the end user has that option selected or not. if they do have Word '03 set as their text editor is to turn that off, finish the code and then if needed to turn it back on (if that is Applicable or not). I have found a reference through the MSDN site (http://msdn.microsoft.com/en-us/libr...ffice.11).aspx) for 'IsWordMail' method but I am unable to get my code to return a True / False answer. I am getting 'Run-time error '424' Object required error msg when I attempt to run the below code. The debug puts me to my Dim statement for TestOutlookEditor. Below I have the code that I am working with (I have excluded the RangeToHTML() for space) Sub TestHTMLEmailEditor() ' This creates an email from Excel using Outlook 2003 . Dim TestHTMLString As String Dim TestHTMLString2 As String Dim rng As Range ' Test variable to check Outlook msg formatting .. currently not working Set TestOutlookEditor = OutlookApp(ActiveInspector.IsWordEditor) Set OutlookApp = CreateObject("Outlook.Application") Set MItem = OutlookApp.CreateItem(olMailItem) Set rng = Nothing On Error Resume Next 'Only the visible cells in the selection Set rng = Range("B4:C10") ' This is a test range of cells 'You can also use a range if you want 'Set rng = Sheets("YourSheet").Range("D4:D12").SpecialCells(x lCellTypeVisible) On Error GoTo 0 If rng Is Nothing Then MsgBox "The selection is not a range or the sheet is protected" & _ vbNewLine & "please correct and try again.", vbOKOnly Exit Sub End If ' Below is the section of code I am unable to get to work ' ********************** If TestEditor.IsWordEditor = True Then MsgBox ("Pass") ' confirmation from Excel if value is true Else MsgBox ("Failed") ' confirmation from Excel if value is False End If ' ********************* End of Test code TestHTMLString = "<font face=Arial<font size=2<color=#000000Hello Everyone,<br /<br /" & _ "<span style=background-color:#FF0000<font color=#FFFFFFRed</span" & _ "<font color=#4B0082 = Missed Due Date.<br / " & _ "<font color=#FF00FFTesting<br /" & _ "<font color=#000000<dir" & _ "<liLine <b2</b</dir<br /" & _ "Testing new line" TestHTMLString2 = "<font color=#FF00FFTesting next section <br /" With MItem .To = "" .CC = "" .Subject = "Test Email using HTML on " & Format(Now, "dddd mm/dd/yy") .htmlBody = TestHTMLString & RangetoHTML(rng) & TestHTMLString2 .Display ' This allows the email to show vs auto sending End With Set OutlookApp = Nothing Set MItem = Nothing End Sub |
| All times are GMT +1. The time now is 07:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com