Copy a range from Excel to Oulook MailItem's body as Bitmap

[WhytheQ]

Ok - here we go:

I've got my formatted numbers in an Excel workbook:
Sheets(1).Range("A118").
If I run the following in an Excel code module:

Sheets(1).Range("A118").CopyPicture xlScreen, xlBitmap

and then:

activate an empty Outlook MailItem.
do control & v, the data appears in the email in Bitmap format in the body of the mail, which is what I want.

An alternative manual way of doing the above is to simply copy the
range then open the MailItem and do Edit>PasteSpecial>Bitmap and the
formatted range appears as a bitmap in the body of the mail.


How do I automate all of the above - preferably without using the
SendKey method?
I need it in a Bitmap format because my clients are using
BlackBerries.
I've looked through Ron's website but can't get a definite answer from
that resource.

Any help greatly appreciated
Jason

 

[Michael Bauer [MVP - Outlook]]

With Word as e-mail editor you can use this sample:
http://www.vboffice.net/sample.html?mnu=2&lang=en&smp=41&cmd=showitem&pub=6

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Organize eMails:
<http://www.vboffice.net/product.html?id=2006063&cmd=detail&lang=en&pub=6>

Am Thu, 18 Oct 2007 08:44:59 -0000 schrieb WhytheQ:

Ok - here we go:

I've got my formatted numbers in an Excel workbook:
Sheets(1).Range("A118").
If I run the following in an Excel code module:

Sheets(1).Range("A118").CopyPicture xlScreen, xlBitmap

and then:

of the mail, which is what I want.

 

[WhytheQ]

Cheers Michael,
If you alreay have something in the clipboard (via
"Sheets(1).Range("A118").CopyPicture xlScreen, xlBitmap") is there
no way of automating a simple pasting of this data into the body of a
mailitem.
Problem is that I'm executing this code from Excel and I need to
somehow activate the mailitem and then paste the data from the
clipboard, which was put into the clipboard whilst in Excel.

Jason

 

[Michael Bauer [MVP - Outlook]]

Not pasting is the problem, but setting the focus into the body is.

You could still use the sample if you're using Word as mail editor. Then
skip the copy stuff and only set the Word Range and call Paste.

If you do not use Word as mail editor you either can use Redemption
(www.dimastr.com), which also allows to set the cursor, or use Win32 API
calls to set the cursor yourself. Here's a sampel for how to do the latter
in OL2k or OL03:
http://www.vboffice.net/sample.html?lang=en&mnu=2&smp=20&cmd=showitem&pub=6


--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Organize eMails:
<http://www.vboffice.net/product.html?id=2006063&cmd=detail&lang=en&pub=6>

Am Fri, 19 Oct 2007 01:38:33 -0700 schrieb WhytheQ:

 

[WhytheQ]

Thanks for all the help Michael. I've still not quite resolved this
query.

Using one of the links you provided I've created the following. Had to
adapt it slightly as I am putting my code in a module behind Excel.

'==========================================================
Sub CopyFromExcelIntoEMail()

Dim OutApp As Object
Dim Doc As Word.Document
Dim wdRn As Word.Range

Set OutApp = CreateObject("Outlook.Application")
Set oItem = OutApp.CreateItem(0)

oItem.Display

Set Doc = OutApp.ActiveInspector.WordEditor
Set wdRn = Doc.Range

ActiveSheet.Range("A1").Copy
wdRn.Paste

End Sub
'==========================================================

The only problem is that there is a security prompt half way through
this routine. Any ideas of a way around it? .... or is Redemption the
way to go?

I checked out the redemption site. Found some really useful stuff
(especially using SafeItem to automatically send emails using 2003
without annoying security prompts)
Unfortunately in the link you provided for Redemption
(www.dimastr.com) I couldn't
find any samples for setting the cursor - can you point me in the
right direction please?


Any help is much appreciated,
JasonQ

Not pasting is the problem, but setting the focus into the body is.

You could still use the sample if you're using Word as mail editor. Then
skip the copy stuff and only set the Word Range and call Paste.

If you do not use Word as mail editor you either can use Redemption
(www.dimastr.com), which also allows to set the cursor, or use Win32 API
calls to set the cursor yourself. Here's a sampel for how to do the latter
in OL2k or OL03:http://www.vboffice.net/sample.html?lang=en&mnu=2&smp=20&cmd=showitem...

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Organize eMails:
<http://www.vboffice.net/product.html?id=2006063&cmd=detail&lang=en&pub=6>

Am Fri, 19 Oct 2007 01:38:33 -0700 schrieb WhytheQ:

