insert into a body of email...

sal21

Board Regular
Joined
Apr 1, 2002
Messages
224
how to insert into a of email thei range of cells similar txt attached...

bodyhttp://www.mytempdir.com/974055
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

awalle

Board Regular
Joined
Jul 12, 2006
Messages
56
I hope you are using Outlook, if yes use the following code, to send the range of cells.

Sub main_program ()

Range("a6:g36").Select ' This is the range you will include in the body of the email

Set source = Selection
On Error GoTo 0
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

With OutMail
.To = WHATEVERYOUWANT@WHEREVERINTHEWORLD.COM
.CC = ""
.BCC = ""
.Subject = "Whatever you wnat to tell the user"
.HTMLBody = RangetoHTML
.Send 'or use .Display
End With

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

Function RangetoHTML()
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:=Selection.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Kill TempFile
End Function

Hope this can help you
AWalle.
 

sal21

Board Regular
Joined
Apr 1, 2002
Messages
224
I hope you are using Outlook, if yes use the following code, to send the range of cells.

Sub main_program ()

Range("a6:g36").Select ' This is the range you will include in the body of the email

Set source = Selection
On Error GoTo 0
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

With OutMail
.To = WHATEVERYOUWANT@WHEREVERINTHEWORLD.COM
.CC = ""
.BCC = ""
.Subject = "Whatever you wnat to tell the user"
.HTMLBody = RangetoHTML
.Send 'or use .Display
End With

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

Function RangetoHTML()
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:=Selection.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Kill TempFile
End Function

Hope this can help you
AWalle.

Very nice and good code!
Tks.
 

sal21

Board Regular
Joined
Apr 1, 2002
Messages
224
I hope you are using Outlook, if yes use the following code, to send the range of cells.

Sub main_program ()

Range("a6:g36").Select ' This is the range you will include in the body of the email

Set source = Selection
On Error GoTo 0
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

With OutMail
.To = WHATEVERYOUWANT@WHEREVERINTHEWORLD.COM
.CC = ""
.BCC = ""
.Subject = "Whatever you wnat to tell the user"
.HTMLBody = RangetoHTML
.Send 'or use .Display
End With

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

Function RangetoHTML()
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:=Selection.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Kill TempFile
End Function

Hope this can help you
AWalle.

Hi AWalle, suggestion...
But is possible to insert, before to past the value from sheet a text string similar header with name of sigle column of excel sheet...

Similar:

FILELD1 FILED2 FILED3 ...... FIELDxx

tks.
 

awalle

Board Regular
Joined
Jul 12, 2006
Messages
56

ADVERTISEMENT

Of course you can do it, just modify the following instruction:

.HTMLBody = RangetoHTML

and add the text in the body you need, like:

.HTMLBody = "Whatever you want to tell the user" & RangetoHTML


Hope this can help you.
AWalle
 

sal21

Board Regular
Joined
Apr 1, 2002
Messages
224
I hope you are using Outlook, if yes use the following code, to send the range of cells.

Sub main_program ()

Range("a6:g36").Select ' This is the range you will include in the body of the email

Set source = Selection
On Error GoTo 0
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

With OutMail
.To = WHATEVERYOUWANT@WHEREVERINTHEWORLD.COM
.CC = ""
.BCC = ""
.Subject = "Whatever you wnat to tell the user"
.HTMLBody = RangetoHTML
.Send 'or use .Display
End With

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

Function RangetoHTML()
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:=Selection.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Kill TempFile
End Function

Hope this can help you
AWalle.
Hi AWalle (sorry but i dont know your real name) is possible to use this code if the sheet where i select the range is hided?
 

sal21

Board Regular
Joined
Apr 1, 2002
Messages
224
I hope you are using Outlook, if yes use the following code, to send the range of cells.

Sub main_program ()

Range("a6:g36").Select ' This is the range you will include in the body of the email

Set source = Selection
On Error GoTo 0
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

With OutMail
.To = WHATEVERYOUWANT@WHEREVERINTHEWORLD.COM
.CC = ""
.BCC = ""
.Subject = "Whatever you wnat to tell the user"
.HTMLBody = RangetoHTML
.Send 'or use .Display
End With

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

Function RangetoHTML()
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:=Selection.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Kill TempFile
End Function

Hope this can help you
AWalle.
Hi AWalle (sorry but i dont know your real name) is possible to use this code if the sheet where i select the range is hided?

Hi AWalle (sorry but i dont know your real name) is possible to use this code if the sheet where i select the range is hided?
 

awalle

Board Regular
Joined
Jul 12, 2006
Messages
56
As far as I know, you can not take the data from a hided sheet, but using VB you can unhide the sheet, take the data and hide it again,

you can make a small changes to the code, as shown below:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> main_program()

Sheet2.Visible = xlSheetVisible

Sheet2.Activate
Range("a2:g36").Select <SPAN style="color:#007F00">' This is the range you will include in the body of the email</SPAN>

<SPAN style="color:#00007F">Set</SPAN> Source = Selection
HTMLBody_x = RangetoHTML

Sheet2.Visible = xlSheetHidden

<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0
<SPAN style="color:#00007F">Set</SPAN> OutApp = CreateObject("Outlook.Application")
<SPAN style="color:#00007F">Set</SPAN> OutMail = OutApp.CreateItem(0)

<SPAN style="color:#00007F">With</SPAN> OutMail
.To = "whoeveryouwanttosend@company.com"
.CC = ""
.BCC = ""
.Subject = "Whatever you want to tell the user"
.HTMLBody = HTMLBody_x
.Send <SPAN style="color:#007F00">'or use .Display</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>

<SPAN style="color:#00007F">Set</SPAN> OutMail = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">Set</SPAN> OutApp = <SPAN style="color:#00007F">Nothing</SPAN>




<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Function</SPAN> RangetoHTML()
<SPAN style="color:#00007F">Dim</SPAN> fso <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> ts <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> TempFile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
TempFile = Environ$("temp") & "/" & _
Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
<SPAN style="color:#00007F">With</SPAN> ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=ActiveSheet.Name, _
Source:=Selection.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">Set</SPAN> fso = CreateObject("Scripting.FileSystemObject")
<SPAN style="color:#00007F">Set</SPAN> ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
<SPAN style="color:#00007F">Set</SPAN> ts = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">Set</SPAN> fso = <SPAN style="color:#00007F">Nothing</SPAN>
Kill TempFile
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>

</FONT>


I hope, this can help you.

AWalle
 

Watch MrExcel Video

Forum statistics

Threads
1,114,096
Messages
5,545,929
Members
410,713
Latest member
TaremyLunsil
Top