utroger
Goto Top

Nur E-Mailinhalte in Excel inportieren, die Exceldatei soll immer wieder mit den E-Mailinhalten ergänzt werden

Also ich bekomme regelmäßig E-Mails die immer den gleichen Absender haben deren Inhalt ich auswerte.
Folgendes würde mir viel Arbeit abnehemen wenn es möglich wäre diese E-Mail Inhalte in Excel in eine Tabelle zu importieren und diese Tabelle immer erweitern zu können, also alte Importe sollen bestehen bleiben und neue sollen sich anfügen.
Die EMails werden in einem separaten Ordner "Eskalation" in meinem Postfach gesendet.

Das ganze sollte funktionieren wenn ich z.B. die Exceldatei öffnen so das automatisch die E-mails importiert werden.

Verwendet wird Excel 07 sowie Outlook 07 mit Win XP

würde mich freuen über eine Lösung

Danke im Vorraus
utroger

Content-ID: 189654

Url: https://administrator.de/forum/nur-e-mailinhalte-in-excel-inportieren-die-exceldatei-soll-immer-wieder-mit-den-e-mailinhalten-ergaenzt-189654.html

Ausgedruckt am: 23.12.2024 um 07:12 Uhr

utroger
utroger 16.08.2012 aktualisiert um 15:18:40 Uhr
Goto Top
so ich bin nun schon ein Stück weiter
habe einen Code der zum Teil das erfüllt was ich will.

Was ich für diesen Code noch benötige ist das die importieren Inhalte in der Exceltabelle angehängt werden so das bereits enthaltene Inhalte bestehenbleiben.

Sub OutlookPosteingang()
Dim OLF As Outlook.MAPIFolder
Dim AnzEintraege As Integer, i As Integer, Email As Integer
Set OLF = GetObject("", "Outlook.Application") _
.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Folders("Unterordnername")
AnzEintraege = OLF.Items.Count
i = 0: Email = 0
While i < AnzEintraege
i = i + 1
Application.StatusBar = "Lese Posteingang " & _
Format(i / AnzEintraege, "0%")
With OLF.Items(i)
Email = Email + 1
Cells(Email + 1, 1).Value = .Body
End With
Wend
Set OLF = Nothing
End Sub

Wenn jetzt noch jemand die letzten Punkte ergänzen könnte?

Hinweis:
Wer diesen Code verwenden möchte muss beachten das der Code im Visual Basic Fenster unter Extras - Verweise der Verweis "Microsoft Outlook xxx Objekt Libary" noch auszuwählen ist (Hacken setzen), ansonsten funktioniert das nicht.
Dann noch den entsprechend Unterordnernamen hinter Folders eintragen, oder wenn direkt aus dem Posteingang importiert werden soll die .Folders("Unterordnername") weglassen

Diesen Code dann als Makro ausführen.

Danke
Gruß
utroger
76109
76109 16.08.2012 aktualisiert um 18:55:21 Uhr
Goto Top
Hallo utroger!

Sollte durch eine Änderung der Codzeile
i = 0: Email = 0
nach
i = 0: Email = Cells(Rows.Count, 1).End(xlUp).Row
möglich sein

Ferner würde ich den Dim für EMail auf Long ändern, ansonsten ist bei Zeile 32767 Schluss (Integer>=32768=Negativ=Error)

Gruß Dieter
Biber
Biber 16.08.2012 aktualisiert um 19:07:16 Uhr
Goto Top
[OT] @76109

Zitat von @76109:
Ferner würde ich den Dim für EMail auf Long ändern, ansonsten ist bei Zeile 32767 Schluss
(Integer>=32768=Negativ=Error)

Gruß Dieter

Öhhmm.... ich habe mich ja eigentlich immer für relativ belastbar und halbwegs stressresistent gehalten....

Aber Hey! in der Liga kann ich nicht mitspielen.....

Wenn an meinen Mailaccount mehr als 32768 Mails gehen, die in den Unterordner "Eskalation" einsortiert werden...

... und meine größte Sorge ist: "Wie bekomme ich die sauber in eine Exceltabelle archiviert, damit mein Cheffe nicht denkt, ich mache gar nix mit diesen Eskalationen...."

RESPEKT!

Ich bin beeindruckt.

Grüße
Biber
[/OT]
76109
76109 16.08.2012 um 23:17:54 Uhr
Goto Top
[OT]@Biber
RESPEKT!
Ich bin beeindruckt.
Ich ebenfallsface-sad

Eventuell habe ich mich mit einem Mailaufkommen von täglich 100-150 Stk. an 365 Tagen im Jahr etwas verschätztface-wink

Gruß Dieter
[/OT]
utroger
utroger 17.08.2012 um 07:21:52 Uhr
Goto Top
Hallo Dieter,