Cheers Michael,
If you alreay have something in the clipboard (via
"Sheets(1).Range("A118").CopyPicture xlScreen, xlBitmap") is there
no way of automating a simple pasting of this data into the body of a
mailitem.
Problem is that I'm executing this code from Excel and I need to
somehow activate the mailitem and then paste the data from the
clipboard, which was put into the clipboard whilst in Excel.

Jason

With Word as e-mail editor you can use this

sample:http://www.vboffice.net/sample.html?mnu=2&lang=en&smp=41&cmd=showitem...
<http://www.vboffice.net/product.html?id=2006063&cmd=detail&lang=en&pub=6>

Am Thu, 18 Oct 2007 08:44:59 -0000 schrieb WhytheQ:
Ok - here we go:
I've got my formatted numbers in an Excel workbook:
Sheets(1).Range("A118").
If I run the following in an Excel code module:
Sheets(1).Range("A118").CopyPicture xlScreen, xlBitmap
and then:
activate an empty Outlook MailItem.
do control & v, the data appears in the email in Bitmap format in the

body

of the mail, which is what I want.
An alternative manual way of doing the above is to simply copy the
range then open the MailItem and do Edit>PasteSpecial>Bitmap and the
formatted range appears as a bitmap in the body of the mail.
How do I automate all of the above - preferably without using the
SendKey method?
I need it in a Bitmap format because my clients are using
BlackBerries.
I've looked through Ron's website but can't get a definite answer from
that resource.
Any help greatly appreciated
Jason- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Michael Bauer [MVP - Outlook]]

The security prompts when accessing the WordEditor property from 'outside'.
With Redemption you can use the SafeInspector.WordEditor, which also returns
a Word.Document, to bypass the security. Then you don't need to set the
cursor.

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Synchronize Color Categories & Ensure that Every Item Gets Categorized:
<http://www.vboffice.net/product.html?id=2006063&cmd=detail&lang=en&pub=6>

Am Tue, 30 Oct 2007 11:36:18 -0000 schrieb WhytheQ:

Thanks for all the help Michael. I've still not quite resolved this
query.

Using one of the links you provided I've created the following. Had to
adapt it slightly as I am putting my code in a module behind Excel.

'==========================================================
Sub CopyFromExcelIntoEMail()

Dim OutApp As Object
Dim Doc As Word.Document
Dim wdRn As Word.Range

Set OutApp = CreateObject("Outlook.Application")
Set oItem = OutApp.CreateItem(0)

oItem.Display

Set Doc = OutApp.ActiveInspector.WordEditor
Set wdRn = Doc.Range

ActiveSheet.Range("A1").Copy
wdRn.Paste

End Sub
'==========================================================

The only problem is that there is a security prompt half way through
this routine. Any ideas of a way around it? .... or is Redemption the
way to go?

I checked out the redemption site. Found some really useful stuff
(especially using SafeItem to automatically send emails using 2003
without annoying security prompts)
Unfortunately in the link you provided for Redemption
(www.dimastr.com) I couldn't
find any samples for setting the cursor - can you point me in the
right direction please?


Any help is much appreciated,
JasonQ

Not pasting is the problem, but setting the focus into the body is.

You could still use the sample if you're using Word as mail editor. Then
skip the copy stuff and only set the Word Range and call Paste.

If you do not use Word as mail editor you either can use Redemption
(www.dimastr.com), which also allows to set the cursor, or use Win32 API
calls to set the cursor yourself. Here's a sampel for how to do the latter
in OL2k or OL03:http://www.vboffice.net/sample.html?lang=en&mnu=2&smp=20&cmd=showitem...

Am Fri, 19 Oct 2007 01:38:33 -0700 schrieb WhytheQ:

Cheers Michael,
If you alreay have something in the clipboard (via
"Sheets(1).Range("A118").CopyPicture xlScreen, xlBitmap") is there
no way of automating a simple pasting of this data into the body of a
mailitem.
Problem is that I'm executing this code from Excel and I need to
somehow activate the mailitem and then paste the data from the
clipboard, which was put into the clipboard whilst in Excel.

Jason

On 19 Oct, 06:04, "Michael Bauer [MVP - Outlook]" <[email protected]>
wrote:
With Word as e-mail editor you can use this

sample:http://www.vboffice.net/sample.html?mnu=2&lang=en&smp=41&cmd=showitem...

Am Thu, 18 Oct 2007 08:44:59 -0000 schrieb WhytheQ:

Ok - here we go:

I've got my formatted numbers in an Excel workbook:
Sheets(1).Range("A118").
If I run the following in an Excel code module:

