Body of message to Excel

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

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
--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx


"kabaka" <kabaka@discussions.microsoft.com> wrote in message
news:B37CD05C-50B3-4E70-80EE-74FDECC3468E@microsoft.com...
> 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

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.


"kabaka" <kabaka@discussions.microsoft.com> wrote in message
news:B37CD05C-50B3-4E70-80EE-74FDECC3468E@microsoft.com...
> 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

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.

--
Viele Grüße
Michael Bauer


"kabaka" <kabaka@discussions.microsoft.com> wrote in message
news:B37CD05C-50B3-4E70-80EE-74FDECC3468E@microsoft.com...
> 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

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!

"Matt Williamson" wrote:

> 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.
>
>
> "kabaka" <kabaka@discussions.microsoft.com> wrote in message
> news:B37CD05C-50B3-4E70-80EE-74FDECC3468E@microsoft.com...
> > 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
>
>
>

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!

"Sue Mosher [MVP-Outlook]" wrote:

> 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
> --
> Sue Mosher, Outlook MVP
> Author of
> Microsoft Outlook Programming - Jumpstart for
> Administrators, Power Users, and Developers
> http://www.outlookcode.com/jumpstart.aspx
>
>
> "kabaka" <kabaka@discussions.microsoft.com> wrote in message
> news:B37CD05C-50B3-4E70-80EE-74FDECC3468E@microsoft.com...
> > 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
>
>
>