Zur Hauptseite ... Zum Onlinearchiv ... Zum Abonnement ... Zum Newsletter ... Zu den Tools ... Zum Impressum ... Zum Login ...

Gedrucktes Heft

Diesen Beitrag finden Sie in Ausgabe 1/2008.

Unser Angebot für Sie!

Lesen Sie diesen Beitrag und 500 andere sofort im Onlinearchiv, und erhalten Sie alle zwei Monate brandheißes Access-Know-how auf 72 gedruckten Seiten! Plus attraktive Präsente, zum Beispiel das bald erscheinende Buch 'Access 2010 - Das Grundlagenbuch für Entwickler'!

Diesen Beitrag twittern

Zusammenfassung

Verwenden Sie Outlook, um eingehende Bestellungen direkt in eine Access-Datenbank zu schreiben.

Techniken

VBA, Outlook

Voraussetzungen

Access 2000 und höher, Outlook 2000 und höher

Beispieldateien

OutlookBestellung.zip (Inhalt von OutlookBestellung.txt unter Outlook in das Modul DieseOutlookSitzung kopieren)

Shortlink

542

Onlinebestellungen mit Outlook und Access

André Minhorst, Duisburg

Der Workflow beim Einsatz einer webbasierten Shoplösung ist nicht immer optimal: Die Bestellungen können manchmal nur über ein Webfrontend abgearbeitet werden oder man erhält die Bestelldaten per E-Mail, ohne dass man diese direkt weiterverarbeiten könnte. Der vorliegende Beitrag zeigt, wie Sie sich unter Umständen das Leben leichter machen. Voraussetzung dafür ist ein Shop, der seine Bestellungen per E-Mail an Sie schickt.

Wenn ein Onlineshop seine Bestellungen per E-Mail an den Betreiber schickt, haben Sie immerhin einen Vorteil: Sie haben die Bestelldaten auf dem heimischen Rechner und können diese auf die eine oder andere Art und Weise abarbeiten.

Im schlimmsten Fall druckt man sich diese aus, kopiert die Kundenadresse in ein Anschreiben, fügt dort ebenfalls manuell die Liste der bestellten Artikel ein und verschickt das Ganze dann.

Immerhin weisen solche per E-Mail erhaltenen Bestellungen einen halbwegs festen Aufbau auf, sodass es meist möglich ist, die enthaltenen Daten per VBA zu parsen. Aber was dann? Nun, wenn Sie Kunden- und Bestelldaten per E-Mail erhalten, können Sie beispielsweise eine kleine Access-Datenbank stricken, die alle notwendigen Informationen in entsprechenden Tabellen speichert.

Diese hilft dann dabei, etwa Rechnungen oder Adressetiketten auszudrucken oder auch Zahlungseingänge oder -erinnerungen zu verwalten.

Erst einmal muss man die Bestelldaten jedoch in die Datenbank bekommen. Auch hier gibt es eine Reihe verschiedener Möglichkeiten:

Dass Sie die Daten nicht von Hand abtippen oder per Drag and Drop aus der Mail in das entsprechende Formular der Anwendung kopieren möchten, ist klar. Also parsen Sie den Inhalt der Mail per VBA. Aber wie geht das? Wenn Sie die Mail erhalten und öffnen, finden Sie dort zunächst den Mailbody mit den Daten vor - beispielsweise in der folgenden stark vereinfachten Form:

Kundendaten:

Anrede: Herr

Vorname: André

Nachname: Minhorst

...

Bestelldaten:

ArtikelID: 12

Anzahl: 4

ArtikelID: 14

Anzahl: 1

...

Diesen Text könnten Sie nun aus der Mail herauskopieren, in ein Textfeld eines eigens dafür vorgesehenen Formulars einfügen und dann auf eine Schaltfläche klicken, die den Inhalt der Mail parst und in die Felder der entsprechenden Tabellen hineinschreibt.

Je nachdem, wie viele Bestellungen am Tag hereinkommen, kann das sehr aufwendig werden: Also streben wir eine weitere Stufe auf dem Weg zur vollständigen Automatisierung an - das automatische Einlesen der E-Mail.

