Serienmails mit Excel verschicken

Wie wir bereits im ersten Teil unseres Workshops gezeigt haben, ist Visual Basic for Applications ein sehr mächtiges Werkzeug, mit dem auf relativ simple Weise die Funktionalitäten von Office erweitert und fehlende Funktionen in den Anwendungen ausgemerzt werden können (vgl. InfoWeek 07/2001). Dabei haben wir einige Beispiele für Word vorgestellt und haben bereits die Interoperabilität zwischen den verschiedenen Bestandteilen der Office-Suite kurz gestreift.

Im vorliegenden zweiten Teil wenden wir uns nun den Programmen Excel und Outlook zu. Zunächst zeigen wir mit einem kurzen Code, wie ursprünglich als Text formatierte Zellen so konvertiert werden können, dass mit darin befindlichen Zahlen gerechnet werden kann. Aufwendiger ist das Beispiel, mit dem Excel die Differenz zwischen zwei gegebenen Daten berechnet.

Unser drittes Beispiel besteht aus mehreren Einzelteilen und dient dazu, Adressen aus Outlook in Excel zu importieren, daraus personalisierte Serienbriefe oder -mails zu erstellen und diese direkt zu drucken oder zu verschicken. Schliesslich stellen wir ein Macro vor, mit dem Attachments aus E-Mails herausgeholt und in einem Ordner gespeichert werden können.

Rechnen mit Zahlen im Textformat

Die einzelnen Applikationen der Office-Suite strotzen nur so vor Features, die häufig bloss von Spezialisten benötigt werden. Dennoch haben die Programmierer von Microsoft einige Funktionen vergessen, was den Umgang mit den Anwendungen im Alltag erschweren kann - oder zumindest eine Menge Zusatzarbeit verursacht.

Eines dieser Probleme findet sich in Excel. Wenn hier in einer Tabelle ein Bereich als Text formatiert, später allerdings mit Zahlen gefüllt wurde, lassen sich mit diesen Zahlen keine Berechnungen durchführen. Das Resultat ist immer Null, weil Excel mit Text nicht rechnen kann.

Eigentlich wäre es nun ein Leichtes, den betroffenen Zellbereich über das Zell-Format-Menü wieder als Zahl oder als Standard zu formatieren. Bloss funktioniert das nicht - Excel behandelt die Zahlen nach wie vor als Text. Erst wenn der Text gelöscht und neu eingegeben wird, erkennt Excel die Zahl als solche.

Das Problem hinter diesem Verhalten ist die Excel-interne Unterscheidung zwischen dem angezeigten und gespeicherten Inhalt einer Zelle und dem tatsächlichen Wert derselben. Die in Textzellen eingetragenen Zahlen speichert das Programm als Text. Bei der Konvertierung übers Menü wird zwar der Zellenwert in Zahl geändert, die Inhalte bleiben aber Text.

Die Lösung, sämtliche Zahlen in den Textfeldern zu löschen und erneut einzugeben, ist alles andere als optimal. Abgesehen von der verlorenen Zeit ist diese Übung auch ziemlich mühsam - und gerade in solchen Situationen können Macros ihre wahren Stärken ausspielen.

Die kleine Prozedur liest über eine Schleife sämtliche markierten Zellen im geöffneten Spreadsheet ein und ändert in Zeile 5 über NumberFormat ihre Formatierungs-Eigenschaft zurück auf Standard. Darauf wird der Inhalt der Zelle, falls er als numerischer Wert erkannt wird, in eine Zahl des Gleitkommatyps Double konvertiert und wieder in die Zelle zurückgeschrieben (Zeilen 6 bis 8).

1 Sub FormatTextToNumber()
2 Dim rngCell As Range
3    For Each rngCell In Selection.Cells
4         With rngCell
5             .NumberFormat = "General"
6             If IsNumeric(.Text) Then
7                 .Value = CDbl(.Text)
8             End If
9         End With
10    Next
11 End Sub
		

Differenzberechnungen zwischen zwei Daten

Eine ähnliche Leistung, nämlich eine Berechnung unter Berücksichtigung des Formats, vollbringt unser zweites Beispiel. Das Problem besteht dabei darin, dass bei der Kalkulation beispielsweise einer Differenz zwischen zwei Daten nicht bloss deren Zahlenwerte berücksichtigt werden können: Das Resultat würde dabei unweigerlich falsch.

Statt dessen müssen die internen Formate, also des Jahres, des Monats und des Tags in die Berechnung einfliessen, was unser Macro, das sich als Funktion in Excel integriert, erledigt.