Sheets(1).Range("A118").CopyPicture xlScreen, xlBitmap

and then:
activate an empty Outlook MailItem.
do control & v, the data appears in the email in Bitmap format in the body

of the mail, which is what I want.

An alternative manual way of doing the above is to simply copy the
range then open the MailItem and do Edit>PasteSpecial>Bitmap and the
formatted range appears as a bitmap in the body of the mail.

How do I automate all of the above - preferably without using the
SendKey method?
I need it in a Bitmap format because my clients are using
BlackBerries.
I've looked through Ron's website but can't get a definite answer from
that resource.

Any help greatly appreciated
Jason- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[WhytheQ]

If I set this up using redemption will the code run on my colleague's
machines?
I'm pretty sure my company won't be interested in paying a
subscription for Redemption - is there a way of doing the above
without Redemption.
Maybe have to try the last option: Win32 API. Was hoping not to have
to head into API calls.

J

The security prompts when accessing the WordEditor property from 'outside'.
With Redemption you can use the SafeInspector.WordEditor, which also returns
a Word.Document, to bypass the security. Then you don't need to set the
cursor.

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Synchronize Color Categories & Ensure that Every Item Gets Categorized:
<http://www.vboffice.net/product.html?id=2006063&cmd=detail&lang=en&pub=6>

Am Tue, 30 Oct 2007 11:36:18 -0000 schrieb WhytheQ:

Thanks for all the help Michael. I've still not quite resolved this
query.

Using one of the links you provided I've created the following. Had to
adapt it slightly as I am putting my code in a module behind Excel.

'==========================================================
Sub CopyFromExcelIntoEMail()

Dim OutApp As Object
Dim Doc As Word.Document
Dim wdRn As Word.Range

Set OutApp = CreateObject("Outlook.Application")
Set oItem = OutApp.CreateItem(0)

Set Doc = OutApp.ActiveInspector.WordEditor
Set wdRn = Doc.Range

End Sub
'==========================================================

The only problem is that there is a security prompt half way through
this routine. Any ideas of a way around it? .... or is Redemption the
way to go?

I checked out the redemption site. Found some really useful stuff
(especially using SafeItem to automatically send emails using 2003
without annoying security prompts)
Unfortunately in the link you provided for Redemption
(www.dimastr.com) I couldn't
find any samples for setting the cursor - can you point me in the
right direction please?

Any help is much appreciated,
JasonQ

Not pasting is the problem, but setting the focus into the body is.
You could still use the sample if you're using Word as mail editor. Then
skip the copy stuff and only set the Word Range and call Paste.
If you do not use Word as mail editor you either can use Redemption
(www.dimastr.com), which also allows to set the cursor, or use Win32 API
calls to set the cursor yourself. Here's a sampel for how to do the latter
in OL2k or

OL03:http://www.vboffice.net/sample.html?lang=en&mnu=2&smp=20&cmd=showitem...
<http://www.vboffice.net/product.html?id=2006063&cmd=detail&lang=en&pub=6>

Am Fri, 19 Oct 2007 01:38:33 -0700 schrieb WhytheQ:
Cheers Michael,
If you alreay have something in the clipboard (via
"Sheets(1).Range("A118").CopyPicture xlScreen, xlBitmap") is there
no way of automating a simple pasting of this data into the body of a
mailitem.
Problem is that I'm executing this code from Excel and I need to
somehow activate the mailitem and then paste the data from the
clipboard, which was put into the clipboard whilst in Excel.
Jason
On 19 Oct, 06:04, "Michael Bauer [MVP - Outlook]" <[email protected]>
wrote:
With Word as e-mail editor you can use this

sample:http://www.vboffice.net/sample.html?mnu=2&lang=en&smp=41&cmd=showitem...
<http://www.vboffice.net/product.html?id=2006063&cmd=detail&lang=en&pub=6>

Am Thu, 18 Oct 2007 08:44:59 -0000 schrieb WhytheQ:
Ok - here we go:
I've got my formatted numbers in an Excel workbook:
Sheets(1).Range("A118").
If I run the following in an Excel code module:
Sheets(1).Range("A118").CopyPicture xlScreen, xlBitmap
and then:
activate an empty Outlook MailItem.
do control & v, the data appears in the email in Bitmap format in the
body
of the mail, which is what I want.
An alternative manual way of doing the above is to simply copy the
range then open the MailItem and do Edit>PasteSpecial>Bitmap and the
formatted range appears as a bitmap in the body of the mail.
How do I automate all of the above - preferably without using the
SendKey method?
I need it in a Bitmap format because my clients are using
BlackBerries.
I've looked through Ron's website but can't get a definite answer from
that resource.
Any help greatly appreciated
Jason- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Michael Bauer [MVP - Outlook]]