Dazu gehen wir davon aus, dass Sie Microsoft Outlook verwenden. Obwohl das Objektmodell von Outlook, gerade in Bezug auf die vorhandenen Ereignisse, in den letzten Office-Versionen stark erweitert und verbessert wurde, enthält bereits Outlook 2000 das entscheidende Ereignis, das beim Eingang einer E-Mail ausgelöst wird.

Das bedeutet, dass Sie Outlook so programmieren können, dass beim Eingang einer neuen Mail etwas geschieht - nämlich der Aufruf einer speziellen Ereignisprozedur.

Und was tun wir damit? Ganz klar: Wir lesen den Inhalt der E-Mail aus und schauen erst einmal, ob es sich dabei um eine Bestellung handelt. Falls ja, schnappen wir uns den Body der E-Mail und vielleicht noch weitere Eigenschaften und schreiben die Bestelldaten in eine eigens dafür vorbereitete Bestelldatenbank.

Diese ist, um es vorwegzunehmen, sehr rudimentär ausgestattet - schließlich soll diese Lösung zunächst einmal beschreiben, wie Sie den Workflow von der eingehenden Bestellung bis zur weiteren Verarbeitung vereinfachen können. Und sind die Bestelldaten dann einmal in der Datenbank, ist alles weitere ein Klacks - einen Bericht mit einer Rechnung einzurichten, sollte wohl kein allzu großes Problem darstellen.

Eingehende Mails abfangen

Fangen wir also mit der Vorbereitung von Outlook an, indem wir das Ereignis zum Abfangen eingehender Mails programmieren. Dies geschieht in dem einzigen standardmäßig vorhandenen Modul von Outlook namens DieseOutlookSitzung (Name in der deutschen Version).

Das passende Ereignis, gefüllt mit einer Testmeldung, sehen Sie in Abb. 1. Wenn Sie nun eine Mail mit dieser Outlook-Instanz empfangen, erscheint ein entsprechendes Meldungsfenster.

abb001.tif

Abb. 1: Outlook stellt dieses Modul standardmäßig bereit.

Diese Routine soll natürlich kein Meldungsfenster anzeigen, sondern Daten aus der Mail nach Access exportieren. Dazu brauchen Sie zunächst einmal einen Verweis, mit dem Sie auf die DAO-Bibliothek zugreifen können - die brauchen Sie nämlich für den Zugriff auf die Zieldatenbank. Den benötigten Verweis setzen Sie im Verweise-Dialog des VBA-Editors von Outlook (s. Abb. 2).

abb002.tif

Abb. 2: Für den Zugriff aus Outlook auf die Access-Datenbank benötigen Sie einen Verweis auf die jeweilige Version der DAO-Bibliothek.

Aussehen der Bestell-E-Mail

Die nachfolgend zu bearbeitenden Bestellungen sollen wie in Abb. 3 aussehen. Es gibt der Einfachheit halber nur eine Adresse, nicht zwei verschiedene (Liefer- und Rechnungsanschrift). Die Einzelheiten zu den bestellten Artikeln bestehen aus der Artikel-ID und der Anzahl - alle anderen Informationen zu den Artikeln sollen bereits in der Datenbank vorhanden sein.

abb003.tif

Abb. 3: Die Informationen dieser E-Mail sollen in den entsprechenden Tabellen einer Access-Datenbank landen.

Datenmodell

Das Datenmodell setzt sich aus drei Tabellen zusammen:

  • tblKunden: Enthält die Kundendaten wie Name oder Adresse.
  • tblArtikel: Enthält die Artikelstammdaten.
  • tblBestellungen: Enthält die Daten zu jeder einzelnen Bestellung - hier nur aus einer ID und dem Bestelldatum bestehend.
  • tblPositionen: Ordnet die Artikel und die passende Anzahl der jeweiligen Bestellung zu.

Abb. 4 zeigt das Datenmodell der Beispieldatenbank: Die Tabelle tblPositionen stellt dabei eine m:n-Beziehung zwischen den Tabellen tblBestellungen und tblArtikel her, wobei zusätzliche Informationen wie die Anzahl des jeweils bestellten Artikels und sein Preis gespeichert werden. Der Preis ist zwar auch in der Tabelle tblArtikel enthalten, aber - so viel Ordnung muss sein - der Preis eines Artikels kann sich ändern, davon sollen aber längst erledigte Bestellungen nicht betroffen sein. Deshalb wird dieser mit jeder Bestellung nochmals in der jeweils gültigen Höhe gespeichert.