erstmal DANKE für deine Hilfe, jetzt werden die Inhalte angehängt.
Was mir jetzt erst aufgefallen ist, in die erste Zeile werden keine Daten geschrieben er fängt immer erst in der zweiten Zeile an, wo liegt hier mein Fehler.

Gruß utroger
utroger
utroger 17.08.2012 aktualisiert um 07:25:21 Uhr
Goto Top
Hallo Biber,

also das Aufkommen der Emails in diesem Ordner beläuft sich im Jahr so etwa auf 2000 E-Mails, ohne die anderen 7000 E-Mails die sonst noch kommen im Jahr.

Gruß utroger
Biber
Biber 17.08.2012 aktualisiert um 08:32:26 Uhr
Goto Top
Moin utroger,

didi1954 war gestern etwas länger wach, deshalb ist er bestimmt noch beim Frühstück.
Ich versuch mal zu überbrücken:
...
Email = Cells(Rows.Count, 1).End(xlUp).Row 
'Selbst wenn "Email" hier das denkbare Minimum 0 ist  

....
Email = Email + 1 
' ... ist "Email"  hier dann 0+1 = 1  

Cells(Email + 1, 1).Value = .Body 
' und hier wird in Cells( 1 + 1,..), also 2 geschrieben  
...

Grüße
Biber
utroger
utroger 17.08.2012 um 08:41:26 Uhr
Goto Top
Hallo Biber,
so ganz bekomme ich das nicht hin das er mir keine Zeile frei lässt.
Ist es überhaupt möglich die EMailinhalte direkt in die nächste freie Zeile zu importieren.

Glaube ich habe es nicht ganz verstanden.

mit diesem Code lässt er immer 2 Zeilen frei

Sub OutlookPosteingang()
Dim OLF As Outlook.MAPIFolder
Dim AnzEintraege As Integer, i As Integer, Email As Integer
Set OLF = GetObject("", "Outlook.Application") _
.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Folders("Eskalation")
AnzEintraege = OLF.Items.Count
i = 0: Email = Cells(Rows.Count, 1).End(xlUp).Row
While i < AnzEintraege
i = i + 1
With OLF.Items(i)
Email = Email + 1
Cells(Email + 1, 1).Value = .Body
End With
Wend
Set OLF = Nothing
End Sub

Gruß utroger
76109
76109 17.08.2012 um 09:55:13 Uhr
Goto Top
Hallo utroger, Hallo Biber!

@Biber
didi1954 war gestern etwas länger wach, deshalb ist er bestimmt noch beim Frühstück.
Den ersten Kaffee habe ich schon geschafftface-wink

@utroger
Biber hat sicherlich damit gerechnet, dass Du anhand seiner Erklärung von selbst draufkommst.
'=Nächste freie Zeile  
Email = Email + 1

'=Nächste freie Zeile nochmal + 1, also übernächste freie Zeile  
Cells(Email + 1, 1).Value = .Body
Daraus ergibt sich:
'=Nächste freie Zeile  
Cells(Email, 1).Value = .Body

@Biber
Cells(Rows.Count, 1).End(xlUp).Row
Gibt als kleinsten Wert immer 1 zurück, egal ob Leer/Inhalt

Gruß Dieter
utroger
utroger 17.08.2012 um 10:38:55 Uhr
Goto Top
Hallo,
jetzt hab ichs.
Noch mal Danke für die Hilfe

Gruß utroger
arndtk
arndtk 08.04.2013 um 15:00:52 Uhr
Goto Top
Hallo utroger,

sind Sie hier noch aktiv? Ich bin auf der Suche nach genau dieser Lösung und bräuchte etwas Hilfe bzgl. der "Installation".

Gruß
Arndt
utroger
utroger 08.04.2013 um 15:57:36 Uhr
Goto Top
ja bin noch aktiv, wie kann ich eventuell helfen, was genau ist das Problem?
arndtk
arndtk 08.04.2013 um 16:27:51 Uhr
Goto Top
Hallo,

ich Administriere eine Fussballschule und betreue auch die Webseite. Die Anmeldungen der Kinder erfolgen über ein Online-Formular über Wordpress und die Anmeldungsemail hat immer den gleichen Aufbau mit Name, Adresse, Alter, Trikotgröße, Spielerposition usw.

z.B.: http://www.fussballschule-renchtal.de/anmeldeformular-kehl-auenheim-1-k ...

Diese Mails werden in Outlook abgerufen und die Inhalte dann händisch in eine Excel-Tabelle überführt. Der Ideallösung wäre natürlich, die Inhalte automatisiert zu übertragen.

