Report on Outlook Mail items

Posted on 2009-12-16
Last Modified: 2012-05-08
At my company out Nightly reports get sent out on 1 specific Machine.
There is normally 60+ Emails Sent.

I am wanting a macro that could send to excel Some Data.

Id need it to be able to be filtered by Date and possibly some other Fields.

I am fairly confident in Excel, so if someone could set up the basic macro and output settings, i'm sure i could add other filters/Output fields ect.
Question by:bromy2004
    LVL 76

    Expert Comment

    by:David Lee
    Hi, bromy2004.

    "send to excel Some Data"
    You're going to have to be more specific about what data and where it's at before we can determine if we can help.
    LVL 10

    Author Comment

    Apologies for not being clear.
    attached is a sample of what i would like.

    LVL 76

    Accepted Solution

    Sorry to be so slow.  Here's the code for doing this.  Follow these instructions to use it.

    1.  Start Outlook
    2.  Click Tools > Macro > Visual Basic Editor
    3.  If not already expanded, expand Microsoft Office Outlook Objects
    4.  If not already expanded, expand Modules
    5.  Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting Insert > Module.
    6.  Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook's VB Editor window
    7.  Edit the code as needed.  I included comments wherever something needs to or can change
    8.  Click the diskette icon on the toolbar to save the changes
    9.  Close the VB Editor

    The code cannot fill in the BCC fields.  That information is not available in a message.
    Sub Bromy()
        Dim excApp As Object, excBook As Object, excSheet As Object, lngRow As Long, datStart As Date
        Dim olkMsg As Object, olkItems As Outlook.Items, olkRecipient As Outlook.Recipient
        'Change the text on the next line as desired'
        datStart = InputBox("Enter a starting date", "Bromy", Date)
        'Change the condition on the next line as desired'
        Set olkItems = Session.GetDefaultFolder(olFolderInbox).Items.Restrict("[ReceivedTime] >= '" & Format(datStart & " 12:00am", "ddddd h:nn AMPM") & "'")
        If olkItems.count > 0 Then
            Set excApp = CreateObject("Excel.Application")
            Set excBook = excApp.Workbooks.Add()
            Set excSheet = excBook.Worksheets(1)
            With excSheet
                .Cells(1, 1) = "To"
                .Cells(1, 2) = "To Email"
                .Cells(1, 3) = "From"
                .Cells(1, 4) = "From Email"
                .Cells(1, 5) = "CC"
                .Cells(1, 6) = "CC Email"
                .Cells(1, 7) = "BCC"
                .Cells(1, 8) = "BCC Email"
                .Cells(1, 9) = "Subject"
                .Cells(1, 10) = "Attachments"
                .Cells(1, 11) = "Date Sent"
                .Cells(1, 12) = "Date Received"
                .Cells(1, 13) = "Flag"
                .Cells(1, 14) = "Categories"
            End With
            lngRow = 2
            For Each olkMsg In olkItems
                With excSheet
                    For Each olkRecipient In olkMsg.Recipients
                        Select Case olkRecipient.Type
                            Case olTo
                                .Cells(lngRow, 1) = .Cells(lngRow, 1) & olkRecipient.Name & vbLf
                                .Cells(lngRow, 2) = .Cells(lngRow, 2) & olkRecipient.Address & vbLf
                            Case olCC
                                .Cells(lngRow, 5) = .Cells(lngRow, 5) & olkRecipient.Name & vbLf
                                .Cells(lngRow, 6) = .Cells(lngRow, 6) & olkRecipient.Address & vbLf
                        End Select
                    .Cells(lngRow, 3) = olkMsg.SenderName
                    .Cells(lngRow, 4) = olkMsg.SenderEmailAddress
                    .Cells(lngRow, 9) = olkMsg.subject
                    .Cells(lngRow, 10) = IIf(olkMsg.Attachments.count > 0, True, False)
                    .Cells(lngRow, 11) = olkMsg.SentOn
                    .Cells(lngRow, 12) = olkMsg.ReceivedTime
                    .Cells(lngRow, 13) = olkMsg.FlagRequest
                    .Cells(lngRow, 14) = olkMsg.Categories
                End With
                lngRow = lngRow + 1
        End If
        Set olkRecipient = Nothing
        Set olkItems = Nothing
        Set olkMsg = Nothing
        excApp.Visible = True
        Set excSheet = Nothing
        Set excBook = Nothing
        Set excApp = Nothing
    End Sub

    Open in new window

    LVL 10

    Author Closing Comment

    That is perfect.
    Thank you.

    From here i can make a few finer changes.
    LVL 76

    Expert Comment

    by:David Lee
    You're welcome.  Glad I could help.

    Happy holidays.
    LVL 10

    Author Comment

    Is it possible to Add another column with the GUID?
    So i can hyperlink into Outlook.
    LVL 76

    Expert Comment

    by:David Lee
    Outlook items don't have a GUID.  The closest you can get to a GUID is the EntryID property which is only unique within a particular information store.  If you want that property then this will reference it


    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    Sometimes Outlook might have problems sending a message. There may be various causes- corrupted PST, AV scanner etc. The message, instead of going to the Sent Items folder, sits in the Outbox indefinitely. To remove it you can use a free tool cal…
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now