Zunächst wird die Funktion definiert und mit der Angabe der beiden Hauptvariablen dafür gesorgt, dass Excel beim Start der Funktion ein Dialogfeld zur Auswahl der entsprechenden Zellen anzeigt (Zeile 1). Nach der Definition der restlichen Variabeln wird dafür gesorgt, dass das Enddatum grösser als das Startdatum ist (Zeilen 2 bis 13). Von Zeile 14 bis Zeile 35 werden die eigentlichen Berechnungen durchgeführt. Der Rest der Funktion (Zeilen 36 bis 50) dient dazu, die Ausgabe ansprechend und fehlerfrei zu formatieren und schliesslich als Ergebnis wieder ins Spreadsheet zu schreiben (Zeile 51).

Zum Start der Funktion klickt man in Excel auf die entsprechende Schaltfläche oder aber auf das Gleich-Zeichen vor der Eingabezeile. Dann wählt man die Funktion DateDiff und im erscheinenden Fenster die Zellen mit den Datumsangaben. Ein Klick auf OK führt die Berechnung aus.

Outlook-Kontaktdaten in Excel-Tabelle einlesen

Im nächsten Projekt erstellen wir eine einfache Applikation, bei der mit Hilfe von Excel und Outlook ein sogenanntes Bulk-Mailing erstellt und versandt wird. Optional sollen die Daten aber auch dazu dienen, einen normalen Serienbrief zu kreieren und zu drucken. Als Vorgabe gilt ausserdem, dass die einzelnen Mails oder Briefe personalisiert sein sollen, also die korrekte Adresse haben und den Empfänger persönlich ansprechen.

Das Beispiel besteht aus drei grossen Teilen, die jeweils auch einzeln funktionieren und somit relativ leicht an andere Kontexte angepasst werden können - etwa, wenn die Adressdaten nicht aus Outlook kommen, sondern in Excel gespeichert sind.

Der erste Teil dient der Übernahme der Daten aus Outlook. Zunächst wird eine neue Arbeitsmappe erstellt und für den Zugriff mit VBA vorbereitet. Für den folgenden Code muss die Tabelle 1 in "Kunden", die Zelle A7 in "StartTable" und die Zelle H3 in "numberMessages" umbenannt werden. Ausserdem benötigen wir einen Button namens "Start", den wir im Entwurfsmodus mit Hilfe der Button-Leiste Steuerelemente in die Tabelle einfügen. Die restliche Einrichtung der Tabelle dient hauptsächlich der Übersicht, muss also nicht zwingend eingegeben werden.

Noch im Entwurfsmodus wechseln wir mit einem Doppelklick auf den neuen Button in den VBA-Editor, wo wir das bereits vorbereiteten Codegerüst um die Sub CommandButton1_Click ergänzen. Dabei handelt es sich um die Anweisungen, was passiert, wenn der Button angeklickt wird.

Zunächst wird die Funktion CreateAndSetObjects durchgeführt - falls diese den Wert "Falsch" zurückgibt, wird das Programm mit einer Fehlermeldung abgebrochen (Zeilen 2 bis 4). Ansonsten geht's weiter mit den Modulen GetContacts und CreateMailing.

Der Code ist intern mit der Tabelle 1 verknüpft, die den aufrufenden Button beherbergt. Der restliche Code dieses ersten Progammteils gehört dagegen in ein Modul, da er mit mindestens zwei Tabellen zusammenarbeiten muss.

Wir fügen also ein neues Modul in das Projekt ein und beginnen mit der Definition der Variablen und Konstanten (Zeilen 1 bis 12). Dann folgt die oben bereits erwähnte Funktion CreateAndSetObjects. Diese Funktion initialisiert Outlook und Excel und erstellt Applikationsobjekte, wodurch keine neuen Instanzen der Programme geöffnet werden (Zeilen 14 und 15). Darauf wird in den Zeilen 16 und 17 herausgefunden, welcher Ordner in Outlook gerade geöffnet ist und in Zeile 18 überprüft, ob es sich dabei um einen Kontakte-Ordner handelt. Ist das nicht der Fall, wird das Programm über den obigen Code abgebrochen.

Daraus folgt eine Voraussetzung für den Einsatz unseres Programms: Outlook muss gestartet und ein Kontakte-Ordner muss ausgewählt sein, ansonsten macht das Programm nichts. Bevor also eine Serienmail oder ein Serienbrief kreiert werden kann, muss man in Outlook einen neuen Ordner mit den gewünschten Kontakten erstellen und diesen auswählen.

Aus diesem Ordner werden mit der GetContacts-Prozedur nun die Adressen ausgelesen und in Excel geschrieben. Das Vorgehen ist relativ simpel. Zunächst wird dafür gesorgt, dass die folgenden Vorgänge in der Kunden-Tabelle stattfinden, und die aktuelle Zelle definiert wird - dabei handelt es sich um diejenige Zelle, die sich unter der zuvor in der Tabelle als "startTable" benannten Zelle befindet (Zeile 29). Danach durchläuft eine Schleife sämtliche Kontakte im ausgewählten Outlook-Ordner, überprüft, ob der Kontakt über eine E-Mailadresse verfügt und schreibt dann die Daten in die zugehörigen Zellen. Dabei wird immer von der oben definierten aktuellen Zelle ausgegangen; der tatsächliche Speicherort ist durch die Angaben in der Offset-Eigenschaft zweifelsfrei definiert (Zeilen 30 bis 45).

In Zeile 39 findet sich bereits ein Teil unserer Personalisierungsroutine. Hier wird die Variable strPers mit dem Ergebnis der Funktion CustomizeMailing gefüllt und darauf ebenfalls in die Tabelle geschrieben.

Sind alle Kontakte abgearbeitet und in die Tabelle geschrieben, löscht das Programm allfällige Adressen, die sich von früheren Mailings noch im Sheet befinden (Zeilen 46 und 47) und schreibt schliesslich die Anzahl übernommener Adressen in die mit "numberMessages" benannte Zelle des Sheets.

Mit der Funktion CustomizeMailing, die im selben Modul wie GetContacts untergebracht wird, wird die Anrede definiert. Dafür greift die Funktion auf das entsprechende Feld in den Outlook-Kontakten zurück und generiert daraus und aus dem Nachnamen die passende Anrede. Natürlich lässt sich dies problemlos anpassen, indem etwa "Sehr geehrter Herr " durch "Lieber " und der Nachname durch den Vornamen ersetzt werden. Dabei muss bloss berücksichtigt werden, dass mit der Zeile 39 im Code die richtigen Variabeln übergeben werden, also beispielsweise objContact.FirstName statt objContact.LastName.

Erhält CustomizeMailing aus dem entsprechenden Outlook-Feld keine oder eine unpassende Anrede (beispielsweise Firma), wird als Ergebnis der Funktion "Sehr geehrte Damen und Herren" zurückgegeben. Es sollte demnach für alle Kontakte das Feld entsprechend definiert werden.

Serienbriefe erstellen und personalisieren

Die mit dem vorigen Programmteil in Excel eingelesenen Daten werden nun genutzt, um einen personalisierten Serienbrief zu erstellen. Das geht zwar auch mit Word, bietet aber mit Excel einige Vorteile. So kann man etwa das vorliegende Beispiel in der Weise erweitern, dass in einer separaten Spalte (oder auch Tabelle) etwa offene Forderungen oder persönliche Anmerkungen zum Standardbrief aufgelistet werden. Diese lassen sich sodann problemlos mit einer kleinen Erweiterung in der Routine CreateMailing in den Standardbrief einfügen. Dieser Standardbrief lässt sich natürlich auch nach Belieben formatieren.

Auch für diesen Programmteil bedarf es einiger Vorbereitung. So muss eine Tabelle in "Brief" umbenannt werden, damit die Daten eingetragen werden können. Diese Tabelle enthält auch die Briefvorlage.

Des weiteren müssen innerhalb der Tabelle "Brief" einige Zellen umbenannt werden, damit das VBA-Programm weiss, wo es die Daten einsetzen soll. In unserem Beispiel handelt es sich dabei um diejenigen Zellen, die die Adresse des Empfängers und die Anrede enthalten sollen - sie werden je einzeln in "Firma", "Name", "Strasse", "Ort" und "Anrede" umgetauft.

Das Programm selber benutzt wiederum eine Schleife, um die Daten aus dem Kunden-Sheet auszulesen und in die Brief-Tabelle einzufügen. Gestartet wird in Zelle 8 der Kunden-Tabelle, und der Vorgang läuft, solange die Adresseliste noch Adressen enthält (Zeilen 65 und 66). Nun werden die Daten im Kunden-Sheet entsprechend ihres Index (Zeile und Spalte) ausgelesen und im entsprechenden Zellbereich der Brief-Tabelle eingetragen.

Im Beispiel wird der fertige Brief an die Druckvorschau übergeben, was den Vorteil hat, dass bei Testläufen nicht ständig gedruckt wird (Zeile 72). Ist das Programm fertig und genügend getestet, kann man diese Zeile auskommentieren und statt dessen die Zeile 73 verwenden - die Briefe gehen dann direkt auf den Drucker.

Serienmails erstellen

