VBA Clipboard Cut & Paste from Excel to Outlook

A

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.
 
M

Michael Bauer [MVP - Outlook]

A

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.
 
M

Michael Bauer [MVP - Outlook]

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:
 
A

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.
 
M

Michael Bauer [MVP - 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
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:
 
A

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.
 
A

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.
 
M

Michael Bauer [MVP - Outlook]

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:
 
A

Andy

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.
 
M

Michael Bauer [MVP - Outlook]

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:
 
J

Jyothi Muddam

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
 
M

Michael Bauer [MVP - Outlook]

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.

--
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:
 
J

JyothiMallikarjun

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.
 
W

William Collier

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

??
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.
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
Click to expand...
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:
Click to expand...
Click to expand...
On Tuesday, January 30, 2007 1:49 AM 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:

suggestion.
some
Excel
formatting
paste
does
cells.
so
Click to expand...
Click to expand...
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:
Click to expand...
Click to expand...
On Wednesday, January 31, 2007 1:08 AM 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:

an
your
clipboard
position.
a
In
is
format
variable
values
method
Click to expand...
Click to expand...
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:
Click to expand...
Click to expand...
On Wednesday, January 31, 2007 5:39 AM 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:

example :-
folder
Outlook
prompt
With
workbook
"C6":
Range
write
workaround.
new
an
be
http://www.shareit.com/product.html?productid=300120654&languageid=1
message
Click to expand...
Click to expand...
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:
Click to expand...
Click to expand...
On Thursday, February 01, 2007 12:16 AM Michael Bauer [MVP - 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?
Click to expand...
Click to expand...
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:
Click to expand...
Click to expand...
Click to expand...
 
G

Graham Mayor

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


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>




William Collier said:
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

??
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.
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
Click to expand...
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:
Click to expand...
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
Click to expand...
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:
Click to expand...
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
Click to expand...
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:
Click to expand...
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
Click to expand...
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:
Click to expand...
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?
Click to expand...
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:
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Paste to excel from word - auto format problem 1
Pasting from the clipboard 3
Copy/Paste (Clipboard) Issues 1
Pasting a cell into bookmarked table from excel vba 1
text from clipboard -- strange behaviour 0
Selecting Range of Copied Text 2
clipboard .GetText behaviour 0
Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0

Top