MS Office Forum / Outlook / Programming VBA / May 2008
VBA Clipboard Cut & Paste from Excel to Outlook
|
|
Thread rating:  |
Andy - 29 Jan 2007 00:47 GMT 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.
Michael Bauer [MVP - Outlook] - 29 Jan 2007 06:20 GMT 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
 Signature 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. > [quoted text clipped - 30 lines] > > Thanks. Andy - 29 Jan 2007 14:58 GMT 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.
> With Word as mail editor you can use its object model to insert the > clipboard content. Like this: [quoted text clipped - 43 lines] > > > > Thanks. Michael Bauer [MVP - Outlook] - 30 Jan 2007 06:49 GMT 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.
 Signature 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, > [quoted text clipped - 73 lines] >>> >>> Thanks. Andy - 30 Jan 2007 18:16 GMT 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.
> The message must be in HTML, of course. RTF may also work but only if your > receiver uses Outlook, too. [quoted text clipped - 104 lines] > >>> > >>> Thanks. Michael Bauer [MVP - Outlook] - 31 Jan 2007 06:08 GMT 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.
 Signature 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. [quoted text clipped - 114 lines] >>>>> >>>>> Thanks. Andy - 31 Jan 2007 09:32 GMT 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.
> 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 [quoted text clipped - 133 lines] > >>>>> > >>>>> Thanks. Michael Bauer [MVP - Outlook] - 31 Jan 2007 10:39 GMT ActiveInspector exists if an item is opened. In your code call
Set Doc=objEmail.Getinspector.WordEditor
after objEmail is being created.
 Signature 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. > [quoted text clipped - 159 lines] >>>>>> Set Doc=Application.ActiveInspector.WordEditor >>>>>> Doc.Range.Paste http://www.shareit.com/product.html?productid=300120654&languageid=1
>>>>>> (German: http://www.VBOffice.net/product.html?pub=6) >>>>>> [quoted text clipped - 48 lines] >>>>>>> >>>>>>> Thanks. Andy - 01 Feb 2007 00:27 GMT 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.
> ActiveInspector exists if an item is opened. In your code call > [quoted text clipped - 233 lines] > >>>>>>> > >>>>>>> Thanks. Michael Bauer [MVP - Outlook] - 01 Feb 2007 05:16 GMT 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.
 Signature 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. [quoted text clipped - 151 lines] >>>>>> Then I'd recommend Redemption (www.dimastr.com) to set the cursor >>>> position. http://www.shareit.com/product.html?productid=300120654&languageid=1
>>>>>> (German: http://www.VBOffice.net/product.html?pub=6) >>>>>> [quoted text clipped - 101 lines] >>>>>>>>> >>>>>>>>> Thanks. Andy - 01 Feb 2007 09:32 GMT 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.
> 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 [quoted text clipped - 238 lines] > >>>>>>>> formatting > >>>>>>>>> are maintained. Michael Bauer [MVP - Outlook] - 02 Feb 2007 05:59 GMT 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.
 Signature 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:
> Thanks again Michael - I'm obviously not famailar wit Doc objects ( normally > work with Access, Excel and Outook). [quoted text clipped - 145 lines] >>>> prompt >>>>>> a security dialog. http://www.shareit.com/product.html?productid=300120654&languageid=1
>>>>>> (German: http://www.VBOffice.net/product.html?pub=6) >>>>>> [quoted text clipped - 120 lines] >>>>>>>>>> formatting >>>>>>>>>>> are maintained. Jyothi Muddam - 13 May 2008 05:07 GMT 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
Michael Bauer [MVP - Outlook] - 13 May 2008 06:41 GMT In that line the error means Doc isn't set. Probably you don't use Word as e-mail editor, but that is necessary for this code to work.
 Signature Best regards Michael Bauer - MVP Outlook
: Outlook Categories? Category Manager Is Your Tool: : <http://www.vboffice.net/product.html?pub=6&lang=en>
Am Mon, 12 May 2008 21:07:25 -0700 schrieb Jyothi Muddam:
> Hi Michael, > [quoted text clipped - 78 lines] > > Jyothi JyothiMallikarjun@gmail.com - 14 May 2008 02:23 GMT Thanks Michael,
You are right. Thsi option is disabled in my outlook 2003 automatically. Is there anyway that I can enable this option.
Actually I don't want to use Word as editor. For this you suggested for using Redemption. My clients also cna have different verison of outlook i.e. outlook XP/ 2000/2003/2007 etc. At my development server I am running Outlook 2003.
Cna I have any help on this please. I am trying to use Redemption.RDOs. But not having much expertise in VBA I lost.
Can I have any code sample for this please.
Really appreciate your time.
With Thanks.
Jyothi.
On May 13, 5:41 pm, "Michael Bauer [MVP - Outlook]" <m...@mvps.org> wrote:
> In that line the error means Doc isn't set. Probably you don't use Word as > e-mail editor, but that is necessary for this code to work. [quoted text clipped - 93 lines] > > - Show quoted text - Michael Bauer [MVP - Outlook] - 14 May 2008 06:32 GMT If you don't want to use Word, you have to use the Redemption. Please look at Dmitry's site (http://www.dimastr.com/redemption/safeinspector.htm), there're tons of samples.
 Signature Best regards Michael Bauer - MVP Outlook
: Outlook Categories? Category Manager Is Your Tool: : <http://www.vboffice.net/product.html?pub=6&lang=en>
Am Tue, 13 May 2008 18:23:22 -0700 (PDT) schrieb JyothiMallikarjun@gmail.com:
> Thanks Michael, > [quoted text clipped - 119 lines] >> >> - Show quoted text -
|
|
|