PasteSpecial does not keep source formatting when copying range in Excel

Question

I am copying a range of cells from an existing workbook to a temporary workbook and want to keep the exact formatting of the source workbook. I'm using a modified version of the RangetoHTML function by Ron de Bruin.

The purpose is to copy a range from the source workbook to the body of a mail.

But, when I run the macro, it pastes the range in the mail only in values in a different format (see link below). All other paste type of the PasteSpecial either fails or give the same result.

I've tried every paste type of the PasteSpecial method with no luck.

If I try to specify more arguments for the PasteSpecial function it throws a 1004 error (I don't need to use anything else than the default values though).

The source workbook has merged cells but I tried removing the merged cells but that didn't change the output.

This code is called multiple times as I want to insert ranges from different workbook. The result is always the correct range but with no formatting.

It is run from Access as I'm trying to run this script from a form. It is in a public module and called when creating the mail body in another module. Here is an extract of the part that trows an error when removing the comments:

Public Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006

    Dim xlApp As Excel.Application
    Dim TempWB As Excel.workbook

    Set TempWB = xlApp.Workbooks.Add(1)
    rng.Copy 'This is passed as an argument of the RangetoHTML function
    TempWB.Worksheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues ', Operation:=-4142, SkipBlanks:=True, Transpose:=False
    TempWB.Worksheets(1).Range("A1").PasteSpecial Paste:=xlPasteFormats ', Operation:=-4142, SkipBlanks:=True, Transpose:=False
    TempWB.Worksheets(1).Range("A1").PasteSpecial Paste:=xlPasteColumnWidths ', Operation:=-4142, SkipBlanks:=True, Transpose:=False

'The rest is similar to the original version of RangetoHTML

    'Close TempWB
    TempWB.Close savechanges:=False
    xlApp.Quit

    Set TempWB = Nothing
    Set xlApp = Nothing

End Function

I want the range to be displayed in the body of the mail the same as in the source workbook .

I can't seem to find any problem with this code that will prevent the formatting to be copied over despite all my attempts.

EDIT :

If I change the line :

Set TempWB = xlApp.Workbooks.Add(1)

to :

Set TempWB = Workbooks.Add(1)

then the output is in the correct formatting, but there is an instance of Excel that stays open in the task manager after the sub ends.

EDIT 2:

Here is some screenshots to help explain the issue (with sensitive information blurred).

This is the output I'm expecting:

And here is the actual result I get when using the code provided above:

I've checked the content of the temp file where the table is pasted and the formatting is identical to what is pasted in the mail body. That tells me that the PasteSpecial is the culprit here and not the rest of the RangetoHTML function.

EDIT 3:

I also tried to use the plain Paste function and I got the same result as pictured above:

rng.Copy
TempWB.Worksheets(1).Paste

PasteSpecial actually does nothing no matter which arguments I provide.

Answer 1

You need to use the xlPasteAll or xlPasteAllUsingSourceTheme values if you want to preserve formatting. See XlPasteType enumeration.