vba in outlook

[BradRose]

hi, I recieve attachments via emails every day. I'd like to build some VBA that will produce a report in Excel saying, what time the email came into the inbox and who it was sent by. Possibly also another column to highlight the subject line (something to be able to differentiate each attachment).

I currently am using the following which produces a dialog box that tells me the time the email was recieved/replied. can i manipulate this to produce my excel file?

Sub CheckMailTime()
' Define variables for MS Outlook objects
Dim oAppOutl As Outlook.Application
Dim oNameSpace As Outlook.NameSpace
Dim oInbox As Outlook.MAPIFolder
Dim oMailItem As Outlook.MailItem

' Set Outlook object (NB - Outlook must be open or code'll fail)
Set oAppOutl = GetObject("", "Outlook.Application")
' Set MAPI name space
Set oNameSpace = oAppOutl.GetNamespace("MAPI")
' Set Inbox object
Set oInbox = oNameSpace.GetDefaultFolder(olFolderInbox)

'Loop thru' inbox & check date/time recvd for each mailitem
For Each oMailItem In oInbox.Items
MsgBox oMailItem.Subject & " received on: " & oMailItem.ReceivedTime
MsgBox oMailItem.Subject & " created on: " & oMailItem.CreationTime
Next oMailItem

'Clean up objects
Set oAppOutl = Nothing
Set oNameSpace = Nothing
Set oInbox = Nothing

End Sub

Many thank Brad

 

[Gruuuu]

You can add a textstream object and write your values in a plain text format and save it as a .csv

Otherwise, if this is called from Excel, you can just instantiate a new workbook, write the values to the cells, save the workbook off. Or open an existing one, find the last used cell, and write your results after that.

That's probably the easiest way.

But question: what happens if the mail items are moved from your inbox before you call this procedure?

 

[Gammachaser]

Assuming that your code is in an Excel Module and you have a Workbook open with a blank sheet active, try replacing your 'loop through' section with this code:

'=======================================================
With ActiveSheet
.Cells(1, 1) = "Subject"
.Cells(1, 2) = "Received on"
.Cells(1, 3) = "Created on"
.Cells(2, 1).Activate
End With
For Each oMailItem In oInbox.Items
ActiveCell.Offset(0, 0) = oMailItem.Subject
ActiveCell.Offset(0, 1) = oMailItem.ReceivedTime
ActiveCell.Offset(0, 2) = oMailItem.CreationTime
ActiveCell.Offset(1, 0).Activate
Next oMailItem
'=======================================================

'If you don't have a workbook open use this under your existing header:
'=========================================
Dim xlMyBook as Workbook
Dim xlMySheet as Worksheet

Set xlMyBook = Application.Workbooks.Add
set xlMySheet = xlMyBook.Sheets(1)
xlMySheet.Activate
'=========================================
'Don't forget to 'clean up' as you put it, by adding:
'=========================================
xlMyBook.Close '(if you want to close it)
Set xlMyBook = Nothing
Set xlMySheet = Nothing
'=========================================

There is a world of formatting you can do as well, if you want it to look better. Anything you can do on the sheet you can do with code.