Hi Scott

You can start here
http://www.rondebruin.nl/mail/folder3/mail4.htm

Ho do you name your checkboxes in each row
Do you incuode the row number in the name ?


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message ...
I would like to create a macro that would open a new email message with
information from the active row. I also want to incorporate check boxes into
the macro so that if one check box is ticked, then the "To:" would only be
cell E from the active row, if the other check box is ticked, then the "To:"
would be only cell H from the active row, and if both check boxes are ticked,
the the "To:" would be both cell E and cell H from the active row. The
subject would always be cell C from the active row. Is that possible?

Again with less Typo's

How do you name your checkboxes in each row
Do you include the row number in the name ?


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Hi Scott

You can start here
http://www.rondebruin.nl/mail/folder3/mail4.htm

Ho do you name your checkboxes in each row
Do you incuode the row number in the name ?


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message ...
I would like to create a macro that would open a new email message with
information from the active row. I also want to incorporate check boxes into
the macro so that if one check box is ticked, then the "To:" would only be
cell E from the active row, if the other check box is ticked, then the "To:"
would be only cell H from the active row, and if both check boxes are ticked,
the the "To:" would be both cell E and cell H from the active row. The
subject would always be cell C from the active row. Is that possible?

Actually, I wanted there to be only 2 check boxes, not 2 in each row, and
let's call them "1" and "2" to make things easy. Is that possible, or would
there have to be 2 in each row.

"Ron de Bruin" wrote:

Again with less Typo's

How do you name your checkboxes in each row
Do you include the row number in the name ?


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Hi Scott

You can start here
http://www.rondebruin.nl/mail/folder3/mail4.htm

Ho do you name your checkboxes in each row
Do you incuode the row number in the name ?


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message ...
I would like to create a macro that would open a new email message with
information from the active row. I also want to incorporate check boxes into
the macro so that if one check box is ticked, then the "To:" would only be
cell E from the active row, if the other check box is ticked, then the "To:"
would be only cell H from the active row, and if both check boxes are ticked,
the the "To:" would be both cell E and cell H from the active row. The
subject would always be cell C from the active row. Is that possible?

