macro to update excel when email received into outlook folder

  • Thread starter Thread starter amorrison2006
  • Start date Start date
A

amorrison2006

Hi

I am hoping someone may be able to help with this,

I was looking around on the groups for a macro to monitor a folder and
when an email is received to update an excel spreadsheet located in
lets say "C:\temp\excellog.xls"

I am sure this is possible but I would have no idea where to start,

Would anyone have a macro that could help with this?

I have multiple folders and they would update to a different place on
the spreadsheet for each folder.

Thank you so much in advance,

Andrea
 
Implement something similiar to the code below. You'd need to set one
variable to an Items collection for each folder you want to monitor - the
below example shows how to set a reference to the Items collection for the
Inbox. When a message is delivered/moved/copied to that folder the ItemAdd
event will fire and pass an object variable for that specific message so that
you can work with its properties.

Private WithEvents NewMailItems As Outlook.Items

Private Sub Application_Quit()
Set NewMailItems = Nothing
End Sub

Private Sub Application_Startup()
Set NewMailItems =
Application.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Items
End Sub

Private Sub NewMailItems_ItemAdd(ByVal Item As Object)
'THIS WILL FIRE FOR EVERY NEW E-MAIL; YOU CAN USE THE
'Item OBJECT TO WORK WITH THE PROPERTIES OF THE E-MAIL MESSAGE

Dim objMail As Outlook.MailItem

If Item.Class <> olmail Then Exit Sub

Set objMail = Item
objMail.UnRead = False
objMail.FlagStatus = olNoFlag
End Sub
 
acces Excel from Outlook by using the Createobject
add someting like this to the above example
Code: 
myfile = "C:\temp\excellog.xls"
'open excel object
Set objXLS = CreateObject("excel.application")
objXLS.Application.Visible = True
objXLS.workbooks.Open (myfile)				  'open your excel file
objXLS.worksheets(1).Range("A1").Select
' Find the last real row
objXLS.Range("A65536").End(xlUp).Select
objXLS.ActiveCell.Offset(0, 1).Value = objItem.ReceivedTime 'remember to always refer to your object
objXLS.ActiveCell.Offset(0, 2).Value = objItem.Subject
objXLS.ActiveWorkbook.Close SaveChanges:=True   'save excel file
objXLS.Quit									 'quit excel
Set objXLS = Nothing
 
Back
Top