The object browser works the same in Outlook VBA as in Excel. If you used
it, you'd learn that the body of an email message is MailItem.Body
You can use the Items.ItemAdd event to monitor the Inbox for new items and
process them. See code samples at
http://www.slipstick.com/dev/code/zaphtml.htm and
http:/
www.slipstick.com/dev/code/quarexe.htm
The key issue, however, for your project is that an Excel cell doesn't like
carriage return or tab characters and cannot contain more than 32k
characters. Therefore, you would need to use this function (adapted from
Listing 22.7 in my book) to clean up the Body property text:
Function TextToExcel(strText as String)
strText = Replace(strText, vbCr, " ")
strText = Replace(strText, vbTab, " ")
strText = Left(strText, 32767)
TextToExcel = strText
End Function
Hopefully, that will give you the building blocks you need to make further
progress on this project.
--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
Maggie said:
I have tried this method and don't seem to be able to fathom it out(Excel
Macros are more my thing), what I need is for every time an email arrives in
my inbox, I need for the Macro to copy the body of the email and then paste
it to Excel
----- Sue Mosher [MVP-Outlook] wrote: -----
What do you mean by "the body of text in an e-mail field"? What field from
where? When in doubt, check the object browser: Press ALt+F11 to open the
VBA environment in Outlook, then press F2.
--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
Maggie said:
I have used Sue's advice and the VB help to create the attached
code, the
only thing i can't find/workout is how to replace the value="yes 10 no 5"
with the body of text in an e-mail field What I need the code to do is copy
this and then place in it the excel file.......
' Declare an object variable to hold the object
' reference. Dim as Object causes late binding.
Dim ExcelSheet As Object
Set ExcelSheet = CreateObject("Excel.Sheet")
' Make Excel visible through the Application object.
ExcelSheet.Application.Visible = True
' Place some text in the first cell of the sheet.
ExcelSheet.Application.Cells(1, 1).Value = "yes 10 no 5"
' Save the sheet to C:\test.xls directory.
ExcelSheet.SaveAs "C:\TEST.XLS"
' Close Excel with the Quit method on the Application object.
ExcelSheet.Application.Quit
' Release the object variable.
Set ExcelSheet = Nothing
End Sub
