(Msg. 1) Posted: Sun Jan 28, 2007 4:47 pm
Post subject: VBA Clipboard Cut & Paste from Excel to Outlook Archived from groups: microsoft>public>outlook>program_vba (more info?)
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.
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?
(Msg. 2) Posted: Mon Jan 29, 2007 7:20 am
Post subject: Re: VBA Clipboard Cut & Paste from Excel to Outlook [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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
> 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.
(Msg. 3) Posted: Mon Jan 29, 2007 7:20 am
Post subject: Re: VBA Clipboard Cut & Paste from Excel to Outlook [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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?productid=300120654&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.
>
(Msg. 4) Posted: Tue Jan 30, 2007 7:49 am
Post subject: Re: VBA Clipboard Cut & Paste from Excel to Outlook [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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":
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.
> 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?productid=300120654&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.
>>
(Msg. 5) Posted: Tue Jan 30, 2007 10:16 am
Post subject: Re: VBA Clipboard Cut & Paste from Excel to Outlook [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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?mnu=2&smp=41&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?productid=300120654&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?productid=300120654&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.
> >>
>
(Msg. 6) Posted: Wed Jan 31, 2007 7:08 am
Post subject: Re: VBA Clipboard Cut & Paste from Excel to Outlook [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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.
> 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?mnu=2&smp=41&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?productid=300120654&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?productid=300120654&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.
>>>>
>>
(Msg. 7) Posted: Wed Jan 31, 2007 7:08 am
Post subject: Re: VBA Clipboard Cut & Paste from Excel to Outlook [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Thanks again Michael.
I think the key problem I am having is in this line of code in your example :-
Set Doc = Application.ActiveInspector.WordEditor
It works fine in the context of your example but if I create a Draft folder
item as below, it fails :-
Dim objOutlook As Outlook.Application
Dim objOutlookExp As Object
Dim objDrafts As Object
Dim objEmail As Object
Dim strBody, strTitle, strTo as String
Dim Doc As Word.Document
Dim wdRn As Word.Range
Dim Xl As Excel.Application
Dim Ws As Excel.Worksheet
Dim xlRn As Excel.Range
Set objOutlook = New Outlook.Application
Set Doc = objOutlook.ActiveInspector.WordEditor
' Open the outlook drafts folder
Set objDrafts = objOutlook.Session.GetDefaultFolder(olFolderDrafts)
If objDrafts = "Drafts" Then
' Create new email in Drafts folder
Set objEmail = objDrafts.Items.Add
Set wdRn = Doc.Range
Set Xl = GetObject(, "Excel.Application")
Set Ws = Xl.Workbooks("Mappe1.xls").Worksheets(1)
Set xlRn = Ws.Range("b2", "c6")
xlRn.Copy
wdRn.Paste
strBody = "This is where I need to paste xlRn, maybe using a
DataObject.GetFromClipboard"
strTitle = "Excel to Outlook Paste"
strTo = Ws.Range("a1", "a1") ' email adresss in A1 in worksheet
objEmail.To = strTo
objEmail.Body = strBody
objEmail.Subject = strTitle
Set objDoc = objEmail.Attachments
objDoc.Add strAttach
' Save email in drafts folder
objEmail.Close olSave
Else
MsgBox "No Drafts Folder"
End If
-----------------
This line in the above fails with Object not set error :-
Set Doc = objOutlook.ActiveInspector.WordEditor
Also the line below needs to paste into the Draft email i.e. into the Body
of email :-
wdRn.Paste
Any further thoughts - we are almost there? Maybe all this code could be
driven from Outlook.
"Michael Bauer [MVP - Outlook]" wrote:
>
> 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?productid=300120654&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?mnu=2&smp=41&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?productid=300120654&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?productid=300120654&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.
> >>>>
> >>
>
(Msg. 8) Posted: Wed Jan 31, 2007 11:39 am
Post subject: Re: VBA Clipboard Cut & Paste from Excel to Outlook [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
ActiveInspector exists if an item is opened. In your code call
> Thanks again Michael.
>
> I think the key problem I am having is in this line of code in your
example :-
>
> Set Doc = Application.ActiveInspector.WordEditor
>
> It works fine in the context of your example but if I create a Draft
folder
> item as below, it fails :-
>
> Dim objOutlook As Outlook.Application
> Dim objOutlookExp As Object
> Dim objDrafts As Object
> Dim objEmail As Object
> Dim strBody, strTitle, strTo as String
>
> Dim Doc As Word.Document
> Dim wdRn As Word.Range
> Dim Xl As Excel.Application
> Dim Ws As Excel.Worksheet
> Dim xlRn As Excel.Range
>
> Set objOutlook = New Outlook.Application
>
> Set Doc = objOutlook.ActiveInspector.WordEditor
>
> ' Open the outlook drafts folder
>
> Set objDrafts = objOutlook.Session.GetDefaultFolder(olFolderDrafts)
>
> If objDrafts = "Drafts" Then
>
> ' Create new email in Drafts folder
>
> Set objEmail = objDrafts.Items.Add
>
> Set wdRn = Doc.Range
>
> Set Xl = GetObject(, "Excel.Application")
> Set Ws = Xl.Workbooks("Mappe1.xls").Worksheets(1)
>
> Set xlRn = Ws.Range("b2", "c6")
> xlRn.Copy
>
> wdRn.Paste
>
> strBody = "This is where I need to paste xlRn, maybe using a
> DataObject.GetFromClipboard"
> strTitle = "Excel to Outlook Paste"
> strTo = Ws.Range("a1", "a1") ' email adresss in A1 in worksheet
> objEmail.To = strTo
> objEmail.Body = strBody
> objEmail.Subject = strTitle
> Set objDoc = objEmail.Attachments
> objDoc.Add strAttach
>
> ' Save email in drafts folder
>
> objEmail.Close olSave
> Else
> MsgBox "No Drafts Folder"
> End If
> -----------------
>
> This line in the above fails with Object not set error :-
>
> Set Doc = objOutlook.ActiveInspector.WordEditor
>
> Also the line below needs to paste into the Draft email i.e. into the Body
> of email :-
>
> wdRn.Paste
>
> Any further thoughts - we are almost there? Maybe all this code could be
> driven from Outlook.
>
>
> "Michael Bauer [MVP - Outlook]" wrote:
>
>>
>> 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?productid=300120654&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?mnu=2&smp=41&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?productid=300120654&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?productid=300120654&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.
>>>>>>
>>>>
>>
All times are: Eastern Time (US & Canada) Goto page 1, 2, 3
Page 1 of 3
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Search
Profile
Private Messages
Log in
Office