Ich kenne mich zwar am MAC perfekt aus face-wink, nur leider ist Outlook so gar nicht mein Spezialgebiet. Wäre es zuviel verlangt wenn Sie mir grob aufzeigen würden, was ich wo und wie aktivieren muss, um Ihr Makro auch für mich nutzbar zu machen?

Vielen Dank vorab und Grüße
Arndt
utroger
utroger 09.04.2013 um 07:19:52 Uhr
Goto Top
Also hier noch mal das Makro:

Wie das beim MAC ist mit Outlook hmmm..., denke wenn es so nicht klappt gibt es hirzu bestimmt jemanden der mit MAC helfen kann.

Sub OutlookPosteingang()
Dim OLF As Outlook.MAPIFolder
Dim AnzEintraege As Integer, i As Integer, Email As Integer
Set OLF = GetObject("", "Outlook.Application") _
.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Folders("Der Name in des Postfachs von Outlook")
AnzEintraege = OLF.Items.Count
i = 0: Email = Cells(Rows.Count, 1).End(xlUp).Row
While i < AnzEintraege
i = i + 1
With OLF.Items(i)
Email = Email + 1
Cells(Email + 1, 1).Value = .Body
End With
Wend
Set OLF = Nothing
End Sub


Hinweis:
Wer diesen Code verwenden möchte muss beachten das der Code im Visual Basic Fenster unter Extras - Verweise der Verweis "Microsoft Outlook xxx Objekt Libary" noch auszuwählen ist (Hacken setzen), ansonsten funktioniert das nicht.
Dann noch den entsprechend Unterordnernamen hinter Folders eintragen, oder wenn direkt aus dem Posteingang importiert werden soll die .Folders("Unterordnername") weglassen


wie gesagt ob bei Mac das auch so ist kann ich nicht sagen.
Gruß UT Roger
arndtk
arndtk 09.04.2013 um 08:42:16 Uhr
Goto Top
Hallo,

vielen Dank. Ich werde dies ebenfalls an einem Windowas Rechner installieren wollen ... und eben das ist mein Problem. face-wink Ich bin in der Mac-Welt zu Hause ... ich brauche eher Hilfe auf der PC-Seite. face-smile

Also, ich gehe also unter Outlook/Extras/Makros und erstelle mit dem genannten Code ein Makro, korrekt?

Aber wie und wo weise ich dieses Makro dem gewünschten Posteingang zu? Und wie stelle ich die "Verbindung" zur Excel-Liste her?
Braucht die Excel-Liste einen bestimmten Dateinamen?

Danke vorab und Grüße
Arndt
utroger
utroger 09.04.2013 um 14:33:27 Uhr
Goto Top
Hallo,

nicht in Outlook sondern in Excel ein Macro. Weiter im (Excel) Visual Basic Fenster unter Extras - Verweise, der Verweis "Microsoft Outlook xxx Objekt Libary" noch auszuwählen ist (Hacken setzen). Die XXX stehen für die Version des Outlook

Den Posteingang

Folders("Den Name des Postfachs von Outlook") hier eintragen.

Das Makro wird dort ausgeführt in der Tabelle wo erstellt wird.
Also wenn in Tabelle1 dann wird das Makro in Tabelle 1 die Emaildaten einlesen.


Hoffe es ist verstanden
arndtk
arndtk 09.04.2013 um 15:56:36 Uhr
Goto Top
Hallo,

ok, verstanden.

Habe nun in Excel den Code in ein Makro kopiert. Dann den Haken bei Microsoft Outlook 12 Objekt Libary gesetzt.

Wenn ich nun MAKRO AUSFÜHREN klicke, dann springt er in den Editor und gibt FEHLER BEIM KOMPILIEREN/BENUTZERDEFINIERTER TYP NICHT DEFINIERT aus. Gleichzeitig markiert er diese Zeile gelb: Sub OutlookPosteingang()

und diese blau: OLF As Outlook.MAPIFolder

In Outlook heißt mein Posteingang: INBOX

Ich hoffe Sie haben noch etwas Geduld. face-wink

Danke und Gruß
Arndt
utroger
utroger 10.04.2013 aktualisiert um 07:27:59 Uhr
Goto Top
Hallo,

denke jetzt nur noch Sub OutlookPosteingang()umbenennen in Sub OutlookInbox() dann sollte es funktionieren.
Daran denken wenn die E-Mails nicht in einem Unterordner sind dann "Folders("Der Name in des Postfachs von Outlook")" löschen.

Sollte das immer noch nicht funktionieren dann bitte @Biber anschreiben, er hat mir damals geholfen und er wird auch diese Problem lösen können.
76109
76109 10.04.2013 um 09:54:11 Uhr
Goto Top
Hallo arndtk!

