Capturing information from an e-mail message.

[gjsala]

I'm trying to open up an e-mail message from a specific folder in outlook using VBA in excel and capture what is in the body of the message and paste it into a excel spreadsheet. There will be more than one e-mail message to go through and I would like to paste the information into one sheet. Where would I even start or is this possible? Thanks in advance!

 

[fumei]

Do some searches here.

This may help to get you started.

Dim myOlApp As Outlook.Application
Dim myNameSpace As NameSpace
Dim myFolder As Object
Dim j As Long

Set myOlApp = CreateObject("Outlook.Application")
Set myNameSpace = myOlApp.GetNamespace("MAPI")
Set myFolder = myNameSpace.GetDefaultFolder(olFolderInbox)
j = myFolder.Items.Count

Worksheets("Sheet1").Range("A1").Value = _
    myFolder.Items(j).Body

Set myFolder = Nothing
Set myNameSpace = Nothing
Set myOlApp = Nothing

This grabs the body from the last MailItem in the Inbox, and puts it into A1.

Adjust for whatever it is you are trying to do. The Body of the mail message is a string, so it can be parsed and massaged like any other string.

NOTE: this is using early binding, and therefore requires the appropriate Reference.

Gerry
My paintings and sculpture

 

[gjsala]

fumei,
Thanks for the quick response. What refereneces do I have to include to make this work? I notices I got a compiler error with this line of code

Dim myOlApp As Outlook.Application

.

Thanks.

 

[PHV]

when in VBE, menu Tools -> References ...
Tick the Microsoft Outlook x.y Object Library

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fumei]

Yes, you would get an error. As stated, this is using early binding, and requires a Reference.

In the VBE (in Excel if I understand correctly), go Tools > References. Scroll down to Microsoft Outlook version_number Object Library, and select it. Press OK. You now have a reference to that library, and can use its properties and methods. You can use it in the current project. Making a Reference in one project does not make the reference in all projects.

It is important to remember that early binding ties your code to THAT library, which means THAT version.

If you have any issues with different versions, use late binding.



Gerry
My paintings and sculpture

 

[gjsala]

That worked and thanks for the information on the references! I would like this code to look at a specific folder for example "test" which is located under "Personal Folders". Also, I would like the body of each e-mail located in the test folder to be captured and placed in an excel sheet. For example the first e-mail's body is put on row one of the worksheet, the second e-mail is put on row two and so forth until there are no more e-mails in the folder. From there I can parse out the information in excel it's just getting it there I don't know how to do. Thanks for your help!