insert into a body of email...

sal21

Active Member
Joined
Apr 1, 2002
Messages
250
how to insert into a of email thei range of cells similar txt attached...

bodyhttp://www.mytempdir.com/974055
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

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

Active Member
Joined
Apr 1, 2002
Messages
250
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

Active Member
Joined
Apr 1, 2002
Messages
250
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

Active Member
Joined
Apr 1, 2002
Messages
250
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

Active Member
Joined
Apr 1, 2002
Messages
250
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
 

Forum statistics

Threads
1,141,924
Messages
5,709,382
Members
421,633
Latest member
Ubergribbler

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top