Wenn ich nun MAKRO AUSFÜHREN klicke, dann springt er in den Editor und gibt FEHLER BEIM KOMPILIEREN/BENUTZERDEFINIERTER TYP NICHT DEFINIERT aus. Gleichzeitig markiert er diese Zeile gelb: Sub OutlookPosteingang()
Diese Zeile ist gelb markiert, weil der Debugger aufgrund des Fehlers in der Folgezeile, an dieser Stelle stehen bleibt. D.h. diese Zeile ist OK
und diese blau: OLF As Outlook.MAPIFolder
Stellt sich die Frage, ob Du unter Verweise den Haken gesetzt und mit OK bestätigst hast? Also Verweise erneut öffnen und nachsehen, ob die Outlook-Lib am Anfang mit Haken steht...

Gruß Dieter
arndtk
arndtk 10.04.2013 aktualisiert um 11:34:06 Uhr
Goto Top
Hallo zusammen,

es klappt! Super! Vielen Dank nochmals für Eure Hilfe, toll!!

Die Umstellung auf INBOX hat geholfen!
Außerdem hatte ich die Office-Lib und nicht die Outlook-Lib aktiviert. face-sad

Nun aber noch eine Frage. Es werden nun die kompletten Email-Inhalte importiert. Hätte ich auch die Möglichkeit, nur bestimmte Inhalte aus den Mails zu importieren?

Mir geht es hauptsächlich um den Namen, die Anschrift, Trikot-Größe und die Email-Adresse. Diese Infos werden über ein Wordpress-Plugin in einem Formular erfasst und dann als eine Anmeldung per Mail versendet.

Aber nochmal ... vielen Dank für Eure Geduld und Hilfe!!

Gruß
Arndt
utroger
utroger 11.04.2013 um 07:52:24 Uhr
Goto Top
Hallo,

also wie man geziehlt direkt aus dem E-Mail nur bestimmte Bereich exportiert kann ich nicht sagen.
Aber in Excel ist es kein Problem entsprechende Filterungen z.B. über Formeln auszulesen.
Diese würde dann z.B. so aussehen das die kpl. E-Mail in Tabbelle1 eingelesen wird und in Tabelle2 nur die bestimmten Bereich von Tabelle1 aufgeführt werden. Da es sich hier so wie beschrieben, immer um die gleichen Felder bzw. Positionen handelt sollte es keine Problem darstellen.

Wenns nicht klappen sollte einfach mal ein Beispiel posten und dann sehen wir weiter

Gruß utroger
arndtk
arndtk 11.04.2013 um 14:00:51 Uhr
Goto Top
Hallo utroger,

folgenden Inhalt habe ich nun vorliegen:


Camp vom 25. Juli – 27.Juli 2013 in Ebersweier - 1 Kind

Angaben der Eltern

Vor- und NachnameMaxMustermann

Straße Musterstrasse 12
PLZ 77770

OrtMusterstadt

Telefon (idealerweise Ihre Mobilnummer)01234/123466

E-Mail HYPERLINK "ma

Ich habe den Hinweis bzgl. evtl. Mehrkosten beim Besuch des Kinderzentrums und des Dietmar-Hopp-Stadions von 1899 Hoffenheim gelesen und stimme ihm zu. ✔

Bestätigung der HYPERLINK "http://www.fussballschule-renchtal.de/?page_id=260" \nAllg. Geschäftsbedingungen. ✔

Angaben zum Kind

Vor- und NachnamePeter Mustermann

Geburtsdatum11.12.13

Bist Du Mitglied im Sparkassen-KNAX-Club der Sparkasse Offenburg? Ja

Bist Du Mitglied im S-Club der Sparkasse Offenburg? Nein

Spielst Du schon in einem Verein? Ja

Wenn ja, in welchem? TUS Durbach

Welche Position? Stürmer

Muss Ihr Kind Medikamente einnehmen Nein

Wenn ja, welche und wie oft?

Angaben zum Trikot

Größe M 140-152

Rückenbeschriftung mit Namen (Zusatzkosten von 5 Euro) Ja

Wenn ja, welcher Name?Max

Stutzengröße

Stutzengröße entspricht der Schuhgröße 35 - 38

Sonstige Fragen

Wie sind Sie/bist Du auf uns aufmerksam geworden? Homepage FVE Ebersweier

Sicherheitsabfrage

Bitte geben Sie folgende Ziffern ein: b2ik


Könnten Sie mir evtl. eine Beispielformel erstellen? Dann würde ich mir diese als Vorlage nehmen und die benötigten Felder selber anlegen.

DANKE!!!

