automatic copying from e-mail to spreadsheet?

[ctwobits]

Hi,

I am wondering if I can make a macro in Outlook to automatically copy the body of an e-mail into a spreadsheet. The emails are the same each time and notify me that files have been uploaded to a site. I just have to track them for completion. I already have the e-mails filtered into a folder. I can potentially get hundreds a day. Obviously I would like the macro to run once and go through and copy all - even if I must wait for it to run as it goes through all of them.

TIA,

Christi

 

[Davers]

Hi Christi. I've done just that thing for quite a few reports. Only I usually pull into Excel, not push from Outlook. Meaning the code resides in Excel. Here is what I use:

Sub imPortFromOutlook()

Dim myOlApp As Object    'Outlook.Application
Dim myNameSpace As Object   'Outlook.NameSpace
Dim myInbox As Object     'Outlook.MAPIFolder
Dim myMovebox As Object 'Outlook.MAPIFolder
Dim MItem As Object     'Outlook.MailItem

Set myOlApp = CreateObject("Outlook.Application")
Set myNameSpace = myOlApp.GetNameSpace("MAPI")
Set myInbox = myNameSpace.GetDefaultFolder(olFolderInbox)
Set myMovebox = myNameSpace.GetDefaultFolder(olFolderDeletedItems)

For Each MItem In myInbox.Items
  
  '// Note Only looking for emails where left 5 characters in subject = "Great"
    If Left(MItem.Subject, 5) = "Great" Then
    
        Sheets("Sheet1").Activate
        
        NextRow = _
            Application.WorksheetFunction.CountA(Range("A:A")) + 1
    
        Cells(NextRow, 1) = MItem.Body
       
        MItem.UnRead = False
        MItem.Move myMovebox

    End If
    
Next MItem

With Worksheets("Sheet1").Cells
    .Select
    .EntireRow.AutoFit
End With
    
Range("A1").Select

Set MItem = Nothing
Set myInbox = Nothing
Set myNameSpace = Nothing
Set myMovebox = Nothing

End Sub

This is looking through your Inbox for subjects where the left 5 characters = "Great". You can change this to whatever you'd like, or delete it all together if you want to import everything. It will put the body of the e-mail in column A. Hope this helps or at least points you in the right direction.

Have a good night,

Dave