Maybe it's possible for you to have the code in Outlook VBA instead of
Excel? In OL 03, by using the instrinsic Application object, that would be
without security issues.

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Synchronize Color Categories & Ensure that Every Item Gets Categorized:
<http://www.vboffice.net/product.html?id=2006063&cmd=detail&lang=en&pub=6>

Am Wed, 31 Oct 2007 04:52:21 -0700 schrieb WhytheQ:

If I set this up using redemption will the code run on my colleague's
machines?
I'm pretty sure my company won't be interested in paying a
subscription for Redemption - is there a way of doing the above
without Redemption.
Maybe have to try the last option: Win32 API. Was hoping not to have
to head into API calls.

J

The security prompts when accessing the WordEditor property from 'outside'.
With Redemption you can use the SafeInspector.WordEditor, which also returns
a Word.Document, to bypass the security. Then you don't need to set the
cursor.

Am Tue, 30 Oct 2007 11:36:18 -0000 schrieb WhytheQ:

Thanks for all the help Michael. I've still not quite resolved this
query.

Using one of the links you provided I've created the following. Had to
adapt it slightly as I am putting my code in a module behind Excel.

'==========================================================
Sub CopyFromExcelIntoEMail()

Dim OutApp As Object
Dim Doc As Word.Document
Dim wdRn As Word.Range

Set OutApp = CreateObject("Outlook.Application")
Set oItem = OutApp.CreateItem(0)

Set Doc = OutApp.ActiveInspector.WordEditor
Set wdRn = Doc.Range

End Sub
'==========================================================

The only problem is that there is a security prompt half way through
this routine. Any ideas of a way around it? .... or is Redemption the
way to go?

I checked out the redemption site. Found some really useful stuff
(especially using SafeItem to automatically send emails using 2003
without annoying security prompts)
Unfortunately in the link you provided for Redemption
(www.dimastr.com) I couldn't
find any samples for setting the cursor - can you point me in the
right direction please?

Any help is much appreciated,
JasonQ

On 19 Oct, 12:23, "Michael Bauer [MVP - Outlook]" <[email protected]>
wrote:
Not pasting is the problem, but setting the focus into the body is.

You could still use the sample if you're using Word as mail editor. Then
skip the copy stuff and only set the Word Range and call Paste.

If you do not use Word as mail editor you either can use Redemption
(www.dimastr.com), which also allows to set the cursor, or use Win32 API
calls to set the cursor yourself. Here's a sampel for how to do the latter
in OL2k or

OL03:http://www.vboffice.net/sample.html?lang=en&mnu=2&smp=20&cmd=showitem...

Am Fri, 19 Oct 2007 01:38:33 -0700 schrieb WhytheQ:

Cheers Michael,
If you alreay have something in the clipboard (via
"Sheets(1).Range("A118").CopyPicture xlScreen, xlBitmap") is there
no way of automating a simple pasting of this data into the body of a
mailitem.
Problem is that I'm executing this code from Excel and I need to
somehow activate the mailitem and then paste the data from the
clipboard, which was put into the clipboard whilst in Excel.

Jason

On 19 Oct, 06:04, "Michael Bauer [MVP - Outlook]" <[email protected]>
wrote:
With Word as e-mail editor you can use this

sample:http://www.vboffice.net/sample.html?mnu=2&lang=en&smp=41&cmd=showitem...

Am Thu, 18 Oct 2007 08:44:59 -0000 schrieb WhytheQ:

Ok - here we go:

I've got my formatted numbers in an Excel workbook:
Sheets(1).Range("A118").
If I run the following in an Excel code module:

Sheets(1).Range("A118").CopyPicture xlScreen, xlBitmap

and then:
activate an empty Outlook MailItem.
do control & v, the data appears in the email in Bitmap format in the
body

of the mail, which is what I want.

An alternative manual way of doing the above is to simply copy the
range then open the MailItem and do Edit>PasteSpecial>Bitmap and the
formatted range appears as a bitmap in the body of the mail.

How do I automate all of the above - preferably without using the
SendKey method?
I need it in a Bitmap format because my clients are using
BlackBerries.
I've looked through Ron's website but can't get a definite answer from
that resource.

Any help greatly appreciated
Jason- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[WhytheQ]

