|
- Subject: Re: VBA Clipboard Cut & Paste from Excel to Outlook
- Author: Graham Mayor
- Date: 09 Apr 2011
- References: 1
This is a four year old thread? However to get this to work you need to set
a reference in the VBA editor Tools > References to the MSForms 2.0 object
library then you could use
Dim myData As msforms.DataObject
Dim strClip As String
Set myData = New MSForms.DataObject
myData.GetFromClipboard
strClip = dFname.GetText
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
"William Collier" <sk8nmike@verizon.net> wrote in message
news:201147112621usenet@terrranews.com...
a reference in the VBA editor Tools > References to the MSForms 2.0 object
library then you could use
Dim myData As msforms.DataObject
Dim strClip As String
Set myData = New MSForms.DataObject
myData.GetFromClipboard
strClip = dFname.GetText
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP
My web site www.gmayor.comGraham Mayor - Word MVP
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
"William Collier" <sk8nmike@verizon.net> wrote in message
news:201147112621usenet@terrranews.com...
> I'm trying to copy from the clipboard using VBA and Excel. When I run the
> code you have here is get an error: Compile Error: User-defined type not
> defined.
>
> This is on the command -Set MyData = New DataObject
>
> ??
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> code you have here is get an error: Compile Error: User-defined type not
> defined.
>
> This is on the command -Set MyData = New DataObject
>
> ??
>
>> On Sunday, January 28, 2007 7:47 PM And wrote:
>>> 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.
>>>
>> 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.
>
>>> On Monday, January 29, 2007 1:20 AM 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:
>>>
>>> with
>>> formatting
>>> using
>>> pasted
>>> are
>>> not
>>> 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:
>>>
>>> with
>>> formatting
>>> using
>>> pasted
>>> are
>>> not
>
>>>> On Monday, January 29, 2007 9:58 AM And wrote:
>>>> 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:
>>>>
>>>> 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:
>
>>>>> On Tuesday, January 30, 2007 1:49 AM Michael Bauer [MVP - Outlook]
>>>>> wrote:
>>>>> 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:
>>>>>
>>>>> suggestion.
>>>>> some
>>>>> Excel
>>>>> formatting
>>>>> paste
>>>>> does
>>>>> cells.
>>>>> so
>>>>> 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:
>>>>>
>>>>> suggestion.
>>>>> some
>>>>> Excel
>>>>> formatting
>>>>> paste
>>>>> does
>>>>> cells.
>>>>> so
>
>>>>>> On Tuesday, January 30, 2007 1:16 PM And wrote:
>>>>>> 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:
>>>>>> 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:
>
>>>>>>> On Wednesday, January 31, 2007 1:08 AM Michael Bauer [MVP - Outlook]
>>>>>>> wrote:
>>>>>>> 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:
>>>>>>>
>>>>>>> an
>>>>>>> your
>>>>>>> clipboard
>>>>>>> position.
>>>>>>> a
>>>>>>> In
>>>>>>> is
>>>>>>> format
>>>>>>> variable
>>>>>>> values
>>>>>>> method
>>>>>>> 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:
>>>>>>>
>>>>>>> an
>>>>>>> your
>>>>>>> clipboard
>>>>>>> position.
>>>>>>> a
>>>>>>> In
>>>>>>> is
>>>>>>> format
>>>>>>> variable
>>>>>>> values
>>>>>>> method
>
>>>>>>>> On Wednesday, January 31, 2007 4:32 AM And wrote:
>>>>>>>> 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:
>>>>>>>>
>>>>>>>> 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:
>
>>>>>>>>> On Wednesday, January 31, 2007 5:39 AM Michael Bauer [MVP -
>>>>>>>>> Outlook] wrote:
>>>>>>>>> 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:
>>>>>>>>>
>>>>>>>>> example :-
>>>>>>>>> folder
>>>>>>>>> Outlook
>>>>>>>>> prompt
>>>>>>>>> With
>>>>>>>>> workbook
>>>>>>>>> "C6":
>>>>>>>>> Range
>>>>>>>>> write
>>>>>>>>> workaround.
>>>>>>>>> new
>>>>>>>>> an
>>>>>>>>> be
>>>>>>>>> http://www.shareit.com/product.html?productid=300120654&languageid=1
>>>>>>>>> message
>>>>>>>>>
>>>>>>>>> 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:
>>>>>>>>>
>>>>>>>>> example :-
>>>>>>>>> folder
>>>>>>>>> Outlook
>>>>>>>>> prompt
>>>>>>>>> With
>>>>>>>>> workbook
>>>>>>>>> "C6":
>>>>>>>>> Range
>>>>>>>>> write
>>>>>>>>> workaround.
>>>>>>>>> new
>>>>>>>>> an
>>>>>>>>> be
>>>>>>>>> http://www.shareit.com/product.html?productid=300120654&languageid=1
>>>>>>>>> message
>
>>>>>>>>>> On Wednesday, January 31, 2007 7:27 PM And wrote:
>>>>>>>>>> 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:
>>>>>>>>>> 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:
>
>>>>>>>>>>> On Thursday, February 01, 2007 12:16 AM Michael Bauer [MVP -
>>>>>>>>>>> Outlook] wrote:
>>>>>>>>>>> Outlook] wrote:
>>>>>>>>>>> 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:
>>>>>>>>>>>
>>>>>>>>>>> was
>>>>>>>>>>> Body
>>>>>>>>>>> be
>>>>>>>>>>> variable
>>>>>>>>>>> with
>>>>>>>>>>> us.
>>>>>>>>>>> end.
>>>>>>>>>>> the
>>>>>>>>>>> http://www.shareit.com/product.html?productid=300120654&languageid=1
>>>>>>>>>>> to
>>>>>>>>>>> paste.
>>>>>>>>>>> put
>>>>>>>>>>> in
>>>>>>>>>>> it
>>>>>>>>>>> then
>>>>>>>>>>> resulting
>>>>>>>>>>> It
>>>>>>>>>>> method?
>>>>>>>>>>> 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:
>>>>>>>>>>>
>>>>>>>>>>> was
>>>>>>>>>>> Body
>>>>>>>>>>> be
>>>>>>>>>>> variable
>>>>>>>>>>> with
>>>>>>>>>>> us.
>>>>>>>>>>> end.
>>>>>>>>>>> the
>>>>>>>>>>> http://www.shareit.com/product.html?productid=300120654&languageid=1
>>>>>>>>>>> to
>>>>>>>>>>> paste.
>>>>>>>>>>> put
>>>>>>>>>>> in
>>>>>>>>>>> it
>>>>>>>>>>> then
>>>>>>>>>>> resulting
>>>>>>>>>>> It
>>>>>>>>>>> method?
>
>>>>>>>>>>>> On Thursday, February 01, 2007 4:32 AM And wrote:
>>>>>>>>>>>> Thanks again Michael - I'm obviously not famailar wit Doc
>>>>>>>>>>>> objects ( normally
>>>>>>>>>>>> work with Access, Excel and Outook).
>>>>>>>>>>>>
>>>>>>>>>>>> I have tried this code but get error a compliatio error 'Type
>>>>>>>>>>>> error' on Set
>>>>>>>>>>>> objPara = "Some Text" :-
>>>>>>>>>>>>
>>>>>>>>>>>> ' Other Dims
>>>>>>>>>>>> Dim objPara As Word.Paragraph
>>>>>>>>>>>>
>>>>>>>>>>>> Set objEmail = objDrafts.Items.Add
>>>>>>>>>>>>
>>>>>>>>>>>> Set Doc = objEmail.GetInspector.WordEditor
>>>>>>>>>>>>
>>>>>>>>>>>> Set objPara = "Some Text"
>>>>>>>>>>>>
>>>>>>>>>>>> objPara.Copy
>>>>>>>>>>>> wdRn.Paste
>>>>>>>>>>>>
>>>>>>>>>>>> Set wdRn = Doc.Range.End
>>>>>>>>>>>>
>>>>>>>>>>>> Set xlRn = Range(Cells(aa, 4), Cells(bb, 17))
>>>>>>>>>>>> xlRn.Copy
>>>>>>>>>>>>
>>>>>>>>>>>> wdRn.Paste
>>>>>>>>>>>>
>>>>>>>>>>>> There must be an easy way to insert some text with Range
>>>>>>>>>>>> object? I've tried
>>>>>>>>>>>> several properties. The code line Set wdRn = Doc.Range.End also
>>>>>>>>>>>> fails.
>>>>>>>>>>>>
>>>>>>>>>>>> "Michael Bauer [MVP - Outlook]" wrote:
>>>>>>>>>>>> objects ( normally
>>>>>>>>>>>> work with Access, Excel and Outook).
>>>>>>>>>>>>
>>>>>>>>>>>> I have tried this code but get error a compliatio error 'Type
>>>>>>>>>>>> error' on Set
>>>>>>>>>>>> objPara = "Some Text" :-
>>>>>>>>>>>>
>>>>>>>>>>>> ' Other Dims
>>>>>>>>>>>> Dim objPara As Word.Paragraph
>>>>>>>>>>>>
>>>>>>>>>>>> Set objEmail = objDrafts.Items.Add
>>>>>>>>>>>>
>>>>>>>>>>>> Set Doc = objEmail.GetInspector.WordEditor
>>>>>>>>>>>>
>>>>>>>>>>>> Set objPara = "Some Text"
>>>>>>>>>>>>
>>>>>>>>>>>> objPara.Copy
>>>>>>>>>>>> wdRn.Paste
>>>>>>>>>>>>
>>>>>>>>>>>> Set wdRn = Doc.Range.End
>>>>>>>>>>>>
>>>>>>>>>>>> Set xlRn = Range(Cells(aa, 4), Cells(bb, 17))
>>>>>>>>>>>> xlRn.Copy
>>>>>>>>>>>>
>>>>>>>>>>>> wdRn.Paste
>>>>>>>>>>>>
>>>>>>>>>>>> There must be an easy way to insert some text with Range
>>>>>>>>>>>> object? I've tried
>>>>>>>>>>>> several properties. The code line Set wdRn = Doc.Range.End also
>>>>>>>>>>>> fails.
>>>>>>>>>>>>
>>>>>>>>>>>> "Michael Bauer [MVP - Outlook]" wrote:
>
>>>>>>>>>>>>> On Friday, February 02, 2007 12:59 AM Michael Bauer [MVP -
>>>>>>>>>>>>> Outlook] wrote:
>>>>>>>>>>>>> Outlook] wrote:
>>>>>>>>>>>>> There's no difference between Excel Word & Co.: You can't set
>>>>>>>>>>>>> an object
>>>>>>>>>>>>> variable to a non-object.
>>>>>>>>>>>>>
>>>>>>>>>>>>> Please look into the obejct browser (F2) and switch from <All
>>>>>>>>>>>>> Libraries> to
>>>>>>>>>>>>> Word. Select the Range class in the left pane and you can view
>>>>>>>>>>>>> all its
>>>>>>>>>>>>> properties, methods etc. in the right pane.
>>>>>>>>>>>>>
>>>>>>>>>>>>> There you'll see that the End property returns a Long value
>>>>>>>>>>>>> and not an
>>>>>>>>>>>>> object. You can use that and the Start property to set the
>>>>>>>>>>>>> range. That is
>>>>>>>>>>>>> also explained if you select one of the properties and press
>>>>>>>>>>>>> F1.
>>>>>>>>>>>>>
>>>>>>>>>>>>> A sample: If you set Start=0 and End=0 then the cursor is at
>>>>>>>>>>>>> the beginning
>>>>>>>>>>>>> of the document. Now you can write some text into Range.Text.
>>>>>>>>>>>>> Then set the
>>>>>>>>>>>>> Range to the end and paste the table etc.
>>>>>>>>>>>>>
>>>>>>>>>>>>> --
>>>>>>>>>>>>> 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 Thu, 1 Feb 2007 01:32:00 -0800 schrieb Andy:
>>>>>>>>>>>>>
>>>>>>>>>>>>> normally
>>>>>>>>>>>>> Set
>>>>>>>>>>>>> tried
>>>>>>>>>>>>> Document.Range
>>>>>>>>>>>>> the
>>>>>>>>>>>>> more
>>>>>>>>>>>>> the
>>>>>>>>>>>>> http://www.shareit.com/product.html?productid=300120654&languageid=1
>>>>>>>>>>>>> an
>>>>>>>>>>>>> if
>>>>>>>>>>>>> an
>>>>>>>>>>>>> a
>>>>>>>>>>>>> of
>>>>>>>>>>>>> message
>>>>>>>>>>>>> that
>>>>>>>>>>>>> the
>>>>>>>>>>>>> draft
>>>>>>>>>>>>> and
>>>>>>>>>>>>> an object
>>>>>>>>>>>>> variable to a non-object.
>>>>>>>>>>>>>
>>>>>>>>>>>>> Please look into the obejct browser (F2) and switch from <All
>>>>>>>>>>>>> Libraries> to
>>>>>>>>>>>>> Word. Select the Range class in the left pane and you can view
>>>>>>>>>>>>> all its
>>>>>>>>>>>>> properties, methods etc. in the right pane.
>>>>>>>>>>>>>
>>>>>>>>>>>>> There you'll see that the End property returns a Long value
>>>>>>>>>>>>> and not an
>>>>>>>>>>>>> object. You can use that and the Start property to set the
>>>>>>>>>>>>> range. That is
>>>>>>>>>>>>> also explained if you select one of the properties and press
>>>>>>>>>>>>> F1.
>>>>>>>>>>>>>
>>>>>>>>>>>>> A sample: If you set Start=0 and End=0 then the cursor is at
>>>>>>>>>>>>> the beginning
>>>>>>>>>>>>> of the document. Now you can write some text into Range.Text.
>>>>>>>>>>>>> Then set the
>>>>>>>>>>>>> Range to the end and paste the table etc.
>>>>>>>>>>>>>
>>>>>>>>>>>>> --
>>>>>>>>>>>>> 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 Thu, 1 Feb 2007 01:32:00 -0800 schrieb Andy:
>>>>>>>>>>>>>
>>>>>>>>>>>>> normally
>>>>>>>>>>>>> Set
>>>>>>>>>>>>> tried
>>>>>>>>>>>>> Document.Range
>>>>>>>>>>>>> the
>>>>>>>>>>>>> more
>>>>>>>>>>>>> the
>>>>>>>>>>>>> http://www.shareit.com/product.html?productid=300120654&languageid=1
>>>>>>>>>>>>> an
>>>>>>>>>>>>> if
>>>>>>>>>>>>> an
>>>>>>>>>>>>> a
>>>>>>>>>>>>> of
>>>>>>>>>>>>> message
>>>>>>>>>>>>> that
>>>>>>>>>>>>> the
>>>>>>>>>>>>> draft
>>>>>>>>>>>>> and
>
>>>>>>>>>>>>>> On Tuesday, May 13, 2008 12:07 AM Jyothi Muddam wrote:
>>>>>>>>>>>>>> Hi Michael,
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> I am trying to follow the exactly the smae code which you
>>>>>>>>>>>>>> given here.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Private Sub Test()
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> 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
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> ' 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 Xl = GetObject(, "Excel.Application")
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Set Ws = Xl.Workbooks("ColorTest.xls").Worksheets(1)
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Set xlRn = Ws.Range("a1", "d139")
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> xlRn.Copy
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Set Doc = objEmail.Getinspector.WordEditor
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Set wdRn = Doc.Range
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> 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
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> End Sub
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> I am getting object not set error @line....
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Set wdRn = Doc.Range
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> I am using outlook 2003 and try to call this from Excel.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Actually, I would like to do this from Visual Basic.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Is there anyway I can get this work please.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Thanks in advance.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Jyothi
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> I am trying to follow the exactly the smae code which you
>>>>>>>>>>>>>> given here.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Private Sub Test()
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> 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
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> ' 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 Xl = GetObject(, "Excel.Application")
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Set Ws = Xl.Workbooks("ColorTest.xls").Worksheets(1)
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Set xlRn = Ws.Range("a1", "d139")
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> xlRn.Copy
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Set Doc = objEmail.Getinspector.WordEditor
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Set wdRn = Doc.Range
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> 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
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> End Sub
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> I am getting object not set error @line....
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Set wdRn = Doc.Range
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> I am using outlook 2003 and try to call this from Excel.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Actually, I would like to do this from Visual Basic.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Is there anyway I can get this work please.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Thanks in advance.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Jyothi
>
>
| 07 Apr 2011 | Re: VBA Clipboard Cut & Paste from Excel to Outlook | .William Collier |
| 09 Apr 2011 |  Re: VBA Clipboard Cut & Paste from Excel to Outlook | .Graham Mayor |
Contact Us
All times are in (US) Eastern Daylight Time (GMT -4:00)
All times are in (US) Eastern Daylight Time (GMT -4:00)