Body of message to Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kabaka - 28 Feb 2005 20:41 GMT

Hi,

I'm trying to copy the body of a selected message to excel via a macro
(custom button). I received help a week or so ago and thought I had this
done, or so it seemed.

I have the following code:

Sub email_to_excel()
Dim xlApp As Excel.Application
Dim oWBook As Workbooks
Set xlApp = New Excel.Application
Set oWBook = xlApp.Workbooks
oWBook.Open ("SomeLongPath\exportfromOutlook.xls")
ActiveSheet.Cells(1, 1).Value =
Application.ActiveExplorer.Selection(1).Body
End Sub

which seems to run ok, except that
1) when I open the "exportfromOutlook" file I get the message that the file
is locked for editing (Excel was not previously opened) even after closing
Outlook and
2) when I choose read only, the body of the message is not there.

What is going on here?

Thanks

Reply to this Message

Sue Mosher [MVP-Outlook] - 28 Feb 2005 21:10 GMT

Does it work better this way:

Set oWBook = xlApp.Workbooks.Open _
("SomeLongPath\exportfromOutlook.xls")
oWBook.Sheets(1).Cells(1, 1).Value = _
Application.ActiveExplorer.Selection(1).Body
oWBook.Close True
Set oWBook = Nothing

Note that Excel isn't friendly to carriage returns, tabs, or long text
blocks. You can use this function to do some cleanup:

Function TextToExcel(MyString)
MyString = Replace(MyString, vbCr, " " )
MyString = Replace(MyString, vbTab, " ")
MyString = Left(MyString, 32767)
End Function

Signature

Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx

> Hi,
>
[quoted text clipped - 24 lines]
>
> Thanks

Reply to this Message

kabaka - 28 Feb 2005 22:15 GMT

I must say that I am impressed at how quickly so many knowledgeable people
came to my aid. Your function really cleans things up - Thanks!

> Does it work better this way:
>
[quoted text clipped - 41 lines]
> >
> > Thanks

Reply to this Message

Matt Williamson - 28 Feb 2005 21:39 GMT

Take a look at your Task Manager and see how many instances of Excel you
have running. I'd venture to guess there is one for every time you ran your
macro without rebooting your computer. You need to save/close the workbook
and quit the excel instance and set all of your objects to nothing. The code
you have now is not doing any cleanup at all.

> Hi,
>
[quoted text clipped - 24 lines]
>
> Thanks

Reply to this Message

kabaka - 28 Feb 2005 22:07 GMT

Thanks guys, checking the task manager was one of the first things I had
done. I'm not too knowledgable in that area but what struck me as odd was
that Excel was not one of the programs running under the "Applications" tab,
but was listed several times in the "Processes" tab. After "ending" those
processes and making the code suggestions Michael provided, everything worked.

Thanks again!

> Take a look at your Task Manager and see how many instances of Excel you
> have running. I'd venture to guess there is one for every time you ran your
[quoted text clipped - 30 lines]
> >
> > Thanks

Reply to this Message

Michael Bauer - 28 Feb 2005 21:39 GMT

Hi Kabaka,

you also need to save and close the Workbook, for that in turn you need
a reference on it:

Dim oWb As Excel.Workbook
...
Set oWb=xlApp.Workbooks.Open ("SomeLongPath\exportfromOutlook.xls")
...
' Close and save the Workbook
oWb.Close true

' Quit Excel
xlApp.Quit

Please note: This is an Outlook newsgroup. I´m sure you will have more
questions about automating Excel. If so please visit an Excel VBA
newsgroup for more help.

Signature

Viele Grüße
Michael Bauer

> Hi,
>
[quoted text clipped - 23 lines]
>
> Thanks

Reply to this Message