Thanks for sticking with this thread Michael.
I'm going to look into the API link you supplied.
Most of the routine involves spreadsheet manipulation so I'm going to
keep it in Excel (+ I'm not too sure how to go about "using the
instrinsic Application object")

Thanks,
Jason.

Maybe it's possible for you to have the code in Outlook VBA instead of
Excel? In OL 03, by using the instrinsic Application object, that would be
without security issues.

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Synchronize Color Categories & Ensure that Every Item Gets Categorized:
<http://www.vboffice.net/product.html?id=2006063&cmd=detail&lang=en&pub=6>

Am Wed, 31 Oct 2007 04:52:21 -0700 schrieb WhytheQ:

If I set this up using redemption will the code run on my colleague's
machines?
I'm pretty sure my company won't be interested in paying a
subscription for Redemption - is there a way of doing the above
without Redemption.
Maybe have to try the last option: Win32 API. Was hoping not to have
to head into API calls.

J

The security prompts when accessing the WordEditor property from 'outside'.
With Redemption you can use the SafeInspector.WordEditor, which also returns
a Word.Document, to bypass the security. Then you don't need to set the
cursor.

<http://www.vboffice.net/product.html?id=2006063&cmd=detail&lang=en&pub=6>

Am Tue, 30 Oct 2007 11:36:18 -0000 schrieb WhytheQ:
Thanks for all the help Michael. I've still not quite resolved this
query.
Using one of the links you provided I've created the following. Had to
adapt it slightly as I am putting my code in a module behind Excel.
'==========================================================
Sub CopyFromExcelIntoEMail()
Dim OutApp As Object
Dim Doc As Word.Document
Dim wdRn As Word.Range
Set OutApp = CreateObject("Outlook.Application")
Set oItem = OutApp.CreateItem(0)
oItem.Display
Set Doc = OutApp.ActiveInspector.WordEditor
Set wdRn = Doc.Range
ActiveSheet.Range("A1").Copy
wdRn.Paste
End Sub
'==========================================================
The only problem is that there is a security prompt half way through
this routine. Any ideas of a way around it? .... or is Redemption the
way to go?
I checked out the redemption site. Found some really useful stuff
(especially using SafeItem to automatically send emails using 2003
without annoying security prompts)
Unfortunately in the link you provided for Redemption
(www.dimastr.com) I couldn't
find any samples for setting the cursor - can you point me in the
right direction please?
Any help is much appreciated,
JasonQ
On 19 Oct, 12:23, "Michael Bauer [MVP - Outlook]" <[email protected]>
wrote:
Not pasting is the problem, but setting the focus into the body is.
You could still use the sample if you're using Word as mail editor. Then
skip the copy stuff and only set the Word Range and call Paste.
If you do not use Word as mail editor you either can use Redemption
(www.dimastr.com), which also allows to set the cursor, or use Win32 API
calls to set the cursor yourself. Here's a sampel for how to do the
latter
in OL2k or

OL03:http://www.vboffice.net/sample.html?lang=en&mnu=2&smp=20&cmd=showitem...
<http://www.vboffice.net/product.html?id=2006063&cmd=detail&lang=en&pub=6>

Am Fri, 19 Oct 2007 01:38:33 -0700 schrieb WhytheQ:
Cheers Michael,
If you alreay have something in the clipboard (via
"Sheets(1).Range("A118").CopyPicture xlScreen, xlBitmap") is there
no way of automating a simple pasting of this data into the body of a
mailitem.
Problem is that I'm executing this code from Excel and I need to
somehow activate the mailitem and then paste the data from the
clipboard, which was put into the clipboard whilst in Excel.
Jason
On 19 Oct, 06:04, "Michael Bauer [MVP - Outlook]" <[email protected]>
wrote:
With Word as e-mail editor you can use this

sample:http://www.vboffice.net/sample.html?mnu=2&lang=en&smp=41&cmd=showitem...
<http://www.vboffice.net/product.html?id=2006063&cmd=detail&lang=en&pub=6>

Am Thu, 18 Oct 2007 08:44:59 -0000 schrieb WhytheQ:
Ok - here we go:
I've got my formatted numbers in an Excel workbook:
Sheets(1).Range("A118").
If I run the following in an Excel code module:
Sheets(1).Range("A118").CopyPicture xlScreen, xlBitmap
and then:
activate an empty Outlook MailItem.
do control & v, the data appears in the email in Bitmap format in the
body
of the mail, which is what I want.
An alternative manual way of doing the above is to simply copy the
range then open the MailItem and do Edit>PasteSpecial>Bitmap and the
formatted range appears as a bitmap in the body of the mail.
How do I automate all of the above - preferably without using the
SendKey method?
I need it in a Bitmap format because my clients are using
BlackBerries.
I've looked through Ron's website but can't get a definite answer from
that resource.
Any help greatly appreciated
Jason- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -