Jeder, der seine Kunden und Bestellungen in einer Access-Datenbank verwaltet, hegt früher oder später den Wunsch, Rechnungen und sonstigen kundenbezogenen Schriftverkehr automatisiert erledigen zu lassen. Klassischerweise hält dafür ein Bericht her, der ausgedruckt, eingetütet und mit der Schneckenpost verschickt wird. Manch einer schickt die Daten auch zuvor noch nach Word, um diese von der Sachbearbeiterin nachbearbeiten zu lassen.
In letzter Zeit löst aber die E-Mail den Versand per Post ab: Dabei wird die Rechnung im PDF-Format gespeichert, an die E-Mail angehängt und mit dieser verschickt. Damit entfallen sowohl einige Kosten fürs Material als auch der Zeitaufwand für das Ausdrucken und Eintüten, geschweige denn die Verzögerung durch die postalische Zustellung.
Damit Sie wirklich von dieser gewonnenen Zeit profitieren können, müssen die notwendigen Vorgänge natürlich vollautomatisch ablaufen. Das ist kein Problem: Beiträge aus vergangenen Ausgaben von Access im Unternehmen liefern die Basis für das Programmieren einer Anwendung, die Ihre Anschreiben vollautomatisch an die Kunden versendet.
Das Erstellen von PDF-Dokumenten aus Berichten beschreibt der Beitrag Vom Bericht zum PDF-Dokument (Shortlink 430). Dort kommt eine von Stephen Lebans bereitgestellte DLL, die nicht registriert werden muss, zum Einsatz.
Das Versenden von E-Mails erläutert der Beitrag Mail versenden mit SMTP und VBA (Shortlink 390). Auch dieser Beitrag stellt eine DLL vor, die Sie kostenlos einsetzen können.
Die Techniken sind also vorhanden, fehlt nur noch das Rahmenwerk: Eine Anwendung, die massenhaft Dokumente für unterschiedliche Empfänger liefert, ist beispielsweise die Südsturm-Datenbank (eine geänderte Fassung der Nordwind-Datenbank, siehe Shortlink 411). Sie enthält neben den Tabellen einen Rechnungsbericht, der dem aus der Original-Nordwind-Datenbank entspricht, aber den Tabellen der Südwind-Datenbank angepasst ist (s. Abb. 1).
Abb. 1: Der Rechnungen-Bericht aus der Nordwind-Datenbank hält als Beispiel für den Rechnungsversand im PDF-Format per E-Mail her.
Voraussetzung: E-Mail-Adresse
Damit Sie überhaupt serienweise Rechnungen per E-Mail versenden können, brauchen Sie die passenden E-Mail-Adressen. Diese sollten zusammen mit den übrigen Kundendaten in einem separaten Feld der Kundentabelle untergebracht sein. Im Beispiel enthält so die Tabelle tblKunden ein weiteres Feld namens EMail.
Zusätzliche Felder
Wer Rechnungen und sonstige Korrespondenz per E-Mail verschickt, sollte dies genau so festhalten wie den Versand von Waren oder Briefen. Daher enthält die Tabelle tblBestellungen ein zusätzliches Feld mit dem Namen Rechnungsdatum (s. Abb. 2).
Abb. 2: Die Tabelle tblBestellungen enthält ein zusätzliches Feld, das den Zeitpunkt des Rechnungsversands speichert.
Außerdem fügen Sie der Beispieldatenbank eine weitere Tabelle namens tblRechnungVersenden hinzu. Die Tabelle enthält lediglich zwei Felder: Das Primärschlüsselfeld dient der Verknüpfung mit einem Datensatz der Tabelle tblBestellungen, das zweite ist ein Ja/Nein-Feld, das angibt, ob die Rechnung zur aktuellen Bestellung versendet werden soll. Da diese Angabe nur temporär ist, soll sie in einer separaten Tabelle gespeichert werden und die Anzahl der Felder der Tabelle tblBestellungen nicht unnötig erhöhen. Die Verknüpfung der beiden Tabellen entnehmen Sie Abb. 3.
Abb. 3: Die zusätzliche Tabelle tblRechnungVersenden liefert ein Feld zum Markieren der zu versendenden Rechnungen.
Rechnungen für den Versand auswählen
Bevor es ans Eingemachte geht, stellen Sie zunächst noch ein Formular zusammen, mit dem der Anwender auswählen kann, welche Rechnungen er versenden möchte. Dazu verwenden Sie ein Unterformular, das die Rechnungen aus der Abfrage aus Abb. 4 anzeigt. Um das Unterformular übersichtlich zu halten, soll es nur die Felder BestellungID, Bestelldatum, Rechnungsdatum und das Feld Versenden der Tabelle tblRechnungVersenden enthalten.
Abb. 4: Diese Abfrage liefert die Daten für die Auswahl der zu versendenden Rechnungen im Unterformular sfmRechnungen.
Das Unterformular selbst betten Sie in ein Hauptformular ein, das die Möglichkeit bietet, nach noch nicht verschickten Rechnungen zu fahnden oder die aktuell zu verschickenden Rechnungen zu markieren (s. Abb. 5).
Abb. 5: Die Auswahl der zu versendenden Rechnungen erfolgt im Formular
frmRechnungen per Klick auf das entsprechende Kontrollkästchen.
Technische Voraussetzungen
Wie bereits erwähnt, brauchen Sie einige DLLs für den Betrieb dieser Lösung.
Die vbSendMail.dll [1] müssen Sie registrieren. Dies erledigen Sie ganz einfach mit folgender Anweisung, abgesetzt im Ausführen-Dialog oder in der Eingabeaufforderung:
RegSvr32.exe <Pfad>\vbSendMail.dll
Nutzer von Windows Vista müssen über die entsprechenden Berechtigungen verfügen und installieren DLLs mit demselben Befehl, allerdings in der Administrator-Variante der Eingabeaufforderung, die man mit dem Kontextmenüeintrag Als Administrator ausführen des Menüpunkts Alle Programme/Zubehör/Eingabeaufforderung öffnet.
Für den Einsatz der Bibliothek benötigen Sie einen Verweis auf SMTP Send Mail for VB6 in der Datenbank.
Die beiden für das Erzeugen des PDFs aus einem Bericht erforderlichen DLLs [2] brauchen Sie nicht zu installieren, sondern nur im Verzeichnis der Datenbankanwendung oder im Windows-Systemverzeichnis zu speichern.
Dafür müssen Sie aber die beiden Module modReportToPDF und clsCommonDialog aus der Beispieldatenbank A2000SnapshotToPDFver751.mdb [2] importieren - darin ist unter anderem die Funktion zum Erzeugen eines PDF-Dokuments aus einem Bericht enthalten.
E-Mail-Daten
Für das Versenden von E-Mails mit der vbSendMail-Bibliothek brauchen Sie ein paar Informationen mehr, als wenn Sie dies mit Outlook erledigen. Dafür sind Sie aber auch vor lästigen Sicherheitsrückfragen gefeit.
Zu diesen Informationen gehören unter anderem die Daten des Servers, der die E-Mails verschicken soll, die Absenderinformationen, der Text des Betreffs und der eigentliche Inhalt der E-Mail.
Da sich diese Daten möglicherweise mit der Zeit ändern oder Sie verschiedene E-Mail-Vorlagen erstellen möchten, speichern Sie je einen Satz dieser Daten in einer Tabelle namens tblEMails, die mit einer weiteren Tabelle namens tblAuthentifizierungsdaten verknüpft ist. Letztere enthält die unterschiedlichen Authentifizierungsmethoden.
Diese Tabelle sieht im Beziehungen-Fenster so wie in Abb. 6 aus.
Abb. 6: Diese beiden Tabellen speichern die Vorlagen für verschiedene E-Mails. Auf diese Weise ist das Wechseln etwa des Servers oder einer Absenderadresse kein Problem.
Die Steuerungszentrale
Das Formular frmRechnungsversand dient nicht nur der Anzeige der in den beiden Tabellen tblEMails und tblAuthentifizierungsarten gespeicherten Daten, sondern auch als Steuerungszentrale für den E-Mail-Versand (s. Abb. 7).
Abb. 7:
Das Formular frmRechnungsversand steuert den Versand der Rechnungen und bietet die Möglichkeit, das Formular frmRechnungen zum Auswählen der zu versendenden Rechnungen anzuzeigen.
Interessant sind die Funktionen dieses Formulars: Während die Felder im oberen Bereich entsprechend den vorliegenden Serverdaten gefüllt werden müssen, können Sie bei den beiden unteren Feldern Betreff und Inhalt mit Platzhaltern arbeiten. Die enthaltene Technik lässt sich am besten im Zusammenhang erläutern:
Zunächst trägt der Benutzer die für den Versand wichtigen Informationen in die dafür vorgesehenen Felder ein.
Dann erfolgt die Auswahl der Bestellungen, für die eine Rechnung verschickt werden soll. Diese Auswahl erfolgt im Formular frmRechnungen, das der Benutzer durch einen Mausklick auf die Schaltfläche cmdEmpfaengerAuswaehlen öffnet. Dies löst die folgende Ereignisprozedur aus:
Private Sub cmdEmpfaengerAuswaehlen_Click()
DoCmd.OpenForm "frmRechnungen", _
WindowMode:=acDialog
Me!txtAnzahlEmpfaenger = _
DCount("BestellungID", _
"qryRechnungenAuswahl", _
"Rechnungsdatum IS NULL AND " _
& "Versenden = True")
End Sub
Die Routine öffnet das Formular frmRechnungen als modalen Dialog und aktualisiert anschließend den Inhalt des ungebundenen Textfelds txtAnzahlEmpfaenger - somit gewinnt der Benutzer einen Eindruck vom Umfang des anstehenden Mailversands.
Der anschließende Klick auf die Schaltfläche Senden löst nun die Routine aus Listing 1 aus. Diese erledigt nun zusammengefasst die folgenden Schritte:
- Deklaration der Variablen
- Erzeugen einer Instanz von vbSendMail.clsSendmail
- Einstellen der grundlegenden E-Mail-Parameter
- Erzeugen einer Datensatzgruppe, die alle Datensätze der Abfrage qryRechnungen enthält, zu denen noch keine Rechnung versendet wurde (Rechnungsdatum Is Null) und die im Formular frmRechnungen für den Versand markiert wurden (Versenden = True).
- Die Datensätze dieses Recordsets enthalten alle für das Zusammenstellen des Berichts und für die in der E-Mail anzuzeigenden Rechnungsinformationen notwendigen Daten.
- Nun durchläuft die Routine die Datensätze für alle zu versendenden Rechnungen und erzeugt dabei ein passendes PDF-Dokument, das es in einem Verzeichnis unterhalb des Verzeichnisses der Datenbank speichert. Dieses hängt sie an das E-Mail-Objekt an und trägt die weiteren, auf den Kunden der aktuellen Bestellung zugeschnittenen persönlichen Daten ein. Schließlich versendet die Routine die E-Mail.
Listing 1: Das Versenden von Rechnungen im PDF-Format
Private Sub cmdSenden_Click()
DoCmd.Hourglass True
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim intGesendet As Integer
Dim intNichtGesendet As Integer
Dim intRechnungen As Integer
Dim strOrdner As String
Dim strAttachment As String
DoCmd.RunCommand acCmdSaveRecord
Set objMail = New vbSendMail.clsSendMail
Set db = CurrentDb
objMail.SMTPHost = Me!SMTPServer
objMail.SMTPPort = Me.SMTPPort
objMail.Connect
Set rst = db.OpenRecordset("SELECT DISTINCT * FROM qryRechnungenAuswahl WHERE Rechnungsdatum IS NULL " _
& "AND Versenden = True", dbOpenDynaset)
If Not rst.EOF Then
rst.MoveLast
rst.MoveFirst
intRechnungen = rst.RecordCount
strOrdner = "Rechnungsversand"
On Error Resume Next
MkDir CurrentProject.path & "\" & strOrdner
On Error GoTo 0
Do While Not rst.EOF
RechnungErstellen rst!BestellungID, strOrdner
With objMail
.EncodeType = MIME_ENCODE
.ContentBase = .GetContentType(CurrentProject.path & "\" & strOrdner & "\Abrechnung" _
& rst!BestellungID & ".pdf")
strAttachment = CurrentProject.path & "\" & strOrdner & "\Rechnung_" & rst!BestellungID & ".pdf"
.Attachment = strAttachment
.from = Me.Von
If Len(Me.KopieAn) > 0 Then
.BccRecipient = Me.KopieAn
End If
.Subject = PlatzhalterErsetzen(Me.Betreff, "qryRechnungen", rst!BestellungID)
.Recipient = rst("EMail")
.AsHTML = False
.Message = PlatzhalterErsetzen(Me.Inhalt, "qryRechnungen", rst!BestellungID)
Select Case Me.Authentifizierung
Case 2
.UsePopAuthentication = True
.UserName = Me.Benutzername
.Password = Nz(Me.Kennwort, "")
.POP3Host = Me.POP3Server
Case 3
.UseAuthentication = True
.UserName = Nz(Me.Benutzername, "")
.Password = Nz(Me.Kennwort, "")
End Select
.send
If bolGesendet = True Then
intGesendet = intGesendet + 1
Else
intNichtGesendet = intNichtGesendet + 1
End If
End With
SysCmd acSysCmdSetStatus, rst.AbsolutePosition + 1 & " von " & intKontakte & " versendet."
rst.MoveNext
Loop
End If
objMail.Disconnect
MsgBox "Statistik:" & vbCrLf & "Anzahl erfolgreich versendeter E-Mails: "
& intGesendet & vbCrLf & "Anzahl nicht versendeter E-Mails: " & intNichtGesendet
rst.Close
Set objMail = Nothing
Set rst = Nothing
Set db = Nothing
DoCmd.Hourglass False
End Sub
Personalisierung
Während die Rechnungsdatensätze durchlaufen und die E-Mails zusammengestellt und versendet werden, findet eine Personalisierung der E-Mails statt. Hauptbestandteil ist natürlich die auf den aktuellen Kunden abgestimmte Rechnung im PDF-Format - dazu später mehr. Aber auch die E-Mail selbst soll den Kunden persönlich ansprechen, und zwar nicht nur über die ohnehin notwendige E-Mail-Adresse.
Dazu greift sich die Routine den im Formular angegebenen Betreff und den Inhalt der Mail heraus, die mit Platzhaltern wie [Bestelldatum] und [Kontaktperson] versehen sind.
Für den Einsatz dieser Platzhalter muss eine einzige Regel eingehalten werden: Die in der Routine durchlaufene Datensatzgruppe muss für jeden Platzhalter ein gleichnamiges Feld enthalten - natürlich ohne die eckigen Klammern, die nur zum Identifizieren der Platzhalter dienen.
Genau das ist hier der Fall - zum besseren Verständnis hier noch einmal der SQL-Ausdruck der Abfrage:
SELECT BestellungID, Bestelldatum, Rechnungsdatum, Firma, Versenden, Kontaktperson, EMail FROM tblKunden INNER JOIN ...
Hier sind sowohl das Feld Bestelldatum als auch Kontaktperson enthalten. Die Platzhalter müssen nun bei jedem Durchlauf, also für jeden Rechnungsdatensatz, durch die persönlichen Werte ersetzt werden. Dies geschieht in den beiden folgenden Zeilen der Routine aus Listing 1:
.Subject = PlatzhalterErsetzen(Me!Betreff, "qryRechnungen", rst!BestellungID)
.Message = PlatzhalterErsetzen(Me!Inhalt, "qryRechnungen", rst!BestellungID)
Diese beiden Zeilen rufen die Funktion PlatzhalterErsetzen auf, die Sie in Listing 2 finden. Die Routine erwartet den Originaltext mit den Platzhaltern, den Namen der Tabelle oder der Abfrage, aus der die Ersetzungstexte stammen, und den Wert des Primärschlüsselfeldes für den betroffenen Datensatz.
Listing 2: Ersetzen von Platzhaltern im Mail-Betreff und Mail-Text
Private Function PlatzhalterErsetzen(strText As String, strTabellenname As String, lngID As Long) As String
Dim strTemp As String
Dim intPosStart As Integer
Dim intPosEnde As Integer
Dim strPlatzhalter() As String
Dim i As Integer
strTemp = strText
intPosStart = InStr(1, strTemp, "[")
Do While intPosStart > 0
intPosEnde = InStr(intPosStart, strTemp, "]")
ReDim Preserve strPlatzhalter(i + 1)
strPlatzhalter(i) = Mid(strTemp, intPosStart + 1, intPosEnde - intPosStart - 1)
i = i + 1
intPosStart = InStr(intPosEnde, strTemp, "[")
Loop
For i = LBound(strPlatzhalter) To UBound(strPlatzhalter) - 1
strTemp = Replace(strTemp, "[" & strPlatzhalter(i) & "]", _
DLookup(strPlatzhalter(i), strTabellenname, "BestellungID = " & lngID))
Next i
PlatzhalterErsetzen = strTemp
End Function
Man könnte diese Funktion noch etwas allgemeiner gestalten und damit für weitere Anwendungszwecke verfügbar machen; diese scheinen jedoch nicht allzu weit gesät, sodass diese spezielle Variante praktischer erscheint.
Die Routine durchsucht erst den Text nach Paaren eckiger Klammern und speichert die darin enthaltenen Texte in einem String-Array namens strPlatzhalter.
Die Routine enthält aus Gründen der Übersicht keine Fehlerbehandlung, sodass eine falsche Anzahl öffnender oder schließender Klammern zu einem Fehler führen könnte.
Anschließend durchläuft die Routine die gesammelten Platzhalter und ersetzt diese durch den im passenden Feld des angegebenen Datensatzes vorhandenen Ausdruck. Der Rückgabewert der Funktion entspricht dem Eingangstext mit ersetzten Platzhaltern.
PDF bauen
Den Bau der an die Mail anzuhängenden Berichte im PDF-Format übernimmt eine weitere Funktion namens RechnungErstellen (s. Listing 3). Diese Routine erwartet ebenfalls den Wert des Primärschlüssels, um die richtige Rechnung zu erzeugen ,sowie den Pfad unterhalb des Datenbankverzeichnisses, in den sie die fertigen PDF-Dateien schreiben soll.
Listing 3: Erstellen der PDF-Version des Rechnungsberichts
Private Function RechnungErstellen(lngID As Long, strOrdner As String) As Boolean
gstrReportFilter = "BestellungID = " & lngID
If ConvertReportToPDF("repRechnung", vbNullString, CurrentProject.Path & "\" & strOrdner & "\Rechnung_" _
& lngID & ".pdf", False, False) = False Then
RechnungErstellen = False
End If
End Function
Die Routine enthält prinzipiell nur zwei Anweisungen, die es aber in sich haben: Die erste stellt die globale Variable gstrReportFilter auf den Wert "BestellungID = " & lngID ein. Dies ist wichtig, weil die Funktion zum Umwandeln des Berichts keine direkte Möglichkeit zum Übergeben von Parametern wie etwa die DoCmd.OpenReport-Methode liefert. Daher speichern Sie das einzige Filterkriterium, nämlich die ID der Bestellung, in einer Form, in der sich der Bericht diese mit einer passenden Methode beim Öffnen selbst holen kann. Ein kleiner Ausflug zum Zielbericht repRechnung zeigt, wie das funktioniert. Für das Ereignis Beim Öffnen dieses Berichts legen Sie nämlich die folgende kleine Ereignisprozedur an:
Private Sub Report_Open(Cancel As Integer)
If gstrReportFilter <> vbNullString Then
Me.Filter = gstrReportFilter
Me.FilterOn = True
gstrReportFilter = vbNullString
End If
End Sub
Auf diese Weise stellt sich der Bericht bei jedem Öffnen selbst auf den in gstrReportFilter angegebenen Filter ein. Dies macht sich die Funktion ConvertToPDF zu Nutze, die ein PDF-Dokument auf Basis des Berichts erstellt (s. Abb. 9). Da auch diese Funktion den Bericht öffnen muss, um das PDF daraus erstellen zu können, findet auch hier die Filterung nach dem in gstrReportFilter angegebenen Ausdruck statt.
Abb. 9: Der Bericht mit der
Rechnung wurde 1:1 in das
PDF-Dokument übertragen.
Die Funktion ConvertToPDF erwartet neben dem Namen des Quellberichts noch den Namen der zu erstellenden PDF-Datei inklusive Pfad, der mit einfachen Textoperationen zusammengesetzt wird.
Die Funktion finden Sie übrigens in einem der beiden aus der Beispieldatenbank aus [2] importierten Module.
PDF anhängen
Nach dem Erstellen des PDF hängt die Routine dieses an die Mail an. Dazu dienen die folgenden beiden Zeilen:
strAttachment = _
CurrentProject.path & "\" _
& strOrdner & "\Rechnung_" _
& rst!BestellungID & ".pdf"
.Attachment = strAttachment
Die erste stellt den Dateinamen der PDF-Datei zusammen, die zweite fügt den so erzeugten String der Eigenschaft Attachment des Mail-Objekts hinzu (Ergebnis s. Abb. 10).
Abb. 10: Eine mit der Lösung dieses Artikels verschickte E-Mail samt Anhang
Weitere Informationen über das Versenden von Serienmails mit der hier verwendeten Bibliothek finden Sie in den Beiträgen Serienmails mit Access (Shortlink 380) und Mails versenden mit SMTP und VBA (Shortlink 390).
Sehr wichtig ist auch das Notieren des Rechnungsversands in der Tabelle tblBestellungen: Dort soll das Feld Rechnungsdatum für die versendeten Rechnungen auf das aktuelle Datum eingestellt werden. Dies übernimmt die folgende Anweisung (in einer Zeile):
db.Execute "UPDATE tblBestellungen SET
Rechnungsdatum = " & SQLDatum(Date) & " WHERE BestellungID = " & rst!BestellungID
Zusammenfassung und Ausblick
Die hier vorgestellte Beispiellösung können Sie auf beliebige Anwendungen übertragen, die personalisierte E-Mails mit PDF-Versionen eines oder mehrerer Berichte enthalten sollen.
Dabei gibt es beliebige Erweiterungsoptionen. Die erste und wichtigste ist eine Fehlerbehandlung, die hier aus Gründen der Übersichtlichkeit weggelassen wurde.
Für viele Zwecke ist außerdem die lückenlose Dokumentation der versendeten E-Mails und der anhängenden Dokumente wichtig. Ein wichtiger Punkt ist hierbei, dass Sie eine Kopie jeder E-Mail an sich selbst schicken. Dabei können Sie beispielsweise eine separate E-Mail-Adresse wählen, deren Mails direkt in den Gesendete Objekte-Ordner Ihres E-Mail-Clients landen.
Die PDF-Dateien werden in einem Verzeichnis unterhalb des Datenbankverzeichnisses gespeichert. Sie können natürlich ein OLE-Feld in der Tabelle tblBestellungen anlegen und die PDF-Dokumente darin speichern. Techniken zum Speichern binärer Dateien in OLE-Feldern finden Sie beispielsweise im Beitrag Komprimierte Binärspeicherung (Shortlink 466).
Zur Verwendung weiterer Platzhalter fügen Sie einfach die entsprechenden Felder zu der Abfrage hinzu, die als Basis für das in der beim Senden ausgelösten Routine durchlaufene Recordset dient. Fügen Sie außerdem die passenden Platzhalter in den Betreff und den Inhalt der E-Mail ein.
Quellen
[1] Download vbSendmail.dll: http://www.freevbcode.com/ShowCode.Asp?ID=109
[2] Download ReportToPDF: http://www.lebans.com/reporttopdf.htm
