Outlook VBA help

[boxboy30]

Could someone help me create a code in Outlook that would parse certain information from my email and then input it into an exsisting excel spreadsheet?

 

[Trevor G]

This code will run from Excel then list all the emails in your inbox.

ListAllItemsInInbox()
'You have to Add the reference to Outlook
'Select Tools > References > Search for Microsoft Outlook XX.Object Library
Dim OLF As Outlook.MAPIFolder, CurrUser As String
Dim EmailItemCount As Integer, i As Integer, EmailCount As Integer
Application.ScreenUpdating = False
Sheets.Add ' create a new workbook
' add headings
Cells(1, 1).Formula = "Subject"
Cells(1, 2).Formula = "Recieved"
Cells(1, 3).Formula = "Attachments"
Cells(1, 4).Formula = "Read"
With Range("A1:D1").Font
.Bold = True
.Size = 14
End With
Application.Calculation = xlCalculationManual
Set OLF = GetObject("", _
"Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
EmailItemCount = OLF.Items.Count
i = 0: EmailCount = 0
' read e-mail information
While i < EmailItemCount
i = i + 1
If i Mod 50 = 0 Then Application.StatusBar = "Reading e-mail messages " & _
Format(i / EmailItemCount, "0%") & "..."
With OLF.Items(i)
EmailCount = EmailCount + 1
Cells(EmailCount + 1, 1).Formula = .Subject
Cells(EmailCount + 1, 2).Formula = Format(.ReceivedTime, "dd.mm.yyyy hh:mm")
Cells(EmailCount + 1, 3).Formula = .Attachments.Count
Cells(EmailCount + 1, 4).Formula = Not .UnRead
End With
Wend
Application.Calculation = xlCalculationAutomatic
Set OLF = Nothing
Columns("A:D").AutoFit
Range("A2").Select
ActiveWindow.FreezePanes = True
ActiveWorkbook.Saved = True
Application.StatusBar = False
End Sub

 

[boxboy30]

And I'm assuming I can make it list certain information that's in my inbox?

 

[Trevor G]

This will list all emails, if you need to do something like search for words then you could look to apply a filter after this code. What do you need to search for?

So you would need something like this

Range("A1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$D$50000").AutoFilter Field:=1, Criteria1:= _
"=*What text*", Operator:=xlAnd

 

[boxboy30]

How do i tweek it if I want to get information from my subfolder instead of the inbox?

 

[Trevor G]

You would need something along the lines of this.

I have just found a website that might help you

http://www.ozgrid.com/forum/showthread.php?t=93067&page=1

' subfolders and items within Inbox

For Each olFolder In olInbox.Folders

i = Range("F1") + 2
For Each olItem In olFolder.Items
Cells(i, 1) = olItem.SenderName ' Sender
Cells(i, 2) = olItem.Subject ' Subject
Cells(i, 3) = olItem.ReceivedTime ' Received
Cells(i, 4) = olItem.ReceivedByName ' Recepient
Cells(i, 5) = olItem.UnRead ' Unread?


i = i + 1
Next


Next

 

[boxboy30]

thanks a bunch...I'll check it out!

 

[Derek Brown]

If you then want to extract information from the Body of the email, you would need to save the email as a (temporary) text file and then open the text file to read the content. However, you would need to find someway of identifying what you are looking for.

 

[boxboy30]

This code will run from Excel then list all the emails in your inbox.

so why do you on this line:

Application.Calculation = xlCalculationManual why turn this to manual?
Application.Calculation = xlCalculationAutomatic again, what is the point of changing this within the code?

 

[Derek Brown]

If calculation mode is set to 'auto', all open workbooks may recalculate when you update a worksheet cell. It is safer to set to manual before doing this (where appropriate) in VBA and then set it back to whatever it was before finishing the process.
More information at:
http://support.microsoft.com/kb/214395