Extract data from outlook emails using excel vba

[MarkBickers]

Hi Excel Experts,

Hi There,

I am looking for excel vba code to check the emails in a subfolder in Outlook mailbox and get the following details from the body of the email and save down in excel sheet .

Please see below the content of email body.

The format of the emails will be same for all in that particular subfolder . I have used "Sheet1" in excel workbook to store Mailbox name and the subfolder name so the code will look for those values in sheet1 and loops through all the emails in the subfolder and save down all the details in Sheet2 . Can anyone please amend the code and make it work as I am getting an error message "Method 'Body' of object '_MailItem" failed on the lines highlighted as red

Sub GetFromOutlook()

Dim OutlookApp As Outlook.Application
Dim OutlookNamespace As Namespace
Dim Folder As MAPIFolder
Dim OutlookMail As Variant
Dim i As Integer
Dim msgtext As String

Set OutlookApp = New Outlook.Application
Set OutlookNamespace = OutlookApp.GetNamespace("MAPI")
Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders("New Apps")

i = 1
lrow = 1

For Each OutlookMail In Folder.Items
MsgBox Folder.Items.Count
    msgtext = OutlookMail.Body
    MsgBox msgtext
     With ActiveSheet
            .Cells(lrow, 1) = OutlookMail.SenderName
            .Cells(lrow, 2) = OutlookMail.Subject
            .Cells(lrow, 3) = OutlookMail.ReceivedTime
            lrow = lrow + 1
        End With
  
Next OutlookMail

Set Folder = Nothing
Set OutlookNamespace = Nothing
Set OutlookApp = Nothing

End Sub

Thanks,
Mark

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Extract outlook email body using excel vba code

Hi Excel Experts, I am new to excel and I am building a form in excel which will check the emails in Outlook and extract email body using vba code . Can anyone please share the code with me ? All the emails in the mailbox are in the same format, There is a form in the body of the email...

www.excelforum.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[wombats08]

How to modify code to a subfolder? Thanks in advance!