abb004.tif

Abb. 4: Das Datenmodell der Beispieldatenbank

Alarm! Eine Bestellung!

Ob es sich bei einer eingehenden Mail um eine Bestellung handelt, muss Outlook zunächst erst einmal herausfinden. Das kann es am einfachsten, wenn der Absender - in diesem Fall ein nicht näher bezeichneter Onlineshop - die Mail auf bestimmte Art kennzeichnet. Nehmen wir also beispielsweise an, der Betreff enthielte den Ausdruck [Bestellung]. Die beim Eingang der Mail ausgelöste Ereignisprozedur soll also zunächst einmal prüfen, ob sie es mit einer Bestellung oder einer anderen Mail zu tun hat, und nur im ersteren Fall weiterlaufen, um den Inhalt der Mail zu untersuchen - was im Übrigen von eventuell vorliegenden Regeln unbeeinflusst bleibt.

Nun sollte man meinen, dass Outlook beim Auslösen des Ereignisses, das beim Eintreffen einer neuen Mail ausgelöst wird, in irgendeiner Art und Weise einen Verweis auf das neu eingetroffene Objekt liefert. Tut es aber nicht: Die Prozedur Application_NewMail enthält keinerlei Parameter.

Also behelfen Sie sich mit einer kleinen Notlösung. Schauen Sie sich dazu die folgenden Zeilen an, die Sie in die Routine Application_NewMail einbauen. Zunächst einmal deklarieren Sie zwei Objektvariablen - eine für den Ordner Posteingang als Objekt des Typs MAPIFolder, eine für die E-Mail selbst:

Dim objInbox As MAPIFolder

Dim objMail As MailItem

Dann setzen Sie mit der Variablen objInbox einen Verweis auf den Posteingangs-Ordner (in einer Zeile):

Set objInbox = GetNamespace("Mapi").GetDefaultFolder(olFolderInbox)

Dieser Ordner enthält alle neu eingegangenen E-Mails, die noch nicht gelöscht oder in andere Ordner verschoben wurden. Zwischen neuen und gelesenen E-Mails kann man dabei durch die Eigenschaft Unread eines MailItem-Objekts unterscheiden. Dies machen sich die folgenden Zeilen zu Nutze, die zunächst alle im Inbox-Ordner befindlichen E-Mails durchlaufen. Alle E-Mails, die noch nicht gelesen wurden und in Outlook fett dargestellt werden, unterzieht die Routine einer genaueren Untersuchung: Sie schaut nämlich nach, ob der Betreff der E-Mail [Bestellung] lautet. Trifft das zu, zeigt die Routine eine Meldung an (s. Abb. 5) und stellt die Eigenschaft Unread der E-Mail auf False ein, was bedeutet, dass die E-Mail nun den Status Gelesen aufweist.

abb005.tif

Abb. 5: Meldungsfenster mit dem Body einer Bestellungs-E-Mail

For Each objMail In objInbox.Items

    If objMail.UnRead = True Then

        If objMail.Subject = "[Bestellung]" Then

            MsgBox objMail.Body

        End If

        objMail.UnRead = False

    End If

Next objMail

Die richtigen Mails erkennen wir nun, aber es fehlt noch die Funktionalität, um ihren Inhalt in die Datenbank einzutragen. Die dazu benötigte Routine soll BestellungEinlesen heißen. Ihren Aufruf positionieren Sie dort in der Ereignisprozedur Application_NewMail, wo sich bis jetzt das Meldungsfenster zur Ausgabe des Bestelltextes befindet. Als Parameter übergeben Sie die Objektvariable mit der Bestell-Mail:

...

If objMail.Subject = "[Bestellung]" Then

    BestellungEinlesen objMail

End If

...

Bestellungen einlesen

