Mailing an excel range in the body of an e-mail
 

Dear All,

I am using a piece of code I located on Ron de Bruins excellent site that
allows for a range in excel to be mailed within the body of an outlook
message. The code works beautifully but does not return any value for the
ComboBoxes I have within the range. Is it possible for these to be visible
as well in the e-mail message? I am including the code for reference.

Sub Mail_Selection_Outlook_Body2()
' You must add a reference to the Microsoft outlook Library
' Don't forget to copy the function RangetoHTML2 in the module.
' Is not working in Office 97
Dim source As Range
Dim dest As Workbook
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem

Set source = Nothing
On Error Resume Next
Set source = Selection.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If source Is Nothing Then
MsgBox "The selection is not a range or the sheet is protect" & _
vbNewLine & "please correct and try again.", vbOKOnly
Exit Sub
End If

If ActiveWindow.SelectedSheets.Count 1 Or _
Selection.Cells.Count = 1 Or _
Selection.Areas.Count 1 Then
MsgBox "An Error occurred :" & vbNewLine & vbNewLine & _
"You have more than one sheet selected." & vbNewLine & _
"You only selected one cell." & vbNewLine & _
"You selected more than one area." & vbNewLine & vbNewLine & _
"Please correct and try again.", vbOKOnly
Exit Sub
End If

Application.ScreenUpdating = False
source.Copy
Set dest = Workbooks.Add(xlWBATWorksheet)
With dest.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
' Paste:=8 will copy the column width in Excel 2000 and higher
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
End With

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = RangetoHTML2
.Send 'or use .Display
End With

dest.Close False
Set OutMail = Nothing
Set OutApp = Nothing
Set dest = Nothing
Application.ScreenUpdating = True
End Sub



Public Function RangetoHTML2()
' You can't use this function in Excel 97
Dim fso As Object
Dim ts As Object
Dim TempFile As String
TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") &
".htm"
With ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=ActiveSheet.Name, _
source:=ActiveSheet.UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML2 = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Kill TempFile
End Function



--
Andy Tallent

Hi andy

See the link on top of the example page to
the KB for Excel 2002-2003(If you use that)

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


"Andy Tallent" wrote in message ...
Dear All,

I am using a piece of code I located on Ron de Bruins excellent site that
allows for a range in excel to be mailed within the body of an outlook
message. The code works beautifully but does not return any value for the
ComboBoxes I have within the range. Is it possible for these to be visible
as well in the e-mail message? I am including the code for reference.

Sub Mail_Selection_Outlook_Body2()
' You must add a reference to the Microsoft outlook Library
' Don't forget to copy the function RangetoHTML2 in the module.
' Is not working in Office 97
Dim source As Range
Dim dest As Workbook
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem

Set source = Nothing
On Error Resume Next
Set source = Selection.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If source Is Nothing Then
MsgBox "The selection is not a range or the sheet is protect" & _
vbNewLine & "please correct and try again.", vbOKOnly
Exit Sub
End If

If ActiveWindow.SelectedSheets.Count 1 Or _
Selection.Cells.Count = 1 Or _
Selection.Areas.Count 1 Then
MsgBox "An Error occurred :" & vbNewLine & vbNewLine & _
"You have more than one sheet selected." & vbNewLine & _
"You only selected one cell." & vbNewLine & _
"You selected more than one area." & vbNewLine & vbNewLine & _
"Please correct and try again.", vbOKOnly
Exit Sub
End If

Application.ScreenUpdating = False
source.Copy
Set dest = Workbooks.Add(xlWBATWorksheet)
With dest.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
' Paste:=8 will copy the column width in Excel 2000 and higher
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
End With

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = RangetoHTML2
.Send 'or use .Display
End With

dest.Close False
Set OutMail = Nothing
Set OutApp = Nothing
Set dest = Nothing
Application.ScreenUpdating = True
End Sub



Public Function RangetoHTML2()
' You can't use this function in Excel 97
Dim fso As Object
Dim ts As Object
Dim TempFile As String
TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") &
".htm"
With ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=ActiveSheet.Name, _
source:=ActiveSheet.UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML2 = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Kill TempFile
End Function



--
Andy Tallent

Hi Ron,

Thank you for this. I have just one further question. Is it possible to
format the text in the body of the mail (bold, underline etc) via the VB
script?
--
Andy Tallent


"Ron de Bruin" wrote:

Hi andy

See the link on top of the example page to
the KB for Excel 2002-2003(If you use that)

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


"Andy Tallent" wrote in message ...
Dear All,

