Guten Morgen zusammen,
hallo knobbi38,
in Anlehnung an meinen letzten Post (https://www.access-o-mania.de/forum/index.php?topic=27603.0) möchte ich einen automatisierten, individuellen Mailversand mit individuellem Dateianhang generieren. Hierbei soll nun als Quelle für die entscheidenden Kriterien die folgende Abfrage dienen, die quasi den Wert "[BSTWert]" enthält:
SELECT [Stunden_ALLE].BAUSTELLE
FROM [Stunden_ALLE]
GROUP BY [Stunden_ALLE].BAUSTELLE;
Es sollen also nur Mails für Baustellen ([BSTWert]) erstellt werden, die sich auch in der Abfrage wiederfinden. So wie die zur BST zugehörigen Mail-Adressen abgefragt werden, soll nun auch die entsprechende Nummer der Baustelle [BSTWert] ermittelt werden und statt der statischen BST-Nummer nun individuell in einen Schleifen-Code eingebaut werden.
Bisher habe ich den Mailversand umständlich durch einen statisch VBA-Code für jedes Kriterium nach dem folgenden Schema wie folgt gelöst:
Private Function GetLeistungserfassungMailTo() As String
Dim MailTo As String
MailTo = DLookup("[Mailadressen]", "@BST-Mailing", "[BST]=Form![BSTWert]")
GetLeistungserfassungMailTo = MailTo
End Function
Private Sub Bezeichnungsfeld24_Click()
Me.[BSTWert] = 1000
Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)
With objMail
.To = GetLeistungserfassungMailTo
.Subject = "1000 - Leistungserfassung Sub " & Forms!Start!Text2681 & " - gemäß Export vom: " & Forms!Start!DateiZeitpunkt
.HTMLBody = "<HTML><font size=3> Hallo und guten Tag, ...</BODY></HTML>"
.Attachments.Add "C:\Exporte\Stunden - 1000.xlsm"
.Display
End With
End Sub
Danke für Deine Antwort im Voraus.
Tom
Hallo Tom,
es ist sehr schwer für Außenstehende, deinen Ausführungen zu folgen und zu erahnen, was das eigentliche Problem ist.
Du solltest vielleicht etwas strukturierter vorgehen und dein Problem in mehrere Teilschritte/-aufgaben aufteilen. Ein Nassi-Shneiderman (https://de.wikipedia.org/wiki/Nassi-Shneiderman-Diagramm) Flussdiagramm sollte dir dabei helfen. Für die einzelnen Teilschritte werden dann Prozeduren und Funktionen verwendet, denen die entsprechenden Argumente übergeben werden.
Ein Teilschritt könnte z.B. das erstellen und versenden einer Mail sein. Da Prozeduren und Funktionen i.d.R. nicht auf Werte außerhalb ihres Scopes zugreifen sollten, verbieten sich solche Dinge wie
Me.[BSTWert] = 1000
oder
Subject = "1000 - Leistungserfassung Sub " & Forms!Start!Text2681 & " - gemäß Export vom: " & Forms!Start!DateiZeitpunkt
,
sondern diese Werte werden als Argumente an die Sub übergeben:
Public Sub SendMail( _
Byref olApp as Outlook.Application, byval Recipient as String,
Byval Subject as String, Body as String,
Optional byval AttachmentFilename as String = "")
...
End Sub
Das Ganze wird in einer Schleife durchgeführt, die über die Ergebnismenge der Abfrage iteriert. Um diese Schleife nicht zu unübersichtlich zu mache, würde man noch einen Zwischenschritt einfügen, welcher die Argumente für den Aufruf der Sub SendMail aufbereitet, z.B. den Filenamen für die Anlage aufbereitet, Mailtexte generieren und dann SendMail aufrufen.
Das wäre ein Einstieg, wo man bei den Teilschritten dann auch helfen könnte, anders wird es etwas schwierig.
Gruß
Knobbi38
Hallo Ulrich,
danke für Deine schnelle Antwort.
Natürlich hast Du mit dem, wie von Dir zuvor beschrieben Recht, jedoch fällt es mir als Laie "schwer" zu beschreiben, so dass es jemand mit Deinem großen Wissen, Verständnis und Programmier-Logik versteht. Und das was Du als Teil-Code vorgibst und beschreibst, erschliesst sich meiner laienhaften VBA-Kenntnis ebenso schwer bis gar nicht, da ich die ganze Syntax nicht kenne. :(
Ja es sollen aus der Grundabfrage die Baustellen-Nummer und die dazugehörige Mailadresse als Argumente in der Schleife übergeben und zusätzlich soll die jeweilige Baustellen-Nummer als Wert in .Subject sowie in den zu erzeugenden Link unter .Attachments.Add eingefügt werden.
Zudem ist die jeweils passende Mail-Adresse in der Tabelle "BST-Mailing" im Feld "Mailadressen" zu finden, in der die Baustellen-Nummer im Feld "BST" steht.
Das "Problem" ist auch, das der "BSTWert" selbst im Formular nicht steht, weil mit dem Laden der Daten einer ganzen Firma immer Daten von bis zu 20 verschiedenen Baustellen vorhanden sind. Genau aus dem Grund hatte ich für jede mögliche Baustelle einen eigenen VBA-Code erstellt, der "hart" die Baustellen-Nummer beinhaltet.
Da ich genau das nun nicht mehr möchte war mein Gedanke, mit dem Finden der Baustellen-Nummer in der Grundabfrage sowie dem Abgleich zum Finden der dazugehörigen Mail-Adresse, die jeweiligen Mails zu versenden.
Hallo Tom,
ok, dann fängst du einfach mal von vorne an.
Zunächst müsstest du eine SQL-Abfrage erstellen, welche die Baustellennummern liefert. Mit einem Join wird dann aus der Tabelle BST-Mailing die Mailadresse dazu geholt. Diese SQL-Afrage solltest du dann hier zeigen.
Wenn das erledigt ist, kannst du dann den zweiten Teilschritt in angriff nehmen.
Ich würde dir ja gerne dabei helfen, aber leider kenne ich die Tabellenstrukturen nicht.
Gruß
Ulrich
PS:
Feldnamen mit einem Minus-Zeichen oder mit Sonderzeichen allgemein, solltest du vermeiden. Entweder das Minus-Zeichen weglassen und mit Camelcase (https://en.wikipedia.org/wiki/Camel_case) arbeiten, oder notfalls den Unterstrich verwenden - Besser ist aber, ganz wegzulassen.
Guten Morgen Ulrich,
ich hoffe nun den ersten Teil mit dem nachfolgenden Code richtig erstellt zu haben, das Ergebnis sieht zumindest danach aus. :)
Beide Quellen habe ich nun zusammengeführt und die benötigten Felder ausgegeben.
SELECT [@Fa04 AVS-Stunden ALLE_Mailing].BSTneu, CStr([@BST-Mailing].[Mailadressen]) AS Mailadresse
FROM [@Fa04 AVS-Stunden ALLE_Mailing] INNER JOIN [@BST-Mailing] ON [@Fa04 AVS-Stunden ALLE_Mailing].BSTneu = [@BST-Mailing].BST
GROUP BY [@Fa04 AVS-Stunden ALLE_Mailing].BSTneu, CStr([@BST-Mailing].[Mailadressen]);
PS: Ich habe Dein PS natürlich zur Kenntnis genommen, verzeih' aber das ich das mit den Bezeichnungen erst mal so belassen möchte, bis alles funktioniert. Dann widme ich mich gern den Minus- und Sonderzeichen.
Hallo Tom,
ZitatDann widme ich mich gern den Minus- und Sonderzeichen.
Mach's lieber gleich, der Aufwand ist später deutlich grösser. Jetzt
musst du nur die Tabellennamen korrigieren, später auch Abfragen und
Formulare bzw. alle Objekte und Codes, in denen diese Namen verwendet
werden.
Und bei so langen Namen solltest du dich auch mit der Verwendung von
"Aliassen" vertraut machen. Da lassen sich Abfragen deutlich besser
lesen.
gruss ekkehard
Hallo Ekkehard,
Du hast natürlich Recht, vielleicht besser so. Daher nun der angepasste Code ohne Minus- und Sonderzeichen:
SELECT [Fa04_AVS_Stunden_ALLE_Mailing].BSTneu, CStr([BST_Mailing].[Mailadressen]) AS Mailadresse
FROM [Fa04_AVS_Stunden_ALLE_Mailing] INNER JOIN [BST_Mailing] ON [Fa04_AVS_Stunden_ALLE_Mailing].BSTneu = [BST_Mailing].BST
GROUP BY [Fa04_AVS_Stunden_ALLE_Mailing].BSTneu, CStr([BST_Mailing].[Mailadressen]);
Hallo,
bitte Abfragen formatieren und Codetags verwenden.
SELECT BSTneu, CStr([Mailadressen]) AS Mailadresse
FROM [Fa04_AVS_Stunden_ALLE_Mailing]
INNER JOIN [BST_Mailing] ON [Fa04_AVS_Stunden_ALLE_Mailing].BSTneu = [BST_Mailing].BST
GROUP BSTneu, CStr([Mailadressen])
Wieso verwendest Du für ein Feld das ohnehin ein String (Text) ist Cstr)...)
Die Gruppierung dürfte auch überflüssig sein.
Hallo Klaus,
ich verwende die Felder BSTneu aus einer Abfrage und BST aus einer Tabelle heraus. Aus einem mir unbekannten Grund wurden bei der Ergebnisausgabe nicht die Mail-Adressen, sondern komische Zeichen ausgegeben. Mit dem Formatieren steht nun die korrekte Mailadresse da.
Die Gruppierung muss sein, da über die Abfrage viele Datensätze mit mehreren Baustellennummern ausgegeben werden und ich die verschiedenen Baustellennummern nur 1x benötige.
PS: Wie bereits erwähnt, ich bin was Datenbanken und die Programmierung angeht absoluter Laie und verwende eine bestehende, nicht von mir kreierte Datenbank lediglich, um aus dieser für mich relevante Informationen zu gewinnen und bin daher auf Eure wertvolle Hilfe angewiesen.
Wie kann ich nun die beiden zuvor ermittelten Werte für die Baustellennummer und die jeweils zugehörige Mailadresse in eine Schleife einbauen, in der die nachfolgend markierten Werte, gemäß aller in der Abfrage gefundenen Baustellennummern, ermittelt werden ?
Private Sub Bezeichnungsfeld24_Click()
Me.[BSTWert] = 1000
Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)
With objMail
.To = GetLeistungserfassungMailTo
.Subject = "1000 - Leistungserfassung Sub " & Forms!Start!Text2681 & " - gemäß Export vom: " & Forms!Start!DateiZeitpunkt
.HTMLBody = "<HTML><font size=3> Hallo und guten Tag, ...</BODY></HTML>"
.Attachments.Add "C:\Exporte\Stunden - 1000.xlsm"
.Display
End With
End Sub
Hallo Tom,
ich habe mmal den SQL-Code etwas angepaßt und formattiert:
SELECT b.bstneu, m.mailadressen AS Mailadresse
FROM fa04_avs_stunden_alle_mailing AS b
INNER JOIN bst_mailing as m
ON b.bstneu = m.bst
GROUP BY b.bstneu, m.mailadressen;
Das mit der Funktion CStr() brauchst du nicht, denn Mailadresse muß vom Typ text sein. Was mich noch irritiert, ist der Plural im Feldnamen fa04_avs_stunden_alle_mailing.mailadressen, aber das kannst du selber ändern.
Die eckigen Klammern können auch entfallen, weil keine weiteren Sonderzeichen außer "_" verwendet werden und der Alias für die Tabellennamen macht alles einfacher. Warum du für die Eindeutigkeit jetzt gruppieren musst, leuchtet mir jetzt auch nicht ein. Das kannst du aber später klären, es geht her nur ums Prinzip.
Das wäre der erste Schritt mit der "qryBSTMail".
Der zweite Schritt ist dann die Schleife, welche ich dir hier grob skizziert habe:
Sub CreateMails()
Dim olApp As Outlook.Application
Dim rst As DAO.Recordset
Dim strEmpfaenger As String ' Recipient To
Dim strBetreff As String ' Subject
Dim strMailText As String ' HTMLBody
Dim strAnlage As String ' Attachment Pathname
' Erstelle eine neue Outlook Instanz
' oder greife auf eine bereits geöffnete Instanz zu
Set olApp = New Outlook.Application
' Erstelle ein Recorset mit den Baustellnummern und den zughörigen Maildressen
Set rst = CurrentDb.OpenRecordset("qryBSTMail", dbOpenForwardOnly, dbReadOnly)
' Schleife über alle gefunden DS
Do Until rst.EOF
' Ausgabe der beiden Feldinhalte im Direktfenster
Debug.Print rst!bstneu; " -> "; rst!Mailadresse
' Mit den Formatfunktionen werden die einzeln Texte für die Mail erzeugt
strBetreff = FormatBetreff(rst!bstneu)
strMailText = FormatMailtext()
strAnlage = FormatAttachmentFilename(rst!bstneu)
' Erstelle eine neue Mail mit den übergeben Argumenten
Call SendMail(olApp, strEmpfaenger, strBetreff, strMailText, strAnlage)
' Nächste Datensatz
rst.MoveNext
Loop
' Cleanup
If Not rst Is Nothing Then rst.Close
Set rst = Nothing
' If Not olApp Is Nothing Then olApp.Quit
' Set olApp = Nothing
End Sub
Was du noch einbauen musst, ist die Übergabe der Argumente "Forms!Start!Text2681" und "Forms!Start!DateiZeitpunkt" an die Prozedur Createmails und die Weitergabe an die entsprechende Formatierfunktion.
Das Schreiben der Funktionen Formattier...() und Sendmail() überlasse ich jetzt wieder mal dir, denn ich möchte dir ja nicht alle Arbeit abnehmen. ;)
Gruß
Ulrich
Ganz herzlichen Dank Ulrich,
aber ich stoße gerade an meine Grenzen, weil ich gerade mal den "alten, statischen" Code verstehe, jedoch nicht das mit der Schleife un den ganzen Änderungen. Wäre es denn nicht auch möglich den "alten" Code in eine Schleife zu packen und die "hart geschriebenen" Baustellennummern und Mailadressen zu ersetzen ?
Hallo Tom,
wenn du den alten Code in eine Schleife packen würdest, käme ein ähnliches Schleifenkonstrukt heraus, wie ich dir das bereits erstellt habe.
Wenn du nicht alles verstehst - einfach nachfragen, dann lässt sich das klären.
Also, wo genau in dem Code verstehst du was nicht?
Erst mal DANKE für Dein Verständnis.
Ich verstehe gemäß Deinem Code nicht was da wie warum passiert und denke daher das es mit dem "alten" Code für mich nachvollziehbarer ist.
Ich habe dir mal ein paar Kommentare in den Quellcode eingefügt und hoffe, daß es damit etwas verständlicher wird.
Wenn du wirklich VBA anwenden und programmieren möchtest, kommst du allerdings nicht umhin, wenigstens die Grundlagen zu erlernen bzw. nachzulesen. Das mußt du schon selber machen und kann ich dir nicht abnehmen.
Hier mal etwas für den Einstieg:
https://learn.microsoft.com/en-us/office/vba/api/overview/language-reference (https://learn.microsoft.com/en-us/office/vba/api/overview/language-reference)
https://goalkicker.com/VBABook/ (https://goalkicker.com/VBABook/)
Ist zwar für VB6, gilt aber in den meisten Fällen auch für VBA:
https://de.wikibooks.org/wiki/Visual_Basic_6#Inhaltsverzeichnis (https://de.wikibooks.org/wiki/Visual_Basic_6#Inhaltsverzeichnis)
Am besten jedoch ein hilfreiches Fachbuch, z.B.:
https://www.rheinwerk-verlag.de/suche/?suchbegriff=ms+access+vba (https://www.rheinwerk-verlag.de/suche/?suchbegriff=ms+access+vba)
Gruß
Ulrich
Vielen Dank für die ergänzenden Kommentare, Tipps und Hinweise mein lieber Ulrich, doch ist mir das in der Kürze der Zeit die ich dafür als Laie habe leider zu "hoch" und zu kompliziert zu verstehen. Bitte nicht falsch verstehen, aber ich muss das dann wohl leider lassen. Trotzdem DANKE noch mal.
Ich hab es doch mit meinem Code probiert und es zumindest geschafft, dass die korrekte Baustellennummer sowie die zugehörige Mailadresse übergeben und eine neue Mail samt korrektem Anhang erstellt wird.
Problem nun ist nur, das die Schleife nicht ausgeführt wird. Wie müssen die beiden roten Stellen richtig eingearbeitet werden ?
Private Sub Befehl2819_Click()
Dim rst As DAO.Recordset
Dim dbe As DAO.Database
Set dbe = CurrentDb
Set rst = CurrentDb.OpenRecordset("@Mailing", dbOpenForwardOnly, dbReadOnly)
' Do Until rst.EOF
' Ausgabe der beiden Feldinhalte im Direktfenster
Debug.Print rst!bstneu; " -> "; rst!Mailadresse
With rst
Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)
With objMail
.To = rst!Mailadresse ' GetLeistungserfassungMailTo
.Subject = rst!bstneu & " - Leistungserfassung Sub " & Forms!Start!Text2681 & " - gemäß Export vom: " & Forms!Start!DateiZeitpunkt
.HTMLBody = "<HTML><font size=3> Hallo und guten Tag, ...</BODY></HTML>"
.Attachments.Add "C:\Exporte\VWStunden\AVS-WV-Stunden - " & rst!bstneu & ".xlsm"
.Display
End With
rst.MoveNext
' Loop
End With
rst.Close
Set rst = Nothing
Set dbe = Nothing
End Sub
Das sieht doch schon mal nicht schlecht aus, nur solltest du dich an die Vorgaben halten.
Weder habe ich ein Variable dbe definiert, noch WITH verwendet. Dann fehlen die Definition für die Instanz von Outlook und dem Mailitem, was du neu erzeugen möchtest. So könnte das nicht kompiliert werden.
Dann solltest du dir nochmal anschauen, welche Teile in den Schleifenkörper hineingehören und welche Teil des Codes außerhalb der Schleife stehen.
Auch mußt du zwingend die Referenz zum Outlook.Application Objekt wieder löschen, sonst gibst es früher oder später damit Probleme.
Ich hab's zwischenzeitlich auch hinbekommen, mit dem Code funktioniert's
Wie löscht man den die "Referenz zum Outlook.Application Objekt" ?
Private Sub Befehl2819_Click()
Dim rst As DAO.Recordset
Dim dbe As DAO.Database
Set dbe = CurrentDb
' Erstelle ein Recorset mit den Baustellnummern und den zughörigen Maildressen
Set rst = CurrentDb.OpenRecordset("@Mailing", dbOpenForwardOnly, dbReadOnly)
' Schleife über alle gefunden DS
Do Until rst.EOF
Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)
With objMail
.To = rst!Mailadresse
.Subject = rst!bstneu & " - Leistungserfassung Sub " & Forms!Start!Text2681 & " - gemäß Export vom: " & Forms!Start!DateiZeitpunkt
.HTMLBody = "<HTML><font size=3> Hallo und guten Tag, ...</BODY></HTML>"
.Attachments.Add "C:\Exporte\VWStunden\AVS-WV-Stunden - " & rst!bstneu & ".xlsm"
.Display
End With
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set dbe = Nothing
End Sub
Zitat von: knobbi38 am Oktober 25, 2024, 15:52:11Auch mußt du zwingend die Referenz zum Outlook.Application Objekt wieder löschen, sonst gibst es früher oder später damit Probleme.
Ist das dann so gemeint ?
Set objOutlook = Nothing
Set objMail = Nothing
Hallo Sebastian,
wird doch immer besser, aber du solltest schon auf die Details achten. In der Schleife jedes mal Outlook neu starten zu wollen, gibt nicht viel Sinn. Hier mal der bereinigt Code:
Private Sub Befehl2819_Click()
Dim objOutlook as Object
Dim objMail as Object
Dim rst As DAO.Recordset
Set objOutlook = CreateObject("Outlook.Application")
' Erstelle ein Recorset mit den Baustellnummern und den zughörigen Maildressen
Set rst = CurrentDb.OpenRecordset("@Mailing", dbOpenForwardOnly, dbReadOnly)
' Schleife über alle gefunden DS
Do Until rst.EOF
Set objMail = objOutlook.CreateItem(0)
With objMail
.To = rst!Mailadresse
.Subject = rst!bstneu & " - Leistungserfassung Sub " _
& Forms!Start!Text2681 & " - gemäß Export vom: " _
& Forms!Start!DateiZeitpunkt
.HTMLBody = "<HTML><font size=3> Hallo und guten Tag, ...</BODY></HTML>"
.Attachments.Add "C:\Exporte\VWStunden\AVS-WV-Stunden - " _
& rst!bstneu & ".xlsm"
.Display
End With
rst.MoveNext
Loop
' Cleanup
if rst is not nothing then rst.Close
Set rst = Nothing
Set objMail = nothing
Set objOutlook = nothing
End Sub
Vielleicht noch ein paar ergänzende Anmerkungen:
- Warum fängt deine Query mit einem "@" an? Mehrere Helfer haben dir glaube ich schon geraten, auf solche Sonderzeichen besser zu verzichten.
- Dein Button sollte einen "sprechenden" Bezeichner mit einem Präfix haben, z.B. "cmdCreateMails"
- In jeder Klasse/Modul sollte "Option Compare Database" und "Option Explicit" verwendet werden.
Gruß
Ulrich
Guten Morgen lieber Ulrich,
DANKE noch mal für den optimierten Code. Warum ich das mit den Sonderzeichen doch erst mal gelassen hatte, hatte den schon zuvor schon beschriebenen Grund, erst mal zu schauen ob und wie es funktioniert, um dann diese Korrekturen nachdem man es verstandet hat durchzuführen.
Deine anderen wertvollen Hinweise habe ich natürlich dankend aufgenommen und werde sie versuchen mit zu implementieren.
Das mit der Schleife und innerhalb dieser jedes Mal Outlook neu zu starten, macht natürlich wirklich keinen Sinn und habe es daher, wie von Dir beschrieben, vor die Schleife gesetzt.
Ein großes DANKE an der Stelle noch mal für Eure Unterstützung und für Euch nun auch ein erholsames Wochenende.
Beste Grüße
Tom