Microsoft Excel

Excel und VBA: Formeln, Programmierung, Lösungen

Die Excel/VBA-Beispiele

Thema

Outlook-Nachrichten in Excel-Tabellen einlesen

Gruppe

Outlook

Problem

Die Nachrichten eines Outlook-Ordners sollen in hinzuzufügende Tabellenblätter eingelesen werden. Der Outlook-Pfad muss angepasst werden.

Lösung
Den nachstehenden Code in ein Standardmodul eingeben, einer Schaltfläche zuweisen und starten.

StandardModule: Modul1

Sub GrapText()
   Dim objOutlook As Object
   Dim objnSpace As Object
   Dim objFolder As Object
   Dim objMsg As Object
   Dim intCounter As Integer, intCount As Integer, iRow As Integer
   Dim sTxt As String
   Application.ScreenUpdating = False
   Set objOutlook = CreateObject("Outlook.Application")
   Set objnSpace = objOutlook.GetNamespace("MAPI")
   Set objFolder = objnSpace.folders("Persönliche Ordner").folders("Dank")
   intCount = objFolder.Items.Count
   If intCount > 0 Then
      For intCounter = 1 To intCount
         Set objMsg = objFolder.Items(intCounter)
         Worksheets.Add after:=Worksheets(Worksheets.Count)
         objMsg.SaveAs ThisWorkbook.Path & "\temp.txt", olTXT
         Close
         iRow = 0
         Open ThisWorkbook.Path & "\temp.txt" For Input As #1
         Do Until EOF(1)
            iRow = iRow + 1
            Line Input #1, sTxt
            Cells(iRow, 1).Value = "'" & sTxt
         Loop
         Close
      Next intCounter
      Kill ThisWorkbook.Path & "\temp.txt"
   End If
   Set objnSpace = Nothing
   Set objFolder = Nothing
   Set objMsg = Nothing
   Set objOutlook = Nothing
End Sub