insert into a body of email...

S

sal21

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

bodyhttp://www.mytempdir.com/974055
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
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.
 
Upvote 0
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.
Click to expand...

Very nice and good code!
Tks.
 
Upvote 0
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.
Click to expand...

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.
 
Upvote 0
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
 
Upvote 0
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.
Click to expand...
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?
 
Upvote 0
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.
Click to expand...
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?
Click to expand...

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?
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Change existing code to attach range of cells to email
Replies
2
Views
203
bh24524
B
V
  • Solved
Email Body
Replies
9
Views
339
Trixterz
T
The Ruff Report
  • Question
Add multiple attachments from email list
Replies
1
Views
260
Trebor76
T
M
  • Question
Excel VBA Save PDF within workbook folder and send as email attachment
Replies
1
Views
192
rpaulson
rpaulson
J
  • Question
Parse HTML table or extract Outlook body items to excel sheet
Replies
2
Views
198
Jaffirahamed
J

Forum statistics

Threads
1,214,415
Messages
6,119,381
Members
448,888
Latest member
Arle8907

Share this page

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
Back
Top