Gruß
Arndt
utroger
utroger 11.04.2013 um 14:12:13 Uhr
Goto Top
sind diese Angaben in Excel eingelesen oder ist das nur die E-Mail nicht in Excel eingelesen?
arndtk
arndtk 11.04.2013 um 14:14:12 Uhr
Goto Top
Doch, die sind so in Excel eingelesen!
utroger
utroger 11.04.2013 um 14:24:51 Uhr
Goto Top
Das ganze könnte so aussehen in Tabelle2 wenn in Tabelle1 diese Felder die gewünschten informationen enthalten
Vor und Nachname Strasse PLZ Ort Trikot Größe E-Mail
=Tabelle1!A5 =Tabelle1!A7 =Tabelle1!A8 =Tabelle1!A10 =Tabelle1!A42 =Tabelle1!A14
arndtk
arndtk 11.04.2013 um 14:40:46 Uhr
Goto Top
Oh man, ich trau mich gar nicht weiter zu Fragen ...

Wo muss ich das dann eingeben? In Tabelle zwei? In welches Feld?

Sollten Sie mal Hilfe in InDesign, Photoshop, Illustrator benötigen...dann bitte melden face-wink

Gruß
Arndt
utroger
utroger 11.04.2013, aktualisiert am 12.04.2013 um 15:13:12 Uhr
Goto Top
Aslo in Tabelle2 in das Feld z.B. A1 =Tabelle1!A5
Dies bedeutet das aus Tabelle1 das Feld A5 in Tabelle2 in das Feld A1 eingelesen wird
In diesem Fall, wenn also Vor und Nachname in Tabelle1 im Feld A5 steht wird der Vor und Nachname in Tablle2 in das Feld A1 eingelesen.
nach diesem Prinzip kann man sich die gewünschten Felder auswählen
arndtk
arndtk 11.04.2013 um 15:00:13 Uhr
Goto Top
Super, das habe ich verstanden! face-smile

Ich probiere es aus und melde mich zurück!

Danke!
arndtk
arndtk 11.04.2013 um 15:43:23 Uhr
Goto Top
Ich nochmal.

Ein Problem gibt es leider noch. Der Email-Inhalt wird mit dem Makro in EIN Feld übertragen. D.h., alle oben geposteten Inhalte stehen in z.B. A1.

Kann man diese noch per Befehl "aufteilen" lassen? Ich kenne das nur aus InDesign ... nach jedem RETURN ein Feld weiterspringen. Geht sowas auch in Excel?

Oder müsste sogar das Makro angepasst werden?

Grüße
Arndt
76109
76109 12.04.2013 um 01:50:29 Uhr
Goto Top
Hallo arndtk!

Wenn ich das richtig sehe, dann bekommst Du die Emails im HTML-Format. Von daher müsste der Email-Quelltext Table-Tags (<table>) enthalten. Wenn dem so ist, dann speichere zu Testzwecken in Outlook eine Mail als HTML-Datei per 'Datei>Speichern unter'. Anschließend öffnest Du Excel mit einer neuen Arbeitsmappe und importierst diese Datei per 'Datei>Externe Daten importieren>Daten importieren'. Damit solltest Du eine vernünftige Datenansicht erhalten und ein entsprechendes Feedback geben...

Gruß Dieter
utroger
utroger 12.04.2013 aktualisiert um 13:29:56 Uhr
Goto Top
Hallo,
eine Möglichkeit gibt es auch über eine Fromel die dann individuell anzupassen ist je nach dem was das Ergebnis bringen soll.
Hier ein Beispiel:
=TEIL(Tabelle1!A1;MAX(WENN(TEIL(Tabelle1!A1;SPALTE(1:1);1)="Nachname";SPALTE(1:1)))+18;99)
Diese Fromel sagt folgendes:
Such mir in Tabelle1 im Feld A1 das Wort Nachname und schreibe mir alles was rechts nach Nachname kommt in das Feld

Wobwei die +18 steht für den Beginn ab wie viel Zeichen der Wert aus Zelle A1 übernommen wird.

einfach mal ausprobieren, denke dann erklärt sich es von selbst
Dann für die nächsten Felder einfach die Formel anpassen

Gruß utroger
arndtk
arndtk 12.04.2013 um 16:42:55 Uhr
Goto Top
Hallo Dieter,

danke für den Hinweis. Auf diesem Wege erhalte ich eine strukturierte Datei mit den Angaben in einzelnen Feldern.

Name = B1
Strasse = B2
usw.

Jetzt stellt sich mir natürlich die Frage, wie ich diesen Schritt automatisiert hinbekomme? Oder muss ich das gar nicht?

Ich stehe auf'm Schlauch?

@utroger: Auch hier vielen Dank ... dieser Vorschlag hört sich plausibel an.

Ich warte mal was Dieter schreibt, ggf. gibt es ja noch eine andere Lösung.

