OUTLOOK VBA - exporting body from incoming message to excel

[Arthur.J]

Hey All, Hopefuly somebody will be able to help.

I would like to have a macro which is able to transfer some information from incoming email to existing excel workbook (add new line). My code is working fine but only for first email received after restarting outlook. For every next email macro is running, opening workbook and not adding any information.

Anyone is able to help?
Thanks in advance.

Option Explicit

Private WithEvents olInboxItems As Items

Private Sub Application_Startup()
  Dim objNS As NameSpace
  Set objNS = Application.Session
  ' instantiate objects declared WithEvents
  Set olInboxItems = objNS.GetDefaultFolder(olFolderInbox).Items
  Set objNS = Nothing
End Sub


Private Sub olInboxItems_ItemAdd(ByVal Item As Object)

    Dim myXLApp As Excel.Application
    Dim myXLWB As Excel.Workbook
    Dim StrBody As String
    Dim TotalRows, i As Long

  
  
  On Error Resume Next
  Item.BodyFormat = olFormatPlain
  
  MsgBox (Item.Subject)
  
  Set myXLApp = New Excel.Application
  myXLApp.Visible = True
  Set myXLWB = myXLApp.Workbooks.Open("Z:\Leads_replies.xls")
  
  
  'adding to excel
    'Workbooks(myXLWB).Activate
    
    TotalRows = Sheets(1).Range("A55000").End(xlUp).Row
    i = TotalRows + 1
    MsgBox (myXLWB)
    
   
    StrBody = Item.Body
    Range("A" & i).Value = Format(Item.SentOn, "mm/dd/yyyy")
    Range("B" & i).Value = Item.SenderName
    Range("C" & i).Value = Item.To
    
    Range("D" & i).Value = Item.Body
  
  myXLWB.Close SaveChanges:=True
  
  myXLApp.Quit
  
  Item.Save
  Set Item = Nothing

  
End Sub

 

[Darren Bartrup]

Not sure how to do that sous2817.

That was my first bit of programming in Outlook (though I have referenced Outlook from Excel plenty of times). I just mashed up some code I'd written in Excel to pull the attachments from emails.

 

[Arthur.J]

Thank you very much. Actually idea is that I keep file which containing information of all emails coming - so when new message coming it should check if this file is open or not, open if needed, add new line to this file and save changes to the file. I'm trying to change your code to work like this.

Thanks one more time!

 

[sous2817]

No worries, just thought I'd ask

Maybe if I ever get a day of downtime I'll take a stab at it...

 

[Darren Bartrup]

Actually managed to find something useful on MSDN:

http://msdn.microsoft.com/en-us/library/aa171307(v=office.11).aspx

It mentions the ItemAdd event which fires whenever a new item is added to a folder (such as the Inbox) and also the NewMailEx event which fires for all items received in the Inbox (task requests, calendar requests, emails, etc) - this event has the added functionality of passing the ID of each item in a collection which can be referenced.

Edit: Dear MrExcel - can we have new forums for Outlook, Word & Powerpoint to please. :D

 

[Arthur.J]

I will have a look - your code is working fine with handling excel file - but somehow it pickups wrong email information - for every email arriving is adding information from the same old email - first email in Inbox.

 

[Darren Bartrup]

I'd say it's probably something to do with this line:

Set myItem = objFolder.Items(1)

I'm guessing this works the same as referencing sheets by number in Excel - it varies depending on how the emails/sheets are sorted.

 

[Arthur.J]

You are right. I just combined your code with my previous one and looks like working. I will post working code when finish - need to do some more changes.

Thanks one more time - it was really helpful