Paste Excel range to body of Outlook email with no column structure

1
votes

I'm looking to copy an Excel range to the body of an Outlook email.

The problem is that with the code I have, the Outlook email body preserves the column structure of the Excel range and I just want text (with bold and italic like in the Excel file).

I just want simple text in the Email-body of Outlook.

Sub excelrange()

Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

With Sheets(Range("D3").Value)
    Set rng = Sheets("Gibraltar").Range("a16:d47")
End With

On Error Resume Next
With OutMail
    .To = ActiveSheet.Range("b13")
    .CC = ActiveSheet.Range("b14")
    .BCC = ""
    .Subject = ActiveSheet.Range("b11")
    .HTMLBody = RangetoHTML(rng)
    .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub


Function RangetoHTML(rng As Range)
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook
TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
'Copy the range and create a new workbook to past the data in
rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
    .Cells(1).PasteSpecial Paste:=8
    .Cells(1).PasteSpecial xlPasteValues, , False, False
    .Cells(1).PasteSpecial xlPasteFormats, , False, False
    .Cells(1).Select
    Application.CutCopyMode = False
    On Error Resume Next
    .DrawingObjects.Visible = True
    .DrawingObjects.Delete
    On Error GoTo 0
End With
'Publish the sheet to a htm file
With TempWB.PublishObjects.Add( _
     SourceType:=xlSourceRange, _
     Filename:=TempFile, _
     Sheet:=TempWB.Sheets(1).Name, _
     Source:=TempWB.Sheets(1).UsedRange.Address, _
     HtmlType:=xlHtmlStatic)
    .Publish (True)
End With
'Read all data from the htm file into RangetoHTML
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                      "align=left x:publishsource=")
'Close TempWB
TempWB.Close savechanges:=False
'Delete the htm file we used in this function
Kill TempFile
Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function here
2
excelvbaoutlook
do you want simple text or are SOME cells bold/italic? That makes a big difference! – Max
Shall there by tab-stops between cell values, ore other separator? New rows in a new paragraph? – Max
Ron de Bruin’s routine demonstrates how to use Microsoft’s PublishObjects to convert an Excel Range to an Html table with as similar appearance as possible. You are converting a range ("a16:d47"). If you do not want a table, what do you want? A VBA routine to convert an Excel range to Html is not difficult to write if the Excel range is not too complicated. You say you want to copy bold and italic. Do you have entire cells formatted as bold or italic? Or do you have isolated words within a cell formatted as bold or italic? Copying the formatting of isolated words is possible but can be slow. – Tony Dallimore
I´m not an expert in VBA, i just searched the net for code and tried to adapt it. I just dont want the Outlook body in table structure, if it´s too hard to keep the bold and italic words, i´m ok with that, i just dont want the table structure. The bold words are entire cells, not word inside a cell. – Luis Mesquita
Do you mean you do not want to see the borders? Or do you mean you want to read down: Text from cell A16, text from cell B16, C16, D16, A17, and so on until D47? Copying cells into an Html table structure is easy. You can test a cell to be bold, not bold or mixed. You say you have no mixed cells so if bold then surround text with <B> to </B>. Do the same for Italic except <I> to </I>. Mixed cells are more difficult because you need to test each character of the cell separated and decide which bits of the text to highlight with bold or italic. Its messy rather than difficult. – Tony Dallimore

2 Answers

0
votes

Simply remove the following line from your code/Function RangetoHTML(rng As Range)

.Cells(1).PasteSpecial xlPasteFormats, , False, False
0
votes

This is more some ideas than an answer, but it may help you decide what you want.

If you want formatting such as bold or italic, the email body must be Html. You could have a file containing something like:

<p>Dear %receiver%,<br>Good day.</p>    
<p>Please find following other request details:</p> 
<p>Name: %name%<br>I: <b>%id%</b><br>Flag: %flag%</p>
<p>Ammmmm: %xxx% %yyyyy%<br>Collection Date: <i>%colldate%</i></p>

This is valid, if incomplete, Html. If you do not know Html:

  • <p> … </p> is a paragraph
  • <br> is a line break
  • <b> … </b> is bold
  • <i> … </i> is italic

Strings such as “%receiver%” and “%colldate%” would be substituted at runtime with the values from the appropriate cell of the range. You would need another file to specify the substitution. Something like:

receiver   C15   
name       A4     
id         B3     
flag       B4
colldate   B6      m/dd/yyyy

The first column is the code string (without the percent signs) that appears in the template. The second column is the cell within the range that contains the value to replace the code. If you want output numbers and dates to be formatted, you could have a third column specifying the Number format.

With the above approach, your VBA would need to read the two files and then loop down the list in the second file replacing the strings with the cell values.

Since you are using Excel, these two files could be worksheets. The second file is already a table so maps easily to Excel. The first file would need to be converted to a worksheet something like:

Row  Column A
  1  Dear %receiver%,
     Good day.  
  2  Please find following other request details:   
  3  Name: %name%
     I: <b>%id%</b>
     Flag: %flag%
  4  Ammmmm: %xxx% %yyyyy%
     Collection Date: <i>%colldate%</i>

I have left Bold and italic as Html tags in this worksheet. You could use Excel formatting but then your code would have to search for bold and italic characters within each cell and generate the necessary Html. This would slow your routine and be more advanced and much trickier VBA that would otherwise be needed. I can explain what would be involved if you want to know.

You would also need to replace vbLF within the cells with "<br>" and surround the value with “<p>” and “</p>”.

With the one exception I have explained, the VBA to achieve this conversion would be quite simple. I would expect any online, Excel-VBA tutorial to cover the necessary statements.