# Outlook VBA help



## boxboy30 (Sep 23, 2011)

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 (Sep 23, 2011)

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
> ...


----------



## boxboy30 (Sep 23, 2011)

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


----------



## Trevor G (Sep 23, 2011)

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 (Sep 23, 2011)

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


----------



## Trevor G (Sep 23, 2011)

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
> 
> ...


----------



## boxboy30 (Sep 23, 2011)

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


----------



## Derek Brown (Sep 23, 2011)

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 (Sep 23, 2011)

Trevor G said:


> 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 (Sep 23, 2011)

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


----------



## boxboy30 (Sep 23, 2011)

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?


----------



## boxboy30 (Sep 23, 2011)

Aha! You are too smart Mister! I removed that part of the code...and without it, you are absolutely correct! It runs on whatever spread I have open. 

Thanks a bunch!


----------



## NewOrderFac33 (Sep 26, 2011)

Good afternoon,

This topic is pretty much what I thought I was loking for - I have been developing in Excel VBA for many years but have never had the need to touch Outlook until now. Like the other chap, I need to be able to trap key attributes from an open email message such as date and time sent, sender, recipients and cc recipients and message body. I then need to be able to concatenate these into one string and past the result into a text box in an Excel worksheet.
Whilst I was hoping I could use this example with minimum modification, Excel reports "User defined Type Not defined" when I run the code, with "Dim OLF As Outlook.MAPIFolder" highlighted.

I know I should know what this means, but any asistance with this or in adapting the code to meet the requirement described above would be extremely welcome!

P.S. I can't see "References" under "Tools" in either the Excel or the Outlook menu - am I doing something wrong?

Thanks in advance

Pete


----------



## Trevor G (Sep 26, 2011)

Welcome to the Forum Pete

In the Excel VBA Screen you have to select to use the Outlook object, so go to the Tools Menu > References > Scroll Down until you find Microsoft Outlook XX .Object Library and tick the box (XX being the verison number you are using), then based on the code in the thread it would work....


----------



## NewOrderFac33 (Sep 26, 2011)

Hi, Trevor - thanks for the swift reply! 

I found the correct Tools References (Duh!), followed the instructions and restarted both Excel and Outlook, but I still get the same error message.
Any thoughts?

Pete


----------



## Trevor G (Sep 26, 2011)

Can you show the code you are using and highlight the issue please.


----------



## NewOrderFac33 (Sep 26, 2011)

It's the code that you suggested to the previous poster (see below)

When I attempt to run it, Excel reports:

Compile error: user defined type not defined" with the line beginning "Dim OLF..." highlighted.

Obviously, this code is designed to (I think) take details from a group of emails and paste them to a worksheet - what I need to know is how to make it work for just one open email.

Thanks in advance.

Pete

Sub 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


----------



## Trevor G (Sep 26, 2011)

Check the references again to see if the Microsoft Outlook appears near the top of list in the References.

You can always add a fitler to the code to give you a specific type of email once in an excel workbook.


----------



## boxboy30 (Sep 26, 2011)

hey Pete!  i was able to figure it out!!!!

Try this code:


Sub ExportToExcel()

    Dim msg         As Outlook.MailItem
    Dim nms         As Outlook.NameSpace
    Dim fld         As Outlook.MAPIFolder
    Dim itm         As Object
    Set nms = Application.GetNamespace("MAPI")
    Set fld = nms.PickFolder

    If fld Is Nothing Then
        MsgBox "There are no mail messages to export", vbOKOnly, "Error"
        Exit Sub
    ElseIf fld.DefaultItemType <> olMailItem Then
        MsgBox "There are no mail messages to export", vbOKOnly, "Error"
        Exit Sub
    ElseIf fld.Items.Count = 0 Then
        MsgBox "There are no mail messages to export", vbOKOnly, "Error"
        Exit Sub
    End If

    Dim appExcel    As Excel.Application
    Dim wkb         As Excel.Workbook
    Dim wks         As Excel.Worksheet
    Dim sRow        As Integer

    Set appExcel = CreateObject("Excel.Application")
    appExcel.Workbooks.Add
    Set wkb = appExcel.ActiveWorkbook
    Set wks = wkb.Sheets(1)
    wks.Activate
    appExcel.Application.Visible = True
    appExcel.Application.DisplayAlerts = False
    Windows(appExcel.Application.ActiveWorkbook.Name).Activate


    'On Error Resume Next

    For Each itm In fld.Items
        sRow = sRow + 1
        Range("A" & sRow).Value = itm.SentOn
        Range("B" & sRow).Value = itm.SenderEmailAddress
        Range("C" & sRow).Value = itm.Subject
        Range("D" & sRow).Value = itm.Body
        Range("D" & sRow).Replace What:="" & Chr(10) & "", Replacement:=" ", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Range("D" & sRow).Replace What:="" & Chr(13) & "", Replacement:=" ", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Range("F" & sRow).Value = itm.SentOn
        Range("G" & sRow).Value = itm.ReceivedTime
    Next itm

    appExcel.Application.DisplayAlerts = True
    Set appExcel = Nothing
    Set wkb = Nothing
    Set wks = Nothing
    Set msg = Nothing
    Set nms = Nothing
    Set fld = Nothing
    Set itm = Nothing

