• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

Report on Outlook Mail items

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.
  • 4
  • 3
1 Solution
David LeeCommented:
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.
bromy2004Author Commented:
Apologies for not being clear.
attached is a sample of what i would like.

David LeeCommented:
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

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

bromy2004Author Commented:
That is perfect.
Thank you.

From here i can make a few finer changes.
David LeeCommented:
You're welcome.  Glad I could help.

Happy holidays.
bromy2004Author Commented:
Is it possible to Add another column with the GUID?
So i can hyperlink into Outlook.
David LeeCommented:
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now