In den nächsten Schritten geschieht Folgendes:

  • Die Anwendung liest die Kundendaten aus und legt einen neuen Datensatz in der Tabelle tblKunden an. Anschließend ermittelt sie die ID des neu angelegten Datensatzes (eine Prüfung, ob ein Kunde schon vorhanden ist, wurde hier zur Vereinfachung weggelassen).
  • Die Anwendung liest aus der Mail das Sendedatum sowie den Mail-Inhalt aus und trägt diese Daten in die Tabelle tblBestellungen ein. Die soeben ermittelte KundenID wird ebenfalls in diese Tabelle eingetragen. Für das nachfolgende Schreiben der einzelnen Positionen in die Tabelle tblPositionen braucht die Anwendung noch die ID der neu angelegten Bestellung, die ebenfalls hier ermittelt wird.
  • Schließlich folgen die einzelnen Positionen der Bestellung. Diese werden ebenfalls aus dem Mailbody ausgelesen und in der Tabelle tblPositionen gespeichert.

Die Hauptroutine zum Einlesen von

Bestellungen

Die Routine BestellungEinlesen erhält lediglich einen Verweis auf die aktuell zu verarbeitende Mail. Daraus verwendet sie zwei Informationen:

Erstens das Datum, an dem die Mail abgesendet wurde, und zweitens den Inhalt des Mailbodys.

Eine Variable des Typs DAO.Database erhält einen Verweis auf die Zieldatenbank - nochmal zur Erinnerung: Es handelt sich hier um Routinen, die von Outlook aus auf eine Datenbank zugreifen sollen.

Die Deklaration und das Füllen der Variablen sehen wie folgt aus:

Dim db As DAO.Database

Set db = _

DBEngine.OpenDatabase("c:\Bestellungen.mdb")

Den Pfad der Zieldatenbank müssen Sie natürlich noch Ihren Gegebenheiten anpassen.

Den Text der E-Mail weist die Routine der Variablen strBestellung zu:

Dim strBestellung As String

strBestellung = objMail.Body

Die folgenden Variablen dienen dem Speichern der in der Mail enthaltenen Kundeninformationen:

Dim strAnrede As String

Dim strVorname As String

Dim strNachname As String

Dim strStrasse As String

Dim strPLZ As String

Dim strOrt As String

Sie werden jeweils mit der Funktion GetText gefüllt, die Sie in Listing 1 komplett abgedruckt finden. Diese Routine macht sich Textfunktionen wie InStr und Mid zu Nutze, um den jeweils in einer Zeile befindlichen Wert etwa für die Anrede oder den Vornamen auszulesen.

Listing 1: Einlesen bestimmter Elemente aus dem Mailtext

Private Function GetText(strBody As String, strPart As String)

    Dim intPosStart As Integer

    Dim intPosEnde As Integer

    Dim strText As String

    Dim intLenPart As Integer

    Dim intLenText As Integer

    intLenPart = Len(strPart)

    intPosStart = InStr(1, strBody, strPart) + intLenPart + 1

    If intPosStart > 0 Then

        intPosEnde = InStr(intPosStart, strBody, Chr(13))

        If intPosEnde > 0 Then

            intLenText = intPosEnde - intPosStart

            strText = Trim(Mid(strBody, intPosStart, intLenText))

        Else

            strText = Trim(Mid(strBody, intPosStart))

        End If

    End If

    GetText = strText

End Function

Die Funktion GetText erwartet die zu durchsuchende Zeichenkette sowie den Ausdruck, dessen Wert sie ermitteln soll. Das wird am Beispiel der folgenden Zeile deutlicher:

Beispielparameter: Beispielwert

Hier entspricht Beispielparameter dem mit strPart angegebenen Wert und die Funktion GetText soll den hinter dem Doppelpunkt angegebenen Ausdruck zurückliefern. Dazu ermittelt die Funktion zunächst die Position, an welcher der gewünschte Ausdruck beginnt, und sucht dann nach einem Zeilenumbruch, der den gesuchten Ausdruck abschließt. Gibt es einen solchen Zeilenumbruch, liefert die Routine den Ausdruck bis zu dieser Stelle zurück, wenn nicht, geht sie davon aus, dass der Ausdruck bis zum Ende der Zeichenkette läuft, und gibt einfach den Rest der Zeichenkette zurück.

strAnrede = GetText(strBestellung, "Anrede")

strVorname = GetText(strBestellung, "Vorname")

strNachname = GetText(strBestellung, "Nachname")

strStrasse = GetText(strBestellung, "Strasse")

strPLZ = GetText(strBestellung, "PLZ")

strOrt = GetText(strBestellung, "Ort")