End Sub

I think you might find it to be very empressive!   I'm proud of myself on this one!


----------



## NewOrderFac33 (Sep 26, 2011)

Hi, BoxBoy - I pasted the code into Outlook, but it reported: "User defined Type not defined" for line: Dim appExcel As Excel.Application.

I have checked all the References and I have checks against:

Visual Basic for Applications
Microsoft Outlook 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object Library
Microsoft Excel 11.0 Object Library

What am I doing wrong?

Pete


----------



## NewOrderFac33 (Sep 26, 2011)

Trevor,

You can see from my previous reply that I think that everything that I need is installed - not sure where I'm going wrong on this one...

Pete


----------



## boxboy30 (Sep 23, 2011)

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 (Sep 26, 2011)

Hey well done BoxBoy that works nicely here.

Pete what security levels have you got on in your Outlook?


----------



## NewOrderFac33 (Sep 26, 2011)

OK, chaps - to confirm: I am running the code from Outlook in which the security setting is "Low".
NOW, I'm getting "Compile error: user-defined type not defined" against line: Dim appExcel As Excel.Application.
You familiar with Morrissey or The Smiths in your part of the world?


----------



## Trevor G (Sep 26, 2011)

Yep familiar with both.

Try this out.



> Sub ExportToExcel()
> Dim msg As Outlook.MailItem
> Dim nms As Outlook.NameSpace
> Dim fld As Outlook.MAPIFolder
> ...


----------



## NewOrderFac33 (Sep 26, 2011)

Trevor,

The reason I ask is that if either of them didn't write a song called "Take me out and shoot me, Mother", I have the lyrics right here.

I'm going to restart the PC now, because both versions of the code 9the one that runs from Excel and this one) seem to be running up toa  point and doint what they're supposed to, then they hang up at some arbitrary point having output a certain number of rows to the spreadsheet.
I've had this sort of thing before with the latest Excel project I'm working on and despite "switch off and switch on again" being an urban myth, it actually does seem to work in these circumstances.
I'll see ypu in a minute...


----------



## NewOrderFac33 (Sep 26, 2011)

3-2-1 you're back in the room.

OK - after a reboot, the Outlook code appears to work, exporting a number of attributes for each email message to the Excel spreadsheet.
No error messages are displayed, but having exported 69 rows of data, I just see the normal Excel cursor, as though the procedure has completed.
When i go into the VBA editor, the following message is displayed:
"Runtime error 438:Object doesbn't support this property or method", with 
appExcel.Range("A" & sRow).Value = itm.SentOn
highlighted.
I would obviously like to get to the bottom of this as it will be of use in the future, but eventually, what I also need is the means to highlight or open just one message and extract the details for that and that alone.
Your continued help is greatly appreciated!

Pete


----------



## NewOrderFac33 (Sep 26, 2011)

...and going back to the Excel based code which currently looks like this:

Sub 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 = "Received"
    Cells(1, 3).Formula = "Attachments"
    Cells(1, 4).Formula = "Read"
    Cells(1, 5).Formula = "Sender"
    With Range("A1:E1").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, "ddd dd mmm yy hh:mm")
            Cells(EmailCount + 1, 3).Formula = .Attachments.Count
            Cells(EmailCount + 1, 4).Formula = Not .UnRead
            Cells(EmailCount + 1, 5).Formula = .SenderName
        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

when I attempt to run, I get "object doesn't support this property or method" with 
 Cells(EmailCount + 1, 2).Formula = Format(.ReceivedTime, "ddd dd mmm yy hh:mm")

highlighted. Suspect this is just syntax related, though.

Would you know of a list of all the email attribute names somewhere?

Pete


----------



## NewOrderFac33 (Sep 26, 2011)

...and incidentally, this one drops out at row 69 too, again with "object doesn't support this property or method", highlightiing
Cells(EmailCount + 1, 2).Formula = .ReceivedTime

Just realised - the 69th item is a message recall!

How can I make it ignore these?


----------



## boxboy30 (Sep 26, 2011)

try referencing Excel as well to Outlook?  Or open a blank page and then run it...which I don't think it is because I told it to run a new workbook each time


----------



## NewOrderFac33 (Sep 27, 2011)

I added an "On Error Resume Next" statement to both Outlook and Excel code and they both work fine, bypassing the message recall items nicely.
If I can now just work on something to work on the currently active message (without having to copy the message I want into a separate folder and processing that via the Outlook code that allows me to select a folder), that would be great!
Thanks for all your help, both.


----------

