J
J Smith 555
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/library/aa220121(office.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
12").SpecialCells(xlCellTypeVisible)
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=#000000>Hello
Everyone,<br /><br />" & _
"<span style=background-color:#FF0000><font
color=#FFFFFF>Red</span>" & _
"<font color=#4B0082> = Missed Due Date.<br /> " & _
"<font color=#FF00FF>Testing<br />" & _
"<font color=#000000><dir>" & _
"<li>Line <b>2</b></dir><br />" & _
"Testing new line"
TestHTMLString2 = "<font color=#FF00FF>Testing 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
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/library/aa220121(office.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
12").SpecialCells(xlCellTypeVisible)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=#000000>Hello
Everyone,<br /><br />" & _
"<span style=background-color:#FF0000><font
color=#FFFFFF>Red</span>" & _
"<font color=#4B0082> = Missed Due Date.<br /> " & _
"<font color=#FF00FF>Testing<br />" & _
"<font color=#000000><dir>" & _
"<li>Line <b>2</b></dir><br />" & _
"Testing new line"
TestHTMLString2 = "<font color=#FF00FF>Testing 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