Vielen Dank (wie immer!) und ein schönes Wochenende
Arndt
76109
76109 13.04.2013 aktualisiert um 03:10:22 Uhr
Goto Top
Hallo Arndt!

Bei meinem Vorschlag dachte ich schon an eine Automatisierung, aber um dies so einfach wie möglich zu machen, wollte ich zunächst erstmal wissen, ob Du mit einer Html-Datei eine saubere Struktur erhälst face-wink

Nun wäre noch die Frage zu klären, ob Du alle Daten in der Excel-Datei haben möchtest oder eventuell nur Eckdaten mit einem Link auf diejenige Html-Datei. Also eine Art Html-Archivierung...

Ansonsten benötige ich eine genaue Zellfolge der Html-Tabelle, wobei ich mal annehme, dass die Zeile 1 in der Daten-Tabelle eine Überschriftzeile ist und die Daten ab Zeile 2 Spalte A beginnen.

Die Html-Zellfolge hätte ich dann gerne im Format "B1,B2,...". Falls Du Zelleninhalte zusammenfügen möchtest, also z.B. Zelle D1 + Leerzeichen + Zelle D2, dann schreibe im Format "B1,B2,D1+D2,..."

Außerdem könntest Du noch etwas über den Ablauf erzählen z.B.:
Befinden sich in diesem Posteingangs-Ordner auch noch andere Mails außer Anmeldungen?
Werden die Mails nach dem Excel-Import im Posteingangs-Ordner gelöscht bzw. sollen sie beim Import automatisch gelöscht werden...?


Hoffe Du hast das soweit alles verstandenface-wink?

Gruß Dieter


PS. Wäre nicht schlecht, wenn Du Dir eine Mustermann-Anmeldung zusendest und mir den Quelltext per PN zuschickst, damit ich gegebenenfalls auf Formate (Häkchen...?) Einfluss nehmen kann.
arndtk
arndtk 15.04.2013 um 23:09:03 Uhr
Goto Top
Hallo Dieter,

bin am WE leider nicht dazu gekommen. Melde mich schnellst möglich zurück ... ! face-smile

Gruß
Arndt
76109
76109 18.04.2013, aktualisiert am 20.04.2013 um 13:31:49 Uhr
Goto Top
Hallo Arndt!

Hier mal der Code mit Bezug auf Deine PN...
Option Explicit
Option Compare Text

Private Const RowStart = 4                  'Ab Zeile 4 Anmeldungen eintragen  

Private Const SheetData = "Anmeldungen"     'Tabellenname Anmeldedaten  
Private Const SheetTemp = "Temp"            'Tabellenname Htm-Import  

Private Const MailsSubject = "*Anmeldung zum Camp*"  
Private Const HtmDataCells = "A8,A9," & _  
                             "B10,B11,B12,B13,B14,B15," & _  
                             "A18," & _  
                             "B19,B20,B21,B22,B23,B24,B25,B26,B27," & _  
                             "A28," & _  
                             "B29,B30,B31," & _  
                             "A32," & _  
                             "B33,B34,B35,B36,B37,B38,B39,B40,B41," & _  
                             "A42," & _  
                             "B43,B44,B45"  

Private Const Msg1 = "Keine Emails im Posteingang gefunden!"  
Private Const Msg2 = "Es wurden %1 neue Emails importiert!"  
Private Const Err1 = "Der Email-Import ist bei %1 von %2 Emails fehlgeschlagen!"  

