Macro to email with "active row" info

[Guest]

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?

 

[Ron de Bruin]

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 ?

 

[Ron de Bruin]

Again with less Typo's

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

 

[Guest]

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.

 

[Guest]

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]

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 xublishsource=", "align=left xublishsource=")

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

 

[Ron de Bruin]

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

 

[Guest]

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]

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

 

[Guest]

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

 

[Guest]

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]

Hi Scott

 

[Guest]

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]

Send me your test workbook private Scott

I look at it then for you

 

[Ron de Bruin]

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