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

Report on Outlook Mail items

Hi,
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.
0
bromy2004
Asked:
bromy2004
  • 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.
0
 
bromy2004Author Commented:
Apologies for not being clear.
attached is a sample of what i would like.

Outlook-Report.xls
0
 
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
                Next
                .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
        Next
    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

0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
bromy2004Author Commented:
That is perfect.
Thank you.

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

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

    Item.EntryID
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

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