VBA Clipboard Cut & Paste from Excel to Outlook

External Since: Mar 23, 2006 Posts: 296

Excellent - That worked fine. The Draft email was created and the range was
pasted into the Draft email complete with formatting - well done.

One last thing. It would be nice to insert some text before and after the
pasted table.

After the objEmail is created I have experimented with :-

objEmail.Body.PrintText Text:="Please find table below :-"
objEmail.Body.PrintParagraph
wdRn.Paste ' to paste in the Word Range
objEmail.Body.PrintParagraph
objEmail.Body.PrintText Text:="Regards etc."

but I get 424 - 'Object Required'

any last thoughts?

Thanks.

"Michael Bauer [MVP - Outlook]" wrote:

>
>
> ActiveInspector exists if an item is opened. In your code call
>
> Set Doc=objEmail.Getinspector.WordEditor
>
> after objEmail is being created.
>
> --
> 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 Wed, 31 Jan 2007 01:32:01 -0800 schrieb Andy:
>
> > 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.
> >>>>>>
> >>>>
> >>
>

External Since: Aug 04, 2006 Posts: 1361

Yes, as I mentioned earlier use Word's object model, that is Document.Range
instead of MailItem.Body. First insert some text, then set the Range to the
Document's end, paste the table, set the Range to its end again and add more
text.

--
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 Wed, 31 Jan 2007 16:27:01 -0800 schrieb Andy:

> Excellent - That worked fine. The Draft email was created and the range
was
> pasted into the Draft email complete with formatting - well done.
>
> One last thing. It would be nice to insert some text before and after the
> pasted table.
>
> After the objEmail is created I have experimented with :-
>
> objEmail.Body.PrintText Text:="Please find table below :-"
> objEmail.Body.PrintParagraph
> wdRn.Paste ' to paste in the Word Range
> objEmail.Body.PrintParagraph
> objEmail.Body.PrintText Text:="Regards etc."
>
> but I get 424 - 'Object Required'
>
> any last thoughts?
>
> Thanks.
>
>
> "Michael Bauer [MVP - Outlook]" wrote:
>
>>
>>
>> ActiveInspector exists if an item is opened. In your code call
>>
>> Set Doc=objEmail.Getinspector.WordEditor
>>
>> after objEmail is being created.
>>
>> --
>> 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 Wed, 31 Jan 2007 01:32:01 -0800 schrieb Andy:
>>
>>> 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.
>>>>>>>>
>>>>>>
>>>>
>>