Private Sub CommandButton1_Click()
    Dim oOL As Outlook.Application, oMails As Outlook.Items, oFso As Object, oMailList As Object
    Dim sKey As Variant, sFolderArchiv As String, sFileName As String
    Dim iMailsCount As Integer, iRowNext As Long, i As Integer
    
   'Archiv-Pfad für Htm-Dateien festlegen (Pfad Arbeitsmappe\Archiv)  
    sFolderArchiv = ThisWorkbook.Path & "\Archiv\"  
    
   'Set File-System  
    Set oFso = CreateObject("Scripting.FileSystemObject")  
   'Set assoziatives Array  
    Set oMailList = CreateObject("Scripting.Dictionary")  
    
   'Test ob der Archiv-Ordner schon existiert, ansonsten den Ordner neu erstellen  
    If oFso.FolderExists(sFolderArchiv) = False Then
        oFso.CreateFolder sFolderArchiv
    End If
    
   'Set Outlook-Application  
    Set oOL = CreateObject("Outlook.Application")  
   'Set Mail-Items  
    Set oMails = oOL.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Items  
    
   'Htm-Dateien mit Email-HtmlBody erstellen  
    For i = oMails.Count To 1 Step -1
       'Test Betreff-Text Anmeldung  
        If oMails(i).Subject Like MailsSubject Then
           'Htm-Dateipfad erzeugen  
            sFileName = GetFileName(sFolderArchiv, oMails(i).ReceivedTime)
           'Mail-Item-Index als Key und Htm-Dateipfad als Item in File-Liste aufnehmen  
            oMailList.Add i, sFileName
           'Htm-Datei erstellen  
            oFso.CreateTextFile(sFileName).Write oMails(i).HTMLBody
        End If
    Next
    
    If oMailList.Count Then
       'Nächste frei Zeile in Tabellenblatt ermitteln  
        iRowNext = Cells(Rows.Count, "A").End(xlUp).Row + 1  
        
       'Wenn die Tabelle noch keine Einträge hat, dann nächste freie Zeile ist Start-Zeile  
        If iRowNext < RowStart Then iRowNext = RowStart
        
       'Anzahl Mails sichern  
        iMailsCount = oMailList.Count
       
       'Htm-Dateien importieren  
        For Each sKey In oMailList.Keys
           'Test ob Htm-Datei existiert  
            If oFso.FileExists(oMailList.Item(sKey)) Then
               'Htm-Import True: Email in Posteingang löschen / Htm-Datei in File-Liste löschen  
               'Htm-Import False: Email in Posteingang nicht löschen / Htm-Datei in Archiv löschen  
                If GetMailDataImport(oMailList.Item(sKey), iRowNext) Then
                    'oMails(sKey).Delete    'Zum Löschen der Emails, Kommentarzeichen am Anfang entfernen  
                    oMailList.Remove sKey
                    iRowNext = iRowNext + 1
                Else
                    oFso.DeleteFile oMailList.Item(sKey)
                End If
            End If
        Next
       
       'Test ob alle Htm-Dateien importiert wurden? Entsprechende Meldung ausgegeben  
        If oMailList.Count Then
            MsgBox Replace(Replace(Err1, "%1", oMailList.Count), "%2", iMailsCount), vbExclamation, "Fehler..."  
        Else
            MsgBox Replace(Msg2, "%1", iMailsCount), vbInformation, "Hinweis..."  
        End If
        
       'Temp-Sheet bereinigen  
        Call SheetTempCleanUp
    Else
        MsgBox Msg1, vbInformation, "Hinweis..."  
    End If
     
   'Outlook.App beenden  
    oOL.Quit
End Sub

