none
exporting info to Excel RRS feed

  • Question

  • Private Sub Application_NewMail()
    
        Dim objNS As NameSpace
        Dim objFolder As MAPIFolder
        Dim myItem As MailItem
    
        Dim myXLApp As Excel.Application
        Dim myXLWB As Excel.Workbook
        Dim StrBody As String
        Dim TotalRows As Long, i As Long
        
        Set objNS = GetNamespace("MAPI")
        Set objFolder = objNS.GetDefaultFolder(olFolderInbox)
        Set myItem = objFolder.Items(1)
        
        Set myXLApp = New Excel.Application
        myXLApp.Visible = True
        Set myXLWB = myXLApp.Workbooks.Add
        
        TotalRows = Sheets(1).Range("A65536").End(xlUp).Row
        i = TotalRows + 1
        
        With myXLWB.Worksheets(1)
            .Cells(i, 1) = Format(myItem.SentOn, "mm/dd/yyyy")
            .Cells(i, 2) = myItem.SenderName
            .Cells(i, 3) = myItem.To
            .Cells(i, 4) = myItem.Body
        End With
        
    End Sub
    
    
    Set myItem = ActiveExplorer.Selection.Item(1)
        
         Set myXLApp = New Excel.Application
        myXLApp.Visible = True
        Set myXLWB = myXLApp.Workbooks.Open("U:\1.xls")
    

    I have this macro in outlook to capture information from incoming emails. But I cannot have the spreadsheet open while the macro is running

     

    Any idea how I can get the spreadsheet to display the information real time?


    Monday, June 18, 2012 12:33 PM

Answers

All replies

  • Hi,

    Thanks for posting on MSDN Forum.

    Based on your issue , I think there are some problems in your code. The one of them is that you didn't give the declaration of Worksheet.

    The following link is to an article that contains the details that I think would be helpful:

    Quickly export Outlook e-mail items to Excel. Please refer to it for your request.

    If you have any further questions regarding this subject, please do not hesitate to let me know.

    Best Regards,


    Leo_Gao [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, June 19, 2012 6:51 AM
    Moderator
  • Option Explicit
     
    Public myXLApp As Excel.Application
    Private WithEvents myOlItems  As Outlook.Items
     
    Private Sub Application_Startup()
        Dim olApp As Outlook.Application
        Dim objNS As Outlook.NameSpace
          Set olApp = Outlook.Application
          Set objNS = olApp.GetNamespace("MAPI")
          Set myOlItems = objNS.GetDefaultFolder(olFolderInbox).Items
    End Sub
     
    Private Sub myOlItems_ItemAdd(ByVal item As Object)
    On Error GoTo ErrorHandler
     
        Dim Msg As Outlook.MailItem
      
        Dim myXLWB As Excel.Workbook
        Dim StrBody As String
        Dim TotalRows As Long, i As Long
        Dim WorkbookPath As String
        
    WorkbookPath = "C:\emails.xls" 'Location of sheet recording mails
     
    'Check new item is an e-mail:
    If TypeName(item) = "MailItem" Then
        Set Msg = item
     
    'Open Excel:
        Set myXLApp = New Excel.Application
        Set myXLWB = myXLApp.Workbooks.Open(WorkbookPath)
      
    'Find end of sheet:
        TotalRows = myXLWB.Sheets(1).Range("A65536").End(xlUp).Row
        i = TotalRows + 1
        
    'Make entries:
        With myXLWB.Worksheets(1)
            .Cells(i, 1) = Format(Msg.SentOn, "mm/dd/yyyy")
            .Cells(i, 2) = Msg.SenderName
            .Cells(i, 3) = Msg.To
            .Cells(i, 4) = Msg.Body
        End With
     
    'Close workbook
        myXLWB.Save
        myXLWB.Close
        myXLApp.Quit
    End If
     
    ProgramExit:
        Exit Sub
    ErrorHandler:
        MsgBox Err.Number & " - " & Err.Description
        Resume ProgramExit
    End Sub

    Ok. This works to a point

     

    It extract the data from new emails but only if the spreadsheet is closed

     

    How can I have both spreadsheet and outlook open and have the macro extract data real time?

     

    On a side note – how can I have outlook flag the emails (yellow flag for instance) that processed by this macro?


    Tuesday, June 19, 2012 7:14 AM
  • do not start or close your apps but rather use Getobject to return running instances of excel and outlook.

    As for marking - i'd rather use categories then flag because flag will make mail appear in to=do list in outlook. Add your custom category to each processed mail. You can also user userproperty to mark it.

    Tuesday, June 19, 2012 10:45 AM
  • do not start or close your apps but rather use Getobject to return running instances of excel and outlook.

    As for marking - i'd rather use categories then flag because flag will make mail appear in to=do list in outlook. Add your custom category to each processed mail. You can also user userproperty to mark it.

    Tried googling to find how to use this. Not much luck

     

    Any chance of an example?

     


    Tuesday, June 19, 2012 11:35 AM
  • in official documentation page you have example with excel

    http://msdn.microsoft.com/en-us/library/e9waz863(v=vs.71).aspx
    • Marked as answer by buxZED Tuesday, June 19, 2012 12:28 PM
    Tuesday, June 19, 2012 11:42 AM
  • If you want to do that as a solution without assigne code in bigger solution, you can use ready tool for export any data from outlook to CSV File (opened in Excel as well): CodeTwo Outlook Export tool is free.


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Wednesday, June 20, 2012 7:55 AM