I am using a piece of code I located on Ron de Bruins excellent site that
allows for a range in excel to be mailed within the body of an outlook
message. The code works beautifully but does not return any value for the
ComboBoxes I have within the range. Is it possible for these to be visible
as well in the e-mail message? I am including the code for reference.

Sub Mail_Selection_Outlook_Body2()
' You must add a reference to the Microsoft outlook Library
' Don't forget to copy the function RangetoHTML2 in the module.
' Is not working in Office 97
Dim source As Range
Dim dest As Workbook
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem

Set source = Nothing
On Error Resume Next
Set source = Selection.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If source Is Nothing Then
MsgBox "The selection is not a range or the sheet is protect" & _
vbNewLine & "please correct and try again.", vbOKOnly
Exit Sub
End If

If ActiveWindow.SelectedSheets.Count 1 Or _
Selection.Cells.Count = 1 Or _
Selection.Areas.Count 1 Then
MsgBox "An Error occurred :" & vbNewLine & vbNewLine & _
"You have more than one sheet selected." & vbNewLine & _
"You only selected one cell." & vbNewLine & _
"You selected more than one area." & vbNewLine & vbNewLine & _
"Please correct and try again.", vbOKOnly
Exit Sub
End If

Application.ScreenUpdating = False
source.Copy
Set dest = Workbooks.Add(xlWBATWorksheet)
With dest.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
' Paste:=8 will copy the column width in Excel 2000 and higher
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
End With

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = RangetoHTML2
.Send 'or use .Display
End With

dest.Close False
Set OutMail = Nothing
Set OutApp = Nothing
Set dest = Nothing
Application.ScreenUpdating = True
End Sub



Public Function RangetoHTML2()
' You can't use this function in Excel 97
Dim fso As Object
Dim ts As Object
Dim TempFile As String
TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") &
".htm"
With ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=ActiveSheet.Name, _
source:=ActiveSheet.UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML2 = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Kill TempFile
End Function



--
Andy Tallent

The body is the same as you see on the sheet if you use my example
http://www.rondebruin.nl/mail/folder3/mail4.htm

or if you use the KB example


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


"Andy Tallent" wrote in message ...
Hi Ron,

Thank you for this. I have just one further question. Is it possible to
format the text in the body of the mail (bold, underline etc) via the VB
script?
--
Andy Tallent


"Ron de Bruin" wrote:

Hi andy

See the link on top of the example page to
the KB for Excel 2002-2003(If you use that)

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


"Andy Tallent" wrote in message
...
Dear All,

I am using a piece of code I located on Ron de Bruins excellent site that
allows for a range in excel to be mailed within the body of an outlook
message. The code works beautifully but does not return any value for the
ComboBoxes I have within the range. Is it possible for these to be visible
as well in the e-mail message? I am including the code for reference.

Sub Mail_Selection_Outlook_Body2()
' You must add a reference to the Microsoft outlook Library
' Don't forget to copy the function RangetoHTML2 in the module.
' Is not working in Office 97
Dim source As Range
Dim dest As Workbook
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem

Set source = Nothing
On Error Resume Next
Set source = Selection.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If source Is Nothing Then
MsgBox "The selection is not a range or the sheet is protect" & _
vbNewLine & "please correct and try again.", vbOKOnly
Exit Sub
End If

If ActiveWindow.SelectedSheets.Count 1 Or _
Selection.Cells.Count = 1 Or _
Selection.Areas.Count 1 Then
MsgBox "An Error occurred :" & vbNewLine & vbNewLine & _
"You have more than one sheet selected." & vbNewLine & _
"You only selected one cell." & vbNewLine & _
"You selected more than one area." & vbNewLine & vbNewLine & _
"Please correct and try again.", vbOKOnly
Exit Sub
End If

Application.ScreenUpdating = False
source.Copy
Set dest = Workbooks.Add(xlWBATWorksheet)
With dest.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
' Paste:=8 will copy the column width in Excel 2000 and higher
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
End With

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = RangetoHTML2
.Send 'or use .Display
End With

dest.Close False
Set OutMail = Nothing
Set OutApp = Nothing
Set dest = Nothing
Application.ScreenUpdating = True
End Sub



Public Function RangetoHTML2()
' You can't use this function in Excel 97
Dim fso As Object
Dim ts As Object
Dim TempFile As String
TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") &
".htm"
With ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=ActiveSheet.Name, _
source:=ActiveSheet.UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML2 = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Kill TempFile
End Function



--
Andy Tallent

Hi I tried this same code. I can see a temporary excel sheet with cells which i needs to be copied . But in outlook I can see a empty mail with only the subject. I dont see the excel data in the body of the mail . Please let me know what might be the prob. Also can i check whether the html file is getting created in this .

Any suggestion might be really helpful


Thanks

Vasanth