'Diese Funktion importiert die Htm-Dateien in's Temp-Sheet und schreibt die Daten in die Daten-Tabelle  
Private Function GetMailDataImport(ByRef sFile, ByVal iRow As Long) As Boolean
    Dim aDataCells As Variant, aDataPlus As Variant, aDataValues As Variant
    Dim vValue As Variant, sQueryFile As String, i As Integer, p As Integer
    
   'QueryTable-Connection-File definieren  
    sQueryFile = "FINDER;File:///" & Replace(sFile, "\", "/")  
                        
    On Error Resume Next
    
   'Import Htm-Datei  
    With Sheets(SheetTemp)
        .UsedRange.Cells.Clear
         With .QueryTables.Add(Connection:=sQueryFile, Destination:=.Range("A1"))  
            .AdjustColumnWidth = True
            .WebSelectionType = xlAllTables
            .WebFormatting = xlWebFormattingNone
            .Refresh BackgroundQuery:=False
            .Delete
        End With
    End With
    
   'Test Import OK  
    If Err.Number = False Then
        GetMailDataImport = True
        
       'Htm-Zell-Adressen in Array splitten  
        aDataCells = Split(HtmDataCells, ",")  
    
       'Array für die Htm-Daten erzeugen  
        ReDim aDataValues(UBound(aDataCells) + 1)
    
       'Htm-Daten ins Daten-Array schreiben  
        With Sheets(SheetTemp)
            For i = 0 To UBound(aDataCells)
                aDataPlus = Split(aDataCells(i), "+")  
                
                vValue = .Range(Trim(aDataPlus(0))).Value
                
                For p = 1 To UBound(aDataPlus)
                    vValue = vValue & " " & .Range(Trim(aDataPlus(p))).Value  
                Next
                
                aDataValues(i) = vValue
            Next
        End With
        
       'Den Dateinamen der Htm-Datei dem Daten-Array hinzufügen  
        aDataValues(UBound(aDataValues)) = Dir(sFile)
        
       'Daten-Array in die Daten-Tabelle übernehmen  
        Cells(iRow, "A").Resize(1, UBound(aDataValues) + 1).Value = aDataValues  
    Else
        Err.Clear
    End If
End Function

'Diese Function gibt den Dateipfad einer neuen Htm-Dateipfad zurück (Dateiname enthält das Empfangsdatum)  
'Format: ArchivPfad + Jahr + Monat + Tag + 4-stellige fortlaufende Nummer ("..\Archiv\20130413_####.htm")  
Private Function GetFileName(ByRef sFolder, ByVal dDate As Date) As String
    Dim sName As String, sFileName As String, iCount As Integer
    
    sName = sFolder & Year(dDate) & Right("0" & Month(dDate), 2) & Right("0" & Day(dDate), 2) & "_"  
    
    Do: iCount = iCount + 1
        sFileName = sName & Right("000" & iCount, 4) & ".htm"  
    Loop Until Dir(sFileName) = ""  
    
    GetFileName = sFileName
End Function

'Diese Funktion entfernt Restfragmente der QueryTable-Importe und löscht die Zell-Inhalte im Temp-Sheet  
Private Sub SheetTempCleanUp()
    Dim oName As Name
    For Each oName In ThisWorkbook.Names
        If InStr(oName.Name, "ExterneDaten") > 0 Then oName.Delete  
    Next
    Sheets(SheetTemp).UsedRange.Cells.Clear
End Sub

Schritt 1:
Es werden 2 Tabellenblätter benötigt. Das 1. z.B. mit Namen 'Anmeldungen' und das 2. mit dem Namen 'Temp'. Das 2. Tabellenblatt (Temp)' kannst/solltest Du beim späteren Gebrauch ausblenden.

Schritt 2:
Falls vorhanden, dann lösche im VBA-Editor im Tabellenblatt 'Anmeldungen' den Inhalt und füge anschließend den Quellcode in das Tabellenblatt ein.

Schritt 3:
Sicherstellen, das die Tabellenblatt-Namen mit den Konstanten 'SheetData' und 'SheetTemp' im VBA-Code übereinstimmen.

Schritt 4:
Die Konstante 'RowStart' im VBA-Code die Zeilennummer für die Startzeile angeben. Wenn Du z.B. in der Zeile 1 eine Befehlsschaltfläche (CommandButton) einfügst und in Zeile 3 Deine Spaltenüberschriften stehen, dann wäre die Startzeile die Zeile darunter, also Zeile 4, ab der die Daten eingetragen werden...

Wenn Du nicht weißt, wie das Einfügen eines CommandButtons funktioniert, dann frage nach...

Schritt 5:
Wähle im Menü Debuggen>Kompilieren. Der Code ist OK, sofern keine Fehlermeldung ausgegeben wird.

Schritt 6:
Eventuell vor diesem Schritt erst einmal den unteren Teil zum Programmablauf durchlesen?

Wenn kein Command-Button eingefügt ist, dann den Text-Cursor vor folgende Zeile setzen
Private Sub CommandButton1_Click()
und anschließend den blauen Abspiel-Button oder F5-Taste betätigen...

Schritt 7:
Die Daten-Spalten Deinen Wünschen entsprechend formatieren...

Soweit so gut, hoffe ich zumindest?

Und keine Angst wegen den Emails, die bleiben erst einmal alle erhalten!


Nun zum eigentlich Programmablauf:
Zunächst wird der Pfad der Arbeitsmappe ermittelt und falls noch nicht vorhanden, der Unterordner 'Archiv' für die die Email-Htm-Dateien angelegt.

Anschließend werden alle Outlook-Mails im Posteingang durchforstet und die Emails mit der Betreffzeile 'Anmeldung zum Camp', als Htm-Datei im Ordner 'Archiv' gespeichert. Danach werden die Htm-Dateien in das Sheet 'Temp' importiert und die jeweilige Daten in das Sheet 'Anmeldungen' eingetragen, wobei in der letzten Spalte, der Dateiname der Htm-Datei steht. Bei erfolgreichem Import, werden die Emails im Posteingang gelöscht, sofern in Codezeile 78 am Zeilenanfang das Kommentarzeichen (') nach der Testphase entfernt wurde. War der Import dagegen nicht erfolgreich, dann bleibt die Email im Posteingang erhalten und die Htm-Datei wird gelöscht. Am Ende wird eine entsprechende Meldung ausgegeben...

Sofern sich im Posteingang Emails mit der entsprechenden Betreffzeile befinden, kannst Du den Code zu Testzwecken mehrfach durchlaufen lassen, um zu sehen, wie die jeweiligen Daten erfasst und die Htm-Dateien im Ordner 'Archiv' erstellt werden.

Nach dem beenden der Testphase und der tatsächlichen Nutzung des Email-Imports, müssen zuvor folgende Schritte ausgeführt werden:
Die Daten im Sheet 'Anmeldungen' entfernen.
Die Htm-Dateien im Ordner 'Archiv' löschen.
In Codezeile 78 das Kommentarzeichen (') am Zeilenanfang entfernen. Danach werden die Emails bei erfolgreichem Import im Posteingang gelöscht


Gruß Dieter