Also, the email would be empty (except for my signature if that's possible).
Just the "to:" and "subject" would populate. (Maybe cc:)

I wasn't able to find the answer on your web site.
Thanks for all your help.

"Ron de Bruin" wrote:

Again with less Typo's

How do you name your checkboxes in each row
Do you include the row number in the name ?


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Hi Scott

You can start here
http://www.rondebruin.nl/mail/folder3/mail4.htm

Ho do you name your checkboxes in each row
Do you incuode the row number in the name ?


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message ...
I would like to create a macro that would open a new email message with
information from the active row. I also want to incorporate check boxes into
the macro so that if one check box is ticked, then the "To:" would only be
cell E from the active row, if the other check box is ticked, then the "To:"
would be only cell H from the active row, and if both check boxes are ticked,
the the "To:" would be both cell E and cell H from the active row. The
subject would always be cell C from the active row. Is that possible?

Hi Scott

Two is much better <g

I add two checkboxes from the control toolbox on the sheet named
CheckBox1 and CheckBox2


Sub Mail_Selection_Outlook_Body()
' Don't forget to copy the function RangetoHTML in the module.
' Is not working in Office 97
Dim sh As Worksheet
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim str As String

Set sh = ActiveSheet
Set rng = ActiveCell.EntireRow
With Sheets("Sheet1")

If .CheckBox1 = True And .CheckBox2 = True Then
str = .Cells(ActiveCell.Row, "E").Value & ";" & .Cells(ActiveCell.Row, "H").Value
Else
If .CheckBox1 = True Then str = .Cells(ActiveCell.Row, "E").Value
If .CheckBox2 = True Then str = .Cells(ActiveCell.Row, "H").Value
End If

End With

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = str
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = RangetoHTML(sh, rng)
.Display 'or use .Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub



Public Function RangetoHTML(sh As Worksheet, rng As Range)
'Changed by Ron de Bruin 13-Sept-2006
' You can't use this function in Excel 97
Dim TempFile As String
Dim Nwb As Workbook
Dim fso As Object
Dim ts As Object

sh.Copy
Set Nwb = ActiveWorkbook

With Nwb.Sheets(1)
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With

TempFile = Environ$("temp") & "/" & _
Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

With Nwb.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=sh.Name, _
Source:=rng.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

Nwb.Close False

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
'PublishObjects align center so we change it to left
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", "align=left x:publishsource=")

Set ts = Nothing
Set fso = Nothing
Set Nwb = Nothing
Kill TempFile
End Function



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message ...
Actually, I wanted there to be only 2 check boxes, not 2 in each row, and
let's call them "1" and "2" to make things easy. Is that possible, or would
there have to be 2 in each row.

"Ron de Bruin" wrote:

Again with less Typo's

How do you name your checkboxes in each row
Do you include the row number in the name ?


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Hi Scott

You can start here
http://www.rondebruin.nl/mail/folder3/mail4.htm

Ho do you name your checkboxes in each row
Do you incuode the row number in the name ?


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message
...
I would like to create a macro that would open a new email message with
information from the active row. I also want to incorporate check boxes into
the macro so that if one check box is ticked, then the "To:" would only be
cell E from the active row, if the other check box is ticked, then the "To:"
would be only cell H from the active row, and if both check boxes are ticked,
the the "To:" would be both cell E and cell H from the active row. The
subject would always be cell C from the active row. Is that possible?

Also, the email would be empty (except for my signature if that's possible).
Just the "to:" and "subject" would populate. (Maybe cc:)

That is not what I posted but you can adapt this example
http://www.rondebruin.nl/mail/folder3/signature.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message ...
Also, the email would be empty (except for my signature if that's possible).
Just the "to:" and "subject" would populate. (Maybe cc:)

I wasn't able to find the answer on your web site.
Thanks for all your help.

"Ron de Bruin" wrote:

Again with less Typo's

How do you name your checkboxes in each row
Do you include the row number in the name ?


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Hi Scott

You can start here
http://www.rondebruin.nl/mail/folder3/mail4.htm

Ho do you name your checkboxes in each row
Do you incuode the row number in the name ?


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message
...
I would like to create a macro that would open a new email message with
information from the active row. I also want to incorporate check boxes into
the macro so that if one check box is ticked, then the "To:" would only be
cell E from the active row, if the other check box is ticked, then the "To:"
would be only cell H from the active row, and if both check boxes are ticked,
the the "To:" would be both cell E and cell H from the active row. The
subject would always be cell C from the active row. Is that possible?

I can't seem to get it to work. When I click the command button it says
"subscript out of range". Are there any modifications I need to make, or
does it matter if the module is in "sheet1" or "this workbook"?

Thanks,
Scott

"Ron de Bruin" wrote:

Hi Scott

Two is much better <g

I add two checkboxes from the control toolbox on the sheet named
CheckBox1 and CheckBox2


Sub Mail_Selection_Outlook_Body()
' Don't forget to copy the function RangetoHTML in the module.
' Is not working in Office 97
Dim sh As Worksheet
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim str As String

Set sh = ActiveSheet
Set rng = ActiveCell.EntireRow
With Sheets("Sheet1")

If .CheckBox1 = True And .CheckBox2 = True Then
str = .Cells(ActiveCell.Row, "E").Value & ";" & .Cells(ActiveCell.Row, "H").Value
Else
If .CheckBox1 = True Then str = .Cells(ActiveCell.Row, "E").Value
If .CheckBox2 = True Then str = .Cells(ActiveCell.Row, "H").Value
End If

End With

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = str
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = RangetoHTML(sh, rng)
.Display 'or use .Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub



Public Function RangetoHTML(sh As Worksheet, rng As Range)
'Changed by Ron de Bruin 13-Sept-2006
' You can't use this function in Excel 97
Dim TempFile As String
Dim Nwb As Workbook
Dim fso As Object
Dim ts As Object

sh.Copy
Set Nwb = ActiveWorkbook

With Nwb.Sheets(1)
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With

TempFile = Environ$("temp") & "/" & _
Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

With Nwb.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=sh.Name, _
Source:=rng.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

Nwb.Close False

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
'PublishObjects align center so we change it to left
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", "align=left x:publishsource=")

Set ts = Nothing
Set fso = Nothing
Set Nwb = Nothing
Kill TempFile
End Function



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message ...
Actually, I wanted there to be only 2 check boxes, not 2 in each row, and
let's call them "1" and "2" to make things easy. Is that possible, or would
there have to be 2 in each row.

"Ron de Bruin" wrote:

Again with less Typo's

How do you name your checkboxes in each row
Do you include the row number in the name ?


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Hi Scott

You can start here
http://www.rondebruin.nl/mail/folder3/mail4.htm

Ho do you name your checkboxes in each row
Do you incuode the row number in the name ?


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message
...
I would like to create a macro that would open a new email message with
information from the active row. I also want to incorporate check boxes into
the macro so that if one check box is ticked, then the "To:" would only be
cell E from the active row, if the other check box is ticked, then the "To:"
would be only cell H from the active row, and if both check boxes are ticked,
the the "To:" would be both cell E and cell H from the active row. The
subject would always be cell C from the active row. Is that possible?

Typo from me

And you must copy the function and macro in a normal module (not sheet module or Thisworkbook)

Change
With Sheets("Sheet1")

To
With sh


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message ...
I can't seem to get it to work. When I click the command button it says
"subscript out of range". Are there any modifications I need to make, or
does it matter if the module is in "sheet1" or "this workbook"?

Thanks,
Scott

"Ron de Bruin" wrote:

Hi Scott

Two is much better <g

I add two checkboxes from the control toolbox on the sheet named
CheckBox1 and CheckBox2


Sub Mail_Selection_Outlook_Body()
' Don't forget to copy the function RangetoHTML in the module.
' Is not working in Office 97
Dim sh As Worksheet
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim str As String

Set sh = ActiveSheet
Set rng = ActiveCell.EntireRow
With Sheets("Sheet1")

If .CheckBox1 = True And .CheckBox2 = True Then
str = .Cells(ActiveCell.Row, "E").Value & ";" & .Cells(ActiveCell.Row, "H").Value
Else
If .CheckBox1 = True Then str = .Cells(ActiveCell.Row, "E").Value
If .CheckBox2 = True Then str = .Cells(ActiveCell.Row, "H").Value
End If

End With

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = str
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = RangetoHTML(sh, rng)
.Display 'or use .Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub



Public Function RangetoHTML(sh As Worksheet, rng As Range)
'Changed by Ron de Bruin 13-Sept-2006
' You can't use this function in Excel 97
Dim TempFile As String
Dim Nwb As Workbook
Dim fso As Object
Dim ts As Object

sh.Copy
Set Nwb = ActiveWorkbook

With Nwb.Sheets(1)
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With

TempFile = Environ$("temp") & "/" & _
Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

With Nwb.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=sh.Name, _
Source:=rng.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

Nwb.Close False

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
'PublishObjects align center so we change it to left
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", "align=left x:publishsource=")

Set ts = Nothing
Set fso = Nothing
Set Nwb = Nothing
Kill TempFile
End Function



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message
...
Actually, I wanted there to be only 2 check boxes, not 2 in each row, and
let's call them "1" and "2" to make things easy. Is that possible, or would
there have to be 2 in each row.

"Ron de Bruin" wrote:

Again with less Typo's

How do you name your checkboxes in each row
Do you include the row number in the name ?


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Hi Scott

You can start here
http://www.rondebruin.nl/mail/folder3/mail4.htm

Ho do you name your checkboxes in each row
Do you incuode the row number in the name ?


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message
...
I would like to create a macro that would open a new email message with
information from the active row. I also want to incorporate check boxes into
the macro so that if one check box is ticked, then the "To:" would only be
cell E from the active row, if the other check box is ticked, then the "To:"
would be only cell H from the active row, and if both check boxes are ticked,
the the "To:" would be both cell E and cell H from the active row. The
subject would always be cell C from the active row. Is that possible?

Ok...I copied it into a new module and changed the "sheet1" to "sh" and now I
get the following error:
"Compile error: method or data member not found" and it highlights the
..checkbox1

"Ron de Bruin" wrote:

Typo from me

And you must copy the function and macro in a normal module (not sheet module or Thisworkbook)

Change
With Sheets("Sheet1")

To
With sh


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message ...
I can't seem to get it to work. When I click the command button it says
"subscript out of range". Are there any modifications I need to make, or
does it matter if the module is in "sheet1" or "this workbook"?

Thanks,
Scott

"Ron de Bruin" wrote:

Hi Scott

Two is much better <g

I add two checkboxes from the control toolbox on the sheet named
CheckBox1 and CheckBox2


Sub Mail_Selection_Outlook_Body()
' Don't forget to copy the function RangetoHTML in the module.
' Is not working in Office 97
Dim sh As Worksheet
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim str As String

Set sh = ActiveSheet
Set rng = ActiveCell.EntireRow
With Sheets("Sheet1")

If .CheckBox1 = True And .CheckBox2 = True Then
str = .Cells(ActiveCell.Row, "E").Value & ";" & .Cells(ActiveCell.Row, "H").Value
Else
If .CheckBox1 = True Then str = .Cells(ActiveCell.Row, "E").Value
If .CheckBox2 = True Then str = .Cells(ActiveCell.Row, "H").Value
End If

End With

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = str
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = RangetoHTML(sh, rng)
.Display 'or use .Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub



Public Function RangetoHTML(sh As Worksheet, rng As Range)
'Changed by Ron de Bruin 13-Sept-2006
' You can't use this function in Excel 97
Dim TempFile As String
Dim Nwb As Workbook
Dim fso As Object
Dim ts As Object

sh.Copy
Set Nwb = ActiveWorkbook

With Nwb.Sheets(1)
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With

TempFile = Environ$("temp") & "/" & _
Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

With Nwb.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=sh.Name, _
Source:=rng.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

Nwb.Close False

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
'PublishObjects align center so we change it to left
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", "align=left x:publishsource=")

Set ts = Nothing
Set fso = Nothing
Set Nwb = Nothing
Kill TempFile
End Function



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message
...
Actually, I wanted there to be only 2 check boxes, not 2 in each row, and
let's call them "1" and "2" to make things easy. Is that possible, or would
there have to be 2 in each row.

"Ron de Bruin" wrote:

Again with less Typo's

How do you name your checkboxes in each row
Do you include the row number in the name ?


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Hi Scott

You can start here
http://www.rondebruin.nl/mail/folder3/mail4.htm

Ho do you name your checkboxes in each row
Do you incuode the row number in the name ?


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message
...
I would like to create a macro that would open a new email message with
information from the active row. I also want to incorporate check boxes into
the macro so that if one check box is ticked, then the "To:" would only be
cell E from the active row, if the other check box is ticked, then the "To:"
would be only cell H from the active row, and if both check boxes are ticked,
the the "To:" would be both cell E and cell H from the active row. The
subject would always be cell C from the active row. Is that possible?

Am I supposed to be using the check boxes from the forms toolbar or control
toolbox. I am using the forms check boxes.

"Ron de Bruin" wrote:

Typo from me

And you must copy the function and macro in a normal module (not sheet module or Thisworkbook)

Change
With Sheets("Sheet1")

To
With sh


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message ...
I can't seem to get it to work. When I click the command button it says
"subscript out of range". Are there any modifications I need to make, or
does it matter if the module is in "sheet1" or "this workbook"?

Thanks,
Scott

"Ron de Bruin" wrote:

Hi Scott

Two is much better <g

I add two checkboxes from the control toolbox on the sheet named
CheckBox1 and CheckBox2


Sub Mail_Selection_Outlook_Body()
' Don't forget to copy the function RangetoHTML in the module.
' Is not working in Office 97
Dim sh As Worksheet
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim str As String

Set sh = ActiveSheet
Set rng = ActiveCell.EntireRow
With Sheets("Sheet1")

If .CheckBox1 = True And .CheckBox2 = True Then
str = .Cells(ActiveCell.Row, "E").Value & ";" & .Cells(ActiveCell.Row, "H").Value
Else
If .CheckBox1 = True Then str = .Cells(ActiveCell.Row, "E").Value
If .CheckBox2 = True Then str = .Cells(ActiveCell.Row, "H").Value
End If

End With

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = str
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = RangetoHTML(sh, rng)
.Display 'or use .Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub



Public Function RangetoHTML(sh As Worksheet, rng As Range)
'Changed by Ron de Bruin 13-Sept-2006
' You can't use this function in Excel 97
Dim TempFile As String
Dim Nwb As Workbook
Dim fso As Object
Dim ts As Object

sh.Copy
Set Nwb = ActiveWorkbook

With Nwb.Sheets(1)
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With

TempFile = Environ$("temp") & "/" & _
Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

With Nwb.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=sh.Name, _
Source:=rng.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

Nwb.Close False

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
'PublishObjects align center so we change it to left
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", "align=left x:publishsource=")

Set ts = Nothing
Set fso = Nothing
Set Nwb = Nothing
Kill TempFile
End Function



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message
...
Actually, I wanted there to be only 2 check boxes, not 2 in each row, and
let's call them "1" and "2" to make things easy. Is that possible, or would
there have to be 2 in each row.

"Ron de Bruin" wrote:

Again with less Typo's

How do you name your checkboxes in each row
Do you include the row number in the name ?


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Hi Scott

You can start here
http://www.rondebruin.nl/mail/folder3/mail4.htm

Ho do you name your checkboxes in each row
Do you incuode the row number in the name ?


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message
...
I would like to create a macro that would open a new email message with
information from the active row. I also want to incorporate check boxes into
the macro so that if one check box is ticked, then the "To:" would only be
cell E from the active row, if the other check box is ticked, then the "To:"
would be only cell H from the active row, and if both check boxes are ticked,
the the "To:" would be both cell E and cell H from the active row. The
subject would always be cell C from the active row. Is that possible?

Hi Scott


I add two checkboxes from the control toolbox on the sheet named
CheckBox1 and CheckBox2


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message ...
Am I supposed to be using the check boxes from the forms toolbar or control
toolbox. I am using the forms check boxes.

"Ron de Bruin" wrote:

Typo from me

And you must copy the function and macro in a normal module (not sheet module or Thisworkbook)

Change
With Sheets("Sheet1")

To
With sh


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message
...
I can't seem to get it to work. When I click the command button it says
"subscript out of range". Are there any modifications I need to make, or
does it matter if the module is in "sheet1" or "this workbook"?

Thanks,
Scott

"Ron de Bruin" wrote:

Hi Scott

Two is much better <g

I add two checkboxes from the control toolbox on the sheet named
CheckBox1 and CheckBox2


Sub Mail_Selection_Outlook_Body()
' Don't forget to copy the function RangetoHTML in the module.
' Is not working in Office 97
Dim sh As Worksheet
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim str As String

Set sh = ActiveSheet
Set rng = ActiveCell.EntireRow
With Sheets("Sheet1")

If .CheckBox1 = True And .CheckBox2 = True Then
str = .Cells(ActiveCell.Row, "E").Value & ";" & .Cells(ActiveCell.Row, "H").Value
Else
If .CheckBox1 = True Then str = .Cells(ActiveCell.Row, "E").Value
If .CheckBox2 = True Then str = .Cells(ActiveCell.Row, "H").Value
End If

End With

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = str
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = RangetoHTML(sh, rng)
.Display 'or use .Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub



Public Function RangetoHTML(sh As Worksheet, rng As Range)
'Changed by Ron de Bruin 13-Sept-2006
' You can't use this function in Excel 97
Dim TempFile As String
Dim Nwb As Workbook
Dim fso As Object
Dim ts As Object

sh.Copy
Set Nwb = ActiveWorkbook

With Nwb.Sheets(1)
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With

TempFile = Environ$("temp") & "/" & _
Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

With Nwb.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=sh.Name, _
Source:=rng.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

Nwb.Close False

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
'PublishObjects align center so we change it to left
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", "align=left x:publishsource=")

Set ts = Nothing
Set fso = Nothing
Set Nwb = Nothing
Kill TempFile
End Function



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message
...
Actually, I wanted there to be only 2 check boxes, not 2 in each row, and
let's call them "1" and "2" to make things easy. Is that possible, or would
there have to be 2 in each row.

"Ron de Bruin" wrote:

Again with less Typo's

How do you name your checkboxes in each row
Do you include the row number in the name ?


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Hi Scott

You can start here
http://www.rondebruin.nl/mail/folder3/mail4.htm

Ho do you name your checkboxes in each row
Do you incuode the row number in the name ?


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message
...
I would like to create a macro that would open a new email message with
information from the active row. I also want to incorporate check boxes into
the macro so that if one check box is ticked, then the "To:" would only be
cell E from the active row, if the other check box is ticked, then the "To:"
would be only cell H from the active row, and if both check boxes are ticked,
the the "To:" would be both cell E and cell H from the active row. The
subject would always be cell C from the active row. Is that possible?

For some reason the check boxes are not doing what they're supposed to. The
email opens with nothing in the "to:" section.

"Ron de Bruin" wrote:

Hi Scott


I add two checkboxes from the control toolbox on the sheet named
CheckBox1 and CheckBox2


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message ...
Am I supposed to be using the check boxes from the forms toolbar or control
toolbox. I am using the forms check boxes.

"Ron de Bruin" wrote:

Typo from me

And you must copy the function and macro in a normal module (not sheet module or Thisworkbook)

Change
With Sheets("Sheet1")

To
With sh


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message
...
I can't seem to get it to work. When I click the command button it says
"subscript out of range". Are there any modifications I need to make, or
does it matter if the module is in "sheet1" or "this workbook"?

Thanks,
Scott

"Ron de Bruin" wrote:

Hi Scott

Two is much better <g

I add two checkboxes from the control toolbox on the sheet named
CheckBox1 and CheckBox2


Sub Mail_Selection_Outlook_Body()
' Don't forget to copy the function RangetoHTML in the module.
' Is not working in Office 97
Dim sh As Worksheet
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim str As String

Set sh = ActiveSheet
Set rng = ActiveCell.EntireRow
With Sheets("Sheet1")

If .CheckBox1 = True And .CheckBox2 = True Then
str = .Cells(ActiveCell.Row, "E").Value & ";" & .Cells(ActiveCell.Row, "H").Value
Else
If .CheckBox1 = True Then str = .Cells(ActiveCell.Row, "E").Value
If .CheckBox2 = True Then str = .Cells(ActiveCell.Row, "H").Value
End If

End With

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = str
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = RangetoHTML(sh, rng)
.Display 'or use .Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub



Public Function RangetoHTML(sh As Worksheet, rng As Range)
'Changed by Ron de Bruin 13-Sept-2006
' You can't use this function in Excel 97
Dim TempFile As String
Dim Nwb As Workbook
Dim fso As Object
Dim ts As Object

sh.Copy
Set Nwb = ActiveWorkbook

With Nwb.Sheets(1)
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With

TempFile = Environ$("temp") & "/" & _
Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

With Nwb.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=sh.Name, _
Source:=rng.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

Nwb.Close False

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
'PublishObjects align center so we change it to left
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", "align=left x:publishsource=")

Set ts = Nothing
Set fso = Nothing
Set Nwb = Nothing
Kill TempFile
End Function



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message
...
Actually, I wanted there to be only 2 check boxes, not 2 in each row, and
let's call them "1" and "2" to make things easy. Is that possible, or would
there have to be 2 in each row.

"Ron de Bruin" wrote:

Again with less Typo's

How do you name your checkboxes in each row
Do you include the row number in the name ?


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Hi Scott

You can start here
http://www.rondebruin.nl/mail/folder3/mail4.htm

Ho do you name your checkboxes in each row
Do you incuode the row number in the name ?


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message
...
I would like to create a macro that would open a new email message with
information from the active row. I also want to incorporate check boxes into
the macro so that if one check box is ticked, then the "To:" would only be
cell E from the active row, if the other check box is ticked, then the "To:"
would be only cell H from the active row, and if both check boxes are ticked,
the the "To:" would be both cell E and cell H from the active row. The
subject would always be cell C from the active row. Is that possible?

Send me your test workbook private Scott

I look at it then for you



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message ...
For some reason the check boxes are not doing what they're supposed to. The
email opens with nothing in the "to:" section.

"Ron de Bruin" wrote:

Hi Scott


I add two checkboxes from the control toolbox on the sheet named
CheckBox1 and CheckBox2


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message
...
Am I supposed to be using the check boxes from the forms toolbar or control
toolbox. I am using the forms check boxes.

"Ron de Bruin" wrote:

Typo from me

And you must copy the function and macro in a normal module (not sheet module or Thisworkbook)

Change
With Sheets("Sheet1")

To
With sh


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message
...
I can't seem to get it to work. When I click the command button it says
"subscript out of range". Are there any modifications I need to make, or
does it matter if the module is in "sheet1" or "this workbook"?

Thanks,
Scott

"Ron de Bruin" wrote:

Hi Scott

Two is much better <g

I add two checkboxes from the control toolbox on the sheet named
CheckBox1 and CheckBox2


Sub Mail_Selection_Outlook_Body()
' Don't forget to copy the function RangetoHTML in the module.
' Is not working in Office 97
Dim sh As Worksheet
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim str As String

Set sh = ActiveSheet
Set rng = ActiveCell.EntireRow
With Sheets("Sheet1")

If .CheckBox1 = True And .CheckBox2 = True Then
str = .Cells(ActiveCell.Row, "E").Value & ";" & .Cells(ActiveCell.Row, "H").Value
Else
If .CheckBox1 = True Then str = .Cells(ActiveCell.Row, "E").Value
If .CheckBox2 = True Then str = .Cells(ActiveCell.Row, "H").Value
End If

End With

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = str
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = RangetoHTML(sh, rng)
.Display 'or use .Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub



Public Function RangetoHTML(sh As Worksheet, rng As Range)
'Changed by Ron de Bruin 13-Sept-2006
' You can't use this function in Excel 97
Dim TempFile As String
Dim Nwb As Workbook
Dim fso As Object
Dim ts As Object

sh.Copy
Set Nwb = ActiveWorkbook

With Nwb.Sheets(1)
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With

TempFile = Environ$("temp") & "/" & _
Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

With Nwb.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=sh.Name, _
Source:=rng.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

Nwb.Close False

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
'PublishObjects align center so we change it to left
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", "align=left x:publishsource=")

Set ts = Nothing
Set fso = Nothing
Set Nwb = Nothing
Kill TempFile
End Function



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message
...
Actually, I wanted there to be only 2 check boxes, not 2 in each row, and
let's call them "1" and "2" to make things easy. Is that possible, or would
there have to be 2 in each row.

"Ron de Bruin" wrote:

Again with less Typo's

How do you name your checkboxes in each row
Do you include the row number in the name ?


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Hi Scott

You can start here
http://www.rondebruin.nl/mail/folder3/mail4.htm

Ho do you name your checkboxes in each row
Do you incuode the row number in the name ?


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message
...
I would like to create a macro that would open a new email message with
information from the active row. I also want to incorporate check boxes into
the macro so that if one check box is ticked, then the "To:" would only be
cell E from the active row, if the other check box is ticked, then the "To:"
would be only cell H from the active row, and if both check boxes are ticked,
the the "To:" would be both cell E and cell H from the active row. The
subject would always be cell C from the active row. Is that possible?

It not like the reference sh to the activesheet

This is working

With ActiveSheet
If .CheckBox1.Value = True And .CheckBox2.Value = True Then
str = .Cells(ActiveCell.Row, "F").Value & ";" & .Cells(ActiveCell.Row, "H").Value
Else
If .CheckBox1 = True Then str = .Cells(ActiveCell.Row, "F").Value
If .CheckBox2 = True Then str = .Cells(ActiveCell.Row, "H").Value
End If

End With



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Send me your test workbook private Scott

I look at it then for you



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message ...
For some reason the check boxes are not doing what they're supposed to. The
email opens with nothing in the "to:" section.

"Ron de Bruin" wrote:

Hi Scott


I add two checkboxes from the control toolbox on the sheet named
CheckBox1 and CheckBox2


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message
...
Am I supposed to be using the check boxes from the forms toolbar or control
toolbox. I am using the forms check boxes.

"Ron de Bruin" wrote:

Typo from me

And you must copy the function and macro in a normal module (not sheet module or Thisworkbook)

Change
With Sheets("Sheet1")

To
With sh


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message
...
I can't seem to get it to work. When I click the command button it says
"subscript out of range". Are there any modifications I need to make, or
does it matter if the module is in "sheet1" or "this workbook"?

Thanks,
Scott

"Ron de Bruin" wrote:

Hi Scott

Two is much better <g

I add two checkboxes from the control toolbox on the sheet named
CheckBox1 and CheckBox2


Sub Mail_Selection_Outlook_Body()
' Don't forget to copy the function RangetoHTML in the module.
' Is not working in Office 97
Dim sh As Worksheet
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim str As String

Set sh = ActiveSheet
Set rng = ActiveCell.EntireRow
With Sheets("Sheet1")

If .CheckBox1 = True And .CheckBox2 = True Then
str = .Cells(ActiveCell.Row, "E").Value & ";" & .Cells(ActiveCell.Row, "H").Value
Else
If .CheckBox1 = True Then str = .Cells(ActiveCell.Row, "E").Value
If .CheckBox2 = True Then str = .Cells(ActiveCell.Row, "H").Value
End If

End With

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = str
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = RangetoHTML(sh, rng)
.Display 'or use .Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub



Public Function RangetoHTML(sh As Worksheet, rng As Range)
'Changed by Ron de Bruin 13-Sept-2006
' You can't use this function in Excel 97
Dim TempFile As String
Dim Nwb As Workbook
Dim fso As Object
Dim ts As Object

sh.Copy
Set Nwb = ActiveWorkbook

With Nwb.Sheets(1)
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With

TempFile = Environ$("temp") & "/" & _
Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

With Nwb.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=sh.Name, _
Source:=rng.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

Nwb.Close False

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
'PublishObjects align center so we change it to left
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", "align=left x:publishsource=")

Set ts = Nothing
Set fso = Nothing
Set Nwb = Nothing
Kill TempFile
End Function



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message
...
Actually, I wanted there to be only 2 check boxes, not 2 in each row, and
let's call them "1" and "2" to make things easy. Is that possible, or would
there have to be 2 in each row.

"Ron de Bruin" wrote:

Again with less Typo's

How do you name your checkboxes in each row
Do you include the row number in the name ?


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Hi Scott

You can start here
http://www.rondebruin.nl/mail/folder3/mail4.htm

Ho do you name your checkboxes in each row
Do you incuode the row number in the name ?


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Scott Marcus" wrote in message
...
I would like to create a macro that would open a new email message with
information from the active row. I also want to incorporate check boxes into
the macro so that if one check box is ticked, then the "To:" would only be
cell E from the active row, if the other check box is ticked, then the "To:"
would be only cell H from the active row, and if both check boxes are ticked,
the the "To:" would be both cell E and cell H from the active row. The
subject would always be cell C from the active row. Is that possible?