Ein wenig aufwendiger ist die Erstellung von Serienmails, die von Excel erstellt und von Outlook verschickt werden. Immerhin können dazu die oben eingelesenen Kontakte problemlos weiterverwendet werden.

Damit das Beispiel funktioniert, muss auch hier etwas vorbereitet werden, und zwar die Vorlage für die Mail. Dazu erstellt man in Outlook eine neue Mail mit Betreff und Text, aber ohne Anrede, die man als Outlook-Template-Datei mit der Endung .oft an einem beliebigen Ort auf der Festplatte speichert.

Ausserdem benötigen wir einen zweiten Button im Kunden-Sheet, um damit die Serienmailfunktion aufzurufen. Der zugehörige Code kommt denn auch in das Codefenster der Tabelle 1 (Kunden).

Wie schon im obigen Beispiel überprüft der Button zunächst, ob ein Outlook-Kontakteordner ausgewählt ist, und startet dann die weiteren Macros - zuerst wieder das bereits bekannte GetContacts für die Adressübernahme in Excel und dann CreateMailSend, das die Mails erstellt und verschickt.

In der Prozedur CreateMailSend, die ins Modul integriert wird, werden zunächst wieder die benötigten Variabeln definiert und darauf mit einer InputBox der Pfad zur vorbereiteten Mail-Vorlage verlangt (Zeile 86). Daraufhin werden mit der aus CreateMailing bekannten Weise in einer Schleife die Daten verarbeitet: In einem ersten Schritt erstellt das Programm aus dem Template eine neue Message, lädt dann aus dem Kunden-Sheet die Mail-Adresse und fügt diese in die Empfängerzeile der Nachricht ein (Zeilen 90 bis 92). Darauf wird die Anrede aus der Excel-Tabelle in eine Variable eingelesen und mit dem Text aus dem Template kombiniert (Zeilen 93 bis 96). Schliesslich wird die Mail in Zeile 97 im Entwürfe-Ordner von Outlook gespeichert - was wiederum vor allem während des Testens hilfreich ist. Soll die Nachricht direkt verschickt werden, kommt die Zeile 98 zum Zug und Zeile 97 wird auskommentiert.

Damit ist die VBA-Applikation für den Versand von personalisierten Mails und Serienbriefen komplett. Wie bereits erwähnt, müssen für eine sinnvolle Anwendung des Programms einige Voraussetzungen erfüllt sein - neben der Erstellung der Templates für Mail und Brief gehört dazu insbesondere die Vorbereitung des Kontakte-Ordners: Damit die Personalisierung funktioniert, müssen die Kontakte zwingend über ein ausgefülltes Outlook-Feld "Anrede" verfügen.

Mail-Attachments in Outlook automatisch speichern

Unser letztes Beispiel ist eine reine Outlook-Anwendung, die Anhänge von ankommenden Mails speichert und in der Originalmail löscht. Damit dient das Tool zwei Zwecken:

Einerseits sinkt damit das Risiko einer Weiterverbreitung von Mail-Viren, weil der Aufwand für das Anklicken des Anhangs steigt. Ausserdem können die Anhänge im separaten Ordner besonders einfach auf Viren untersucht werden.

Andererseits wird auf diese Weise die Performance von Outlook optimiert, weil die pst-Datei, die sämtliche Objekte des Clients beinhaltet, nicht ins Unermessliche wächst.

Wichtig an dieser Prozedur ist zunächst mal ihr Name: Das Programm muss Application_NewMail heissen, damit es bei jedem Eintreffen einer Nachricht automatisch ausgeführt wird. Nach der Definition der Variablen nutzt das Tool wieder eine Schleife, um die neue Mail nach Attachments zu durchsuchen und diese zu zählen. Nur wenn eine Mail ungelesen ist und mindestens einen Anhang enthält (Zeile 13), wird die Prozedur überhaupt ausgeführt.

Ist das der Fall, wird im Ordner Eigene Dateien nach einem Ordner mit dem aktuellen Datum gesucht und erstellt, falls er noch nicht vorhanden wäre (Zeilen 14 bis 17). Anschliessend wird die Nachricht geöffnet, jedes Attachment einzeln separiert und in den
neuen Ordner gespeichert (Zeilen 18 bis 21).

Schliesslich erzeugt das Programm im Body der Message einen Verweis auf den Speicherort, damit dieses leicht wieder gefunden werden kann, löscht das Attachment aus der Mail und schliesst diese wieder (Zeilen 22 bis 25). Die gemachten Änderungen werden dabei natürlich gespeichert.

So sind nun alle Dateianhänge unabhängig von ihrem Format in einem einzigen Ordner mit dem aktuellen Datum gespeichert.