An unusual one this one but please read on.

If I manually copy a range of cells from an Excel worksheet and then paste
into a Draft email in Outlook (My Outlook 2003 uses HTML as draft format with
Word as editor) the resulting pasted cells look fine - colors and formatting
are maintained.

OK now to do it in VBA from Excel.

I copy the range of cells into Clipboard with :-

Range(Cells(aa, 4), Cells(bb, 17)).Select
Selection.Copy

I then obtain the contents of Clipboard and place into a String variable
with :-

Set MyData = New DataObject

MyData.GetFromClipboard
strClip = MyData.GetText

I then create an Outloook object within VBA and build a Draft message using
strClip as part of the Message body.

This all works OK and the Draft message is created but the resulting pasted
range of cells in the Draft message does not look very good, the values are
mis-aligned and wrapped around with any color formatting is lost. It does not
give me the same pretty result as manually cutting and pasting the cells.

Does anyone know how I can preserve the formatting using the VBA method so
that the resulting pasted cells looks as good as the manual method?

Thanks.

With Word as mail editor you can use its object model to insert the
clipboard content. Like this:

Dim Doc as Word.Document
Set Doc=Application.ActiveInspector.WordEditor
Doc.Range.Paste

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Keep your Outlook categories organized!
http://www.shareit.com/product.html?...4&languageid=1
(German: http://www.VBOffice.net/product.html?pub=6)


Am Sun, 28 Jan 2007 16:47:00 -0800 schrieb Andy:

An unusual one this one but please read on.

If I manually copy a range of cells from an Excel worksheet and then paste
into a Draft email in Outlook (My Outlook 2003 uses HTML as draft format
with
Word as editor) the resulting pasted cells look fine - colors and
formatting
are maintained.

OK now to do it in VBA from Excel.

I copy the range of cells into Clipboard with :-

Range(Cells(aa, 4), Cells(bb, 17)).Select
Selection.Copy

I then obtain the contents of Clipboard and place into a String variable
with :-

Set MyData = New DataObject

MyData.GetFromClipboard
strClip = MyData.GetText

I then create an Outloook object within VBA and build a Draft message
using
strClip as part of the Message body.

This all works OK and the Draft message is created but the resulting
pasted
range of cells in the Draft message does not look very good, the values
are
mis-aligned and wrapped around with any color formatting is lost. It does
not
give me the same pretty result as manually cutting and pasting the cells.

Does anyone know how I can preserve the formatting using the VBA method so
that the resulting pasted cells looks as good as the manual method?

Thanks.

Thanks Michael,

Your idea sounds good but I am not sure how exactly to code your suggestion.

I tried :-

Set Doc=objOutlook.ActiveInspector.WordEditor (where objOutlook is a new
Outlook appliaction object)

but I got a 'Can't set object outside a With Block error'

My other confusion is your line :-

Doc.Range.Paste

Yes - the syntax is valid but how would it relate to the building of an
Outlook message for my Draft email? I am setting the objEmail.Body to be a
string hopeflly including the table that I am trying to cut and paste. In
other words, I don't just need the table copying in, but I need to put some
fixed words around the table as well.

I think my main problem relates to Outlook. When building a message in Excel
VBA the message ends up being Plain text which is messing up the formatting
of the cut & paste table. I need a way of letting Outlook know that it is
HTML or Rich Text format.

Happy to include my code so far if you need it.

Thanks.



"Michael Bauer [MVP - Outlook]" wrote:


With Word as mail editor you can use its object model to insert the
clipboard content. Like this:

Dim Doc as Word.Document
Set Doc=Application.ActiveInspector.WordEditor
Doc.Range.Paste

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Keep your Outlook categories organized!
http://www.shareit.com/product.html?...4&languageid=1
(German: http://www.VBOffice.net/product.html?pub=6)


Am Sun, 28 Jan 2007 16:47:00 -0800 schrieb Andy:

An unusual one this one but please read on.

If I manually copy a range of cells from an Excel worksheet and then paste
into a Draft email in Outlook (My Outlook 2003 uses HTML as draft format
with
Word as editor) the resulting pasted cells look fine - colors and
formatting
are maintained.

OK now to do it in VBA from Excel.

I copy the range of cells into Clipboard with :-

Range(Cells(aa, 4), Cells(bb, 17)).Select
Selection.Copy

I then obtain the contents of Clipboard and place into a String variable
with :-

Set MyData = New DataObject

MyData.GetFromClipboard
strClip = MyData.GetText

I then create an Outloook object within VBA and build a Draft message
using
strClip as part of the Message body.

This all works OK and the Draft message is created but the resulting
pasted
range of cells in the Draft message does not look very good, the values
are
mis-aligned and wrapped around with any color formatting is lost. It does
not
give me the same pretty result as manually cutting and pasting the cells.

Does anyone know how I can preserve the formatting using the VBA method so
that the resulting pasted cells looks as good as the manual method?

Thanks.

The message must be in HTML, of course. RTF may also work but only if your
receiver uses Outlook, too.

The shown line wouldn't cause the error 'Can't set object outside a With
Block error'. So there must be more around it that you doesn't show us.

Here's a complete sample. It's assumed that the HTML e-mail and workbook
'Mappe1.xls' are opened yet and copies the range from cell "B2" to "C6":

http://www.vboffice.net/sample.html?...1&cmd=showitem

Then please see Word's object model. You can exactly determine the Range
before calling its Paste method. Or you paste the table first, then write
additional text at the beginning of the document and some at it's end.

Outlook's object model doesn't allow you to place the cursor into an
e-mail's body. For instance, you could call the Insert commadn via the
toolbar, but if the cursor is currently in the To field then the clipboard
content gets inserted into that field.

If you don't want to use Word as mail editor then you need a workaround.
Then I'd recommend Redemption (www.dimastr.com) to set the cursor position.

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Keep your Outlook categories organized!
http://www.shareit.com/product.html?...4&languageid=1
(German: http://www.VBOffice.net/product.html?pub=6)


Am Mon, 29 Jan 2007 06:58:03 -0800 schrieb Andy:

Thanks Michael,

Your idea sounds good but I am not sure how exactly to code your
suggestion.

I tried :-

Set Doc=objOutlook.ActiveInspector.WordEditor (where objOutlook is a new
Outlook appliaction object)

but I got a 'Can't set object outside a With Block error'

My other confusion is your line :-

Doc.Range.Paste

Yes - the syntax is valid but how would it relate to the building of an
Outlook message for my Draft email? I am setting the objEmail.Body to be a
string hopeflly including the table that I am trying to cut and paste. In
other words, I don't just need the table copying in, but I need to put
some
fixed words around the table as well.

I think my main problem relates to Outlook. When building a message in
Excel
VBA the message ends up being Plain text which is messing up the
formatting
of the cut & paste table. I need a way of letting Outlook know that it is
HTML or Rich Text format.

Happy to include my code so far if you need it.

Thanks.



"Michael Bauer [MVP - Outlook]" wrote:


With Word as mail editor you can use its object model to insert the
clipboard content. Like this:

Dim Doc as Word.Document
Set Doc=Application.ActiveInspector.WordEditor
Doc.Range.Paste

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Keep your Outlook categories organized!
http://www.shareit.com/product.html?...4&languageid=1
(German: http://www.VBOffice.net/product.html?pub=6)


Am Sun, 28 Jan 2007 16:47:00 -0800 schrieb Andy:

An unusual one this one but please read on.

If I manually copy a range of cells from an Excel worksheet and then
paste
into a Draft email in Outlook (My Outlook 2003 uses HTML as draft format
with
Word as editor) the resulting pasted cells look fine - colors and
formatting
are maintained.

OK now to do it in VBA from Excel.

I copy the range of cells into Clipboard with :-

Range(Cells(aa, 4), Cells(bb, 17)).Select
Selection.Copy

I then obtain the contents of Clipboard and place into a String variable
with :-

Set MyData = New DataObject

MyData.GetFromClipboard
strClip = MyData.GetText

I then create an Outloook object within VBA and build a Draft message
using
strClip as part of the Message body.

This all works OK and the Draft message is created but the resulting
pasted
range of cells in the Draft message does not look very good, the values
are
mis-aligned and wrapped around with any color formatting is lost. It
does
not
give me the same pretty result as manually cutting and pasting the
cells.

Does anyone know how I can preserve the formatting using the VBA method
so
that the resulting pasted cells looks as good as the manual method?

Thanks.

Cool - That worked fine but your example has to be run from Outlook with an
New email open.

I am trying to run the whole thing from Excel opening up Outlook as an
Object and creating several Draft emails to cut &paste into each.

Could you provide any more guidance?

Thanks.

"Michael Bauer [MVP - Outlook]" wrote:


The message must be in HTML, of course. RTF may also work but only if your
receiver uses Outlook, too.

The shown line wouldn't cause the error 'Can't set object outside a With
Block error'. So there must be more around it that you doesn't show us.

Here's a complete sample. It's assumed that the HTML e-mail and workbook
'Mappe1.xls' are opened yet and copies the range from cell "B2" to "C6":

http://www.vboffice.net/sample.html?...1&cmd=showitem

Then please see Word's object model. You can exactly determine the Range
before calling its Paste method. Or you paste the table first, then write
additional text at the beginning of the document and some at it's end.

Outlook's object model doesn't allow you to place the cursor into an
e-mail's body. For instance, you could call the Insert commadn via the
toolbar, but if the cursor is currently in the To field then the clipboard
content gets inserted into that field.

If you don't want to use Word as mail editor then you need a workaround.
Then I'd recommend Redemption (www.dimastr.com) to set the cursor position.

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Keep your Outlook categories organized!
http://www.shareit.com/product.html?...4&languageid=1
(German: http://www.VBOffice.net/product.html?pub=6)


Am Mon, 29 Jan 2007 06:58:03 -0800 schrieb Andy:

Thanks Michael,

Your idea sounds good but I am not sure how exactly to code your
suggestion.

I tried :-

Set Doc=objOutlook.ActiveInspector.WordEditor (where objOutlook is a new
Outlook appliaction object)

but I got a 'Can't set object outside a With Block error'

My other confusion is your line :-

Doc.Range.Paste

Yes - the syntax is valid but how would it relate to the building of an
Outlook message for my Draft email? I am setting the objEmail.Body to be a
string hopeflly including the table that I am trying to cut and paste. In
other words, I don't just need the table copying in, but I need to put
some
fixed words around the table as well.

I think my main problem relates to Outlook. When building a message in
Excel
VBA the message ends up being Plain text which is messing up the
formatting
of the cut & paste table. I need a way of letting Outlook know that it is
HTML or Rich Text format.

Happy to include my code so far if you need it.

Thanks.



"Michael Bauer [MVP - Outlook]" wrote:


With Word as mail editor you can use its object model to insert the
clipboard content. Like this:

Dim Doc as Word.Document
Set Doc=Application.ActiveInspector.WordEditor
Doc.Range.Paste

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Keep your Outlook categories organized!
http://www.shareit.com/product.html?...4&languageid=1
(German: http://www.VBOffice.net/product.html?pub=6)


Am Sun, 28 Jan 2007 16:47:00 -0800 schrieb Andy:

An unusual one this one but please read on.

If I manually copy a range of cells from an Excel worksheet and then
paste
into a Draft email in Outlook (My Outlook 2003 uses HTML as draft format
with
Word as editor) the resulting pasted cells look fine - colors and
formatting
are maintained.

OK now to do it in VBA from Excel.

I copy the range of cells into Clipboard with :-

Range(Cells(aa, 4), Cells(bb, 17)).Select
Selection.Copy

I then obtain the contents of Clipboard and place into a String variable
with :-

Set MyData = New DataObject

MyData.GetFromClipboard
strClip = MyData.GetText

I then create an Outloook object within VBA and build a Draft message
using
strClip as part of the Message body.

This all works OK and the Draft message is created but the resulting
pasted
range of cells in the Draft message does not look very good, the values
are
mis-aligned and wrapped around with any color formatting is lost. It
does
not
give me the same pretty result as manually cutting and pasting the
cells.

Does anyone know how I can preserve the formatting using the VBA method
so
that the resulting pasted cells looks as good as the manual method?

Thanks.

From Excel the Application object refers to Excel. So you need a variable
for the Outlook Application object and either use GetObject, or, if Outlook
doesn't run, CreateObject to get the reference to Outlook.

Please note, that the WordEditor property is protected. Calling that
property without having Outlook's instrinsic Application object will prompt
a security dialog.

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Keep your Outlook categories organized!
http://www.shareit.com/product.html?...4&languageid=1
(German: http://www.VBOffice.net/product.html?pub=6)


Am Tue, 30 Jan 2007 10:16:02 -0800 schrieb Andy:

Cool - That worked fine but your example has to be run from Outlook with
an
New email open.

I am trying to run the whole thing from Excel opening up Outlook as an
Object and creating several Draft emails to cut &paste into each.

Could you provide any more guidance?

Thanks.

"Michael Bauer [MVP - Outlook]" wrote:


The message must be in HTML, of course. RTF may also work but only if
your
receiver uses Outlook, too.

The shown line wouldn't cause the error 'Can't set object outside a With
Block error'. So there must be more around it that you doesn't show us.

Here's a complete sample. It's assumed that the HTML e-mail and workbook
'Mappe1.xls' are opened yet and copies the range from cell "B2" to "C6":

http://www.vboffice.net/sample.html?...1&cmd=showitem

Then please see Word's object model. You can exactly determine the Range
before calling its Paste method. Or you paste the table first, then write
additional text at the beginning of the document and some at it's end.

Outlook's object model doesn't allow you to place the cursor into an
e-mail's body. For instance, you could call the Insert commadn via the
toolbar, but if the cursor is currently in the To field then the
clipboard
content gets inserted into that field.

If you don't want to use Word as mail editor then you need a workaround.
Then I'd recommend Redemption (www.dimastr.com) to set the cursor
position.

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Keep your Outlook categories organized!
http://www.shareit.com/product.html?...4&languageid=1
(German: http://www.VBOffice.net/product.html?pub=6)


Am Mon, 29 Jan 2007 06:58:03 -0800 schrieb Andy:

Thanks Michael,

Your idea sounds good but I am not sure how exactly to code your
suggestion.

I tried :-

Set Doc=objOutlook.ActiveInspector.WordEditor (where objOutlook is a new
Outlook appliaction object)

but I got a 'Can't set object outside a With Block error'

My other confusion is your line :-

Doc.Range.Paste

Yes - the syntax is valid but how would it relate to the building of an
Outlook message for my Draft email? I am setting the objEmail.Body to be
a
string hopeflly including the table that I am trying to cut and paste.
In
other words, I don't just need the table copying in, but I need to put
some
fixed words around the table as well.

I think my main problem relates to Outlook. When building a message in
Excel
VBA the message ends up being Plain text which is messing up the
formatting
of the cut & paste table. I need a way of letting Outlook know that it
is
HTML or Rich Text format.

Happy to include my code so far if you need it.

Thanks.



"Michael Bauer [MVP - Outlook]" wrote:


With Word as mail editor you can use its object model to insert the
clipboard content. Like this:

Dim Doc as Word.Document
Set Doc=Application.ActiveInspector.WordEditor
Doc.Range.Paste

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Keep your Outlook categories organized!
http://www.shareit.com/product.html?...4&languageid=1
(German: http://www.VBOffice.net/product.html?pub=6)


Am Sun, 28 Jan 2007 16:47:00 -0800 schrieb Andy:

An unusual one this one but please read on.

If I manually copy a range of cells from an Excel worksheet and then
paste
into a Draft email in Outlook (My Outlook 2003 uses HTML as draft
format
with
Word as editor) the resulting pasted cells look fine - colors and
formatting
are maintained.

OK now to do it in VBA from Excel.

I copy the range of cells into Clipboard with :-

Range(Cells(aa, 4), Cells(bb, 17)).Select
Selection.Copy

I then obtain the contents of Clipboard and place into a String
variable
with :-

Set MyData = New DataObject

MyData.GetFromClipboard
strClip = MyData.GetText

I then create an Outloook object within VBA and build a Draft message
using
strClip as part of the Message body.

This all works OK and the Draft message is created but the resulting
pasted
range of cells in the Draft message does not look very good, the
values
are
mis-aligned and wrapped around with any color formatting is lost. It
does
not
give me the same pretty result as manually cutting and pasting the
cells.

Does anyone know how I can preserve the formatting using the VBA
method
so
that the resulting pasted cells looks as good as the manual method?

Thanks.