Sind diese Informationen bekannt, legt die Routine einen neuen Kundendatensatz mit den ermittelten Kundeninformationen an (in einer Zeile):

db.Execute "INSERT INTO tblKunden(Anrede,

Vorname, Nachname, Strasse, PLZ, Ort)

VALUES('" & strAnrede & "', '" & strVorname

& "', '" & strNachname & "', '" & strStrasse

& "', '" & strPLZ & "', '" & strOrt & "')"

Ein Feld des neuen Datensatzes füllt Access automatisch, nämlich das Primärschlüsselfeld namens KundeID. Diesen Wert ermittelt die Routine im nächsten Schritt, um ihn beim Anlegen des Bestelldatensatzes weiterverwenden zu können:

Dim lngKundeID As Long

lngKundeID = _

db.OpenRecordset("SELECT @@IDENTITY", _

dbOpenDynaset)(0)

Mehr zu der hier verwendeten Abfrage erfahren Sie im Artikel ID des zuletzt hinzugefügten Datensatzes ermitteln unter http://www.access-im-unternehmen.de/589.0.html. Bevor es ans Anlegen des Bestelldatensatzes geht, liest die Routine noch das Bestelldatum in die Variable dblBestelldatum ein:

Dim dblBestelldatum As Double

dblBestelldatum = CDbl(objMail.SentOn)

Das Schreiben der Bestellung übernimmt schließlich die folgende Zeile (ohne Zeilenumbruch):

db.Execute "INSERT INTO tblBestellungen(

Bestelldatum, KundeID) VALUES("

& Replace(dblBestelldatum, ",", ".")

& ", " & lngKundeID & ")", dbFailOnError

Fehlen noch die einzelnen Positionen, die in die Tabelle tblPositionen geschrieben werden sollen. Dazu brauchen Sie zunächst die ID der neu angelegten Bestellung in der Tabelle tblBestellungen. Hier kommt erneut die bereits oben verwendete Abfrage zum Ermitteln des zuletzt angelegten Autowerts zum Einsatz:

Dim lngBestellungID As Long

lngBestellungID = db.OpenRecordset("SELECT @@IDENTITY", dbOpenDynaset)(0)

Den Abschluss besorgt der Aufruf einer weiteren Routine, die ausschließlich die Positionen aus dem Mailtext herausliest und in die Tabelle tblPositionen einträgt. Diese erwartet den Text der E-Mail, einen Verweis auf die Zieldatenbank sowie die ID der Bestellung als Parameter:

WritePositions strBestellung, db, lngBestellungID

Positionen in die Datenbank eintragen

Die Routine aus Listing 2 schreibt die Positionen in die Tabelle tblPositionen. Sie besteht im Wesentlichen aus einer Schleife, die erst dann abbricht, wenn alle Zeichenketten ArtikelID in dem übergebenen Text gefunden worden sind. Die einzelnen Positionen in der Mail sehen etwa so aus:

Listing 2: Schreiben der Bestellpositionen in die Tabelle tblPositionen

Private Function WritePositions(strBestellung As String, db As DAO.Database, _

    lngBestellungID As Long)

    Dim intPosStart As Integer

    Dim intPosEnde As Integer

    Dim lngArtikelID As Long

    Dim intArtikelanzahl As Integer

    Dim curPreis As Currency

    Dim strPosition As String

    intPosStart = InStr(1, strBestellung, "ArtikelID")

    Do While intPosStart > 0

        intPosEnde = InStr(intPosStart + 1, strBestellung, "ArtikelID")

        If intPosEnde > 0 Then

            strPosition = Mid(strBestellung, intPosStart, intPosEnde - intPosStart)

        Else

            strPosition = Mid(strBestellung, intPosStart)

        End If

        lngArtikelID = GetText(strPosition, "ArtikelID")

        intArtikelanzahl = GetText(strPosition, "Anzahl")

        curPreis = Nz(DLookup("Preis", "tblArtikel", "ArtikelID = " & lngArtikelID), 0)

        On Error Resume Next

        db.Execute "INSERT INTO tblPositionen(BestellungID, ArtikelID, Anzahl, Preis) VALUES(" _

        & lngBestellungID & ", " & lngArtikelID & ", " & intArtikelanzahl & ", " & curPreis _

        & ")", dbFailOnError

        If Err.Number = 3201 Then

            MsgBox "Artikel mit ID '" & lngArtikelID & "' noch nicht vorhanden."

        End If

        On Error GoTo 0

        If intPosEnde > 0 Then

            intPosStart = intPosEnde

        Else

            intPosStart = 0

        End If

    Loop

End Function

Bestelldaten

================

ArtikelID: 1

Anzahl: 4

ArtikelID: 2

Anzahl: 1

ArtikelID: 3

Anzahl: 10

Eine einzelne Position setzt sich dabei zum Beispiel aus den folgenden beiden Zeilen zusammen, also aus der Artikel-ID und der Anzahl:

ArtikelID: 1

Anzahl: 4

Die Routine WritePositions sucht zunächst nach dem ersten Auftauchen von ArtikelID und ermittelt die Position des ersten Buchstabens dieses Strings innerhalb der Zeichenkette. Wurde der String gefunden, wird das Ende der ersten Artikelposition gesucht, das entweder durch ein erneutes Auftreten von ArtikelID oder das Ende der durchsuchten Zeichenkette markiert wird. In jedem Fall wird der zu einer Position gehörende Text in die Variable strPosition geschrieben.

Und hier kommt wieder die Funktion GetText zum Einsatz: Diese ermittelt zunächst den hinter dem Text ArtikelID stehenden Wert und dann die passende Anzahl für diesen Artikel. Nach dem Auslesen des zu diesem Artikel passenden Preises aus der Tabelle tblArtikel schreibt eine Execute-Anweisung den neuen Datensatz in die Tabelle tblPositionen.

Es kann nun theoretisch vorkommen, dass die Mailbestellung eine Artikel-ID aufweist, zu der noch kein Artikel in der Datenbank gespeichert ist. Da dies definitiv ein Fehler ist, erscheint eine entsprechende Meldung. Auf die gleiche Weise verfährt die Routine mit allen weiteren in der E-Mail enthaltenen Bestellpositionen.

Zusammenfassung und Ausblick

Wer Bestellungen über seine Webseite wie in diesem Beispiel per E-Mail anstatt über ein angeschlossenes Shopsystem erhält und diese auswerten muss, findet hier einige Hinweise und ein konkretes Praxisbeispiel.

Die Wahrscheinlichkeit, dass Sie genau die hier beschriebene Konstellation vorfinden, ist sehr gering. Wenn Sie es selbst in der Hand haben, also etwa das Aussehen der im E-Mail-Text übermittelten Daten selbst festlegen können, können Sie alternativ zum reinen Textformat beispielsweise mit XML arbeiten. Auf diese Weise lassen sich nicht nur die Daten besser strukturieren, sondern auch noch einfacher auslesen - mehr zu diesem Thema erfahren Sie in den Beiträgen XML-Dokumente mit VBA erstellen und lesen (Shortlink 522) sowie XML: Von Datenbank zu Datenbank (Shortlink 524).

Ein wesentlicher Punkt der hier vorgestellten Lösung ist das automatische Auswerten der eingehenden E-Mails: Glücklicherweise liefert Outlook ab Version 2000 das notwendige Ereignis, das beim Empfangen einer E-Mail ausgelöst wird, und dieses ist auch in Outlook 2007 unverändert vorhanden.

Hinweis zu den Listings

Die zum Einlesen einer E-Mail-Bestellung notwendigen Routinen finden Sie im Zusammenhang mit den Beispieldateien zu diesem Artikel. Im Artikel selbst drucken wir diese nicht komplett ab, sondern gehen auf die wichtigsten Stellen ein.

Outlook und DLookup

Damit die Access-eigene Funktion DLookup, die in Listing 2 eingesetzt wird, auch innerhalb von Outlook funktioniert, müssen Sie dem VBA-Projekt noch einen Verweis auf die Bibliothek Microsoft Access x.0 Object Library hinzufügen, wobei x für die jeweils verwendete Office-Version steht.

Kompletten Artikel lesen?

Einfach für den Newsletter anmelden, dann lesen Sie schon in einer Minute den kompletten Artikel und erhalten die Beispieldatenbanken.

E-Mail:

Download

Download

Die .zip-Datei enthält folgende Dateien:

OutlookBestellungen.zip

Beispieldateien downloaden

© 2003-2015 André Minhorst Alle Rechte vorbehalten.