Liste aus Abfrage an unterschiedliche Empfänger senden

[Lalikowski]

Hallo zusammen,

zu einem ähnlichen Thema ist mir schon einmal ganz toll geholfen worden, nur war es seinerzeit ein Report, nun handelt es sich um ein Excelsheet.

Folgendes Ergebnis möchte ich erreichen.
Das Ergebnis aus der Abfrage "qry_XLS" soll partnerrein, nach und nach an die einzelnen Partner (tbl_Partner) versendet werden.

Im Anhang ist ein entsprechendes Demo.

Vielen Dank im Voraus

Andreas

[Beaker s.a.]

Hallo,
Ändere zunächst deine Abfrage

Code [Auswählen] Erweitern

SELECT
       tbl_Partner.TD_Leister,
       tbl_Partner.Name,
       tbl_XLS_Fehler.Datum,
       tbl_XLS_Fehler.Uhrzeit,
       tbl_XLS_Fehler.Trackdetail,
       tbl_XLS_Fehler.Lieferung,
       tbl_XLS_Fehler.Route,
       tbl_XLS_Fehler.Status
FROM tbl_Partner
         INNER JOIN tbl_XLS_Fehler
         ON tbl_Partner.TD_Leister = tbl_XLS_Fehler.TD_Leister
WHERE tbl_XLS_Fehler.Datum = [Forms]![frm_Start]![Leistungsdatum]
Alle Felder zu gruppieren zeigt alle DS genauso an wie ohne Gruppierung. Ausserdem
hast du ja keine Aggregate in der Abfrage.
"Datum" ist ein schlechter Feldname. Zwar nicht in SQL, aber auf der Oberfläche kann
das zu Problemen führen. Nenne es "VDatum" o.s.ä.
Beim Klick auf "Listen senden" gehst du in einer Schleife durch die Tabelle "tbl_Partner"
verschickst die Daten. Wie das genau passieren soll hast du nicht geschrieben.
gruss ekkehard

[Lalikowski]

Guten Morgen Ekkehard,

vielen Dank für Deine Mühe.

Mit Liste senden meinte ich, dass jede einzelne Excelliste an die jeweilige(n) Mailadresse(n) in der Tabelle tbl_Partner versendet werden soll.

Viele Grüße

Andreas

[Beaker s.a.]

Hallo Andreas,
Also als Email-Anhang.

Code [Auswählen] Erweitern

Private Sub Befehl3_Click()
'den Namen solltest du in eigenem Interesse so schnell wie
'möglich ändern (damit du weisst was der Button macht
   
    Dim rst As DAO.Recordset
   
    Set rst = CurrentDb.OpenRecordset( _
          "SELECT Mail " _
        & "FROM tbl_Partner")
'mit einer WHERE-Klausel kannst du hier auch die Empfänger einschränken

    With rst
        Do While Not .EOF
            DoCmd.SendObject _
                Objecttype:=acSendQuery, _
                Objectname:="qry_XLS", _
                Outputformat:=acFormatXLS, _
                To:=.Fields(0).Value, _
                Subject:="Dein Betreff", _
                Messagetext:="Text der Email", _
                Editmessage:=False    'bei True öffnet sich dein Emailprogramm
            .MoveNext
        Loop
    End With
   
rst.Close
Set rst = Nothing

End Sub

gruss ekkehard

[Lalikowski]

Hallo Ekkehard,

super Anfang....

Eine Kleinigkeit passt noch nicht richtig.
Im Mailanhang enthält die Excel-Datei ALLE Datensätze und nicht nur die des Partners, der die Mail bekommt.

Kannst Du bitte nochmal Hand anlegen....

Liebe Grüße

Andreas

[Lalikowski]

Guten Morgen Ekkehard,

ich lese gerade, dass meine Kollegen diese Listen zusätzlich gespeichert haben wollen.

Kannst Du dabei bitte auch unterstützen?

Ganz lieben Dank im Voraus

Andreas

[Beaker s.a.]

Hallo Andreas,

Im Mailanhang enthält die Excel-Datei ALLE Datensätze und nicht nur die des Partners, der die Mail bekommt.

Hat ein bisschen gedauert.
Habe es zuerst mit einer Parameter-Abfrage versucht

Code [Auswählen] Erweitern

SELECT P.TD_Leister, P.Name, F.Datum, F.Uhrzeit, F.Trackdetail, F.Lieferung,
       F.Route, F.Status
FROM tbl_Partner AS P
     INNER JOIN tbl_XLS_Fehler AS F
     ON P.TD_Leister = F.TD_Leister
WHERE F.Datum = [Forms]![frm_Start]![Leistungsdatum]
     AND P.TD_Leister = [@LeisterID]
(damit man's besser lesen kann jetzt mit Aliasen für die Tabellen)
Im Code habe ich es dann so probiert

Code [Auswählen] Erweitern

    Set rstP = dbe.OpenRecordset( _
          "SELECT TD_Leister, Mail " _
        & "FROM tbl_Partner ORDER BY TD_Leister")

    Set qdf = dbe.QueryDefs("qry_XLS")
   
    With rstP
        Do While Not .EOF
            qdf.Parameters("@LeisterID") = "'" & .Fields(0) & "'"
           
            DoCmd.SendObject _
                Objecttype:=acSendQuery, _
                Objectname:="qry_XLS", _
                Outputformat:=acFormatXLS, _
                To:=.Fields(1).Value, _
                Subject:="Dein Betreff", _
                Messagetext:="Dein Anschreiben", _
                Editmessage:=True       'zum Testen, später "False"
            .MoveNext
        Loop
    End With
Das hat aber nicht gefunzt, weil die Abfrage davon nichts mitbekommt, und deshalb
bei "Objectname:="qry_XLS", der Parameter wieder manuell abgefragt wird.
Vielleicht wissen die Regulars wie man's richtig macht.

Ich habe es jetzt erstmal so gemacht (Parameter in Abfrage entfernt)

Code [Auswählen] Erweitern

    Const QRY_XLS_DEF As String = _
                  "SELECT P.TD_Leister, " _
                       & "P.Name, " _
                       & "F.Datum, " _
                       & "F.Uhrzeit, " _
                       & "F.Trackdetail, " _
                       & "F.Lieferung, " _
                       & "F.Route, " _
                       & "F.Status " _
                & "FROM tbl_Partner AS P " _
                        & "INNER JOIN tbl_XLS_Fehler AS F " _
                        & "ON P.TD_Leister = F.TD_Leister " _
                & "WHERE F.Datum = [Forms]![frm_Start]![Leistungsdatum]"
'dies ist die Abfrage OHNE Filter auf den Partner
'(jetzt mit Tabellen-Aliasen wegen besser zu lesen)
'hiermit wird die Abfrage also wieder zurückgesetzt,
'da unten der Abfragetext um den Filter auf den Partner ergänzt wird
   
    Dim rstP As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim dbe As DAO.Database
   
    Set dbe = CurrentDb
   
    Set rstP = dbe.OpenRecordset( _
          "SELECT TD_Leister, Mail " _
        & "FROM tbl_Partner ORDER BY TD_Leister")

    Set qdf = dbe.QueryDefs("qry_XLS")
       
    With rstP
        Do While Not .EOF
            'zurücksetzen des Abfragetextes
            qdf.SQL = QRY_XLS_DEF
            'Filter auf Partner anhängen
            qdf.SQL = qdf.SQL & " AND F.TD_Leister = '" & .Fields(0) & "'"

            DoCmd.SendObject _
                    Objecttype:=acSendQuery, _
                    Objectname:="qry_XLS", _
                    Outputformat:=acFormatXLS, _
                    To:=.Fields(1).Value, _
                    Subject:="Dein Betreff", _
                    Messagetext:="Dein Anschreiben", _
                    Editmessage:=False
            DoCmd.TransferSpreadsheet _
                      TransferType: acExport , _
                      SpreadsheetType:=acSpreadsheetTypeExcel12, _
                      TableName:="qry_XLS", _
                      FileName:="C:\" & .Fields(0) & ".xls", _
                      HasFieldNames:=True           .MoveNext
        Loop
    End With

Ende_CleanUp:
qdf.SQL = QRY_XLS_DEF
qdf.Close
Set qdf = Nothing
rstP.Close
Set rstP = Nothing
Set dbe = Nothing

End Sub
Das läuft bei mir wie vorgestellt.

Das Speichern der Abfrage als Excel-Datei habe ich dir auch gleich mit eingebaut
Mit den Parametern von "DoCmd.TransferSpreadsheet" musst du ein bisschen rumspielen.
Bei "SpreadsheetType" bin ich nicht sicher (habe kein Excel)
Bei "FileName" musst du darauf achten, dass Schreibzugriff auf den angegebenen
Pfad besteht.

gruss ekkehard

[Lalikowski]

Guten Morgen Ekkehard,

es funktioniert schon fast, nur das der Abfragecode, beim Start der Listen, automatisch geändert wird.
Hier der code vor dem Start der Listen:

Code [Auswählen] Erweitern

SELECT P.TD_Leister, P.Name, F.Datum, F.Uhrzeit, F.Trackdetail, F.Lieferung,
       F.Route, F.Status
FROM tbl_Partner AS P
     INNER JOIN tbl_XLS_Fehler AS F
     ON P.TD_Leister = F.TD_Leister
WHERE F.Datum = [Forms]![frm_Start]![Leistungsdatum]
     AND P.TD_Leister = [@LeisterID]
...und hier NACH dem Start:

Code [Auswählen] Erweitern

SELECT P.TD_Leister, P.Name, F.Datum, F.Uhrzeit, F.Trackdetail, F.Lieferung,
F.Route, F.Status
FROM tbl_Partner AS P
INNER JOIN tbl_XLS_Fehler AS F
ON P.TD_Leister = F.TD_Leister
WHERE F.Datum = [Forms]![frm_Start]![Leistungsdatum]
AND F.TD_Leister = '1800225';
Wie Du siehst, wird der f.TD_Leister plötzlich auf "1800225" beschränkt.
Er sollte aber in der Tabelle bis zum Ende weiterlaufen.

Kann es an dem Zurücksetzen des Abfragetextes liegen?

Viele Grüße

Andreas

[Lachtaube]

@Ekkehard,

ab Access 2010 lässt sich vielleicht (ungetestet - SendObject ist explizit nicht erwähnt) DoCmd.SetParameter() in der Schleife verwenden. Ansonten sollte eine Parameterübergabe mittels globaler Funktion (FAQ 3.15) komfortabel genug sein.

[Beaker s.a.]

Hallo,
@Lachtaube
Auf "DoCmd.SetParameter()" bin ich inzwischen auch schon gestossen. Kannte
ich noch nicht und hatte bis jetzt auch keine Zeit es auszuprobieren, - schau
ich mir am WE mal genauer an.

@Andreas
Die ersten beiden Codeschnipsel sind nicht relevant. Das hatte ich nur mit aufge-
schrieben um evtl. einen Hinweis der Regulars zu erhalten (bin ja auch noch ein
Lernender). Wenn ich es mit Lachtaubes Hinweis lösen kann, melde ich mich.
Also der Parameter "[@LeisterID]" steht bei mir nicht mehr drin.

Wie Du siehst, wird der f.TD_Leister plötzlich auf "1800225" beschränkt.

Ja aber das wolltest du doch. Beim nächsten Durchlauf der Schleife steht da

AND F.TD_Leister = '1800229'

Ah, ich sehe gerade den Fehler

Code [Auswählen] Erweitern

                    HasFieldNames:=True
            .MoveNext
        Loop
da ist beim Posten einen Zeilenvorschub verloren gegangen. Es ist hilfreich, wenn
man versucht auch zu verstehen was man kopiert.

gruss ekkehard

[Lalikowski]

Hallo Ekkehard,

läuft leider nicht. Bekomme jetzt einen Kompilierfehler....

Schau Dir mal bitte den Anhang an.

Viele Grüße

Andreas

[Lalikowski]

Hallo Ekkehard,

ich nehme meine vorherige Mail zurück.

Es funktioniert.....top.....

Dankeschön für Eure Hilfe und ein entspanntes Wochenende

Andreas

[Beaker s.a.]

@Lachtaube
Habe es jetzt gerade mal versucht mit ".SetParameter", - funzt nicht.
Jedenfalls bekommt die Abfrage auch so nichts von dem Parameter mit

Code [Auswählen] Erweitern

    Set qdf = dbe.QueryDefs("qry_XLS")

    With rstP
        Do While Not .EOF
            DoCmd.SetParameter "LeisterID", .Fields(0)

            DoCmd.SendObject _
                    Objecttype:=acSendQuery, _
                    Objectname:="qry_XLS", _

Bei beiden DoCmd-Methoden wird der Parameter wieder abgefragt.
Ist wohl wirklich nur für die in der OH angegebenen Methoden verwendbar.

Der Name muss mit dem Namen des Parameters übereinstimmen, der von der BrowseTo-, OpenForm-, OpenQuery-, OpenReport- oder RunDataMacro-Methode erwartet wird.

gruss ekkehard

[Lachtaube]

@Ekkehard,

dann gibt's ja Gott sei Dank noch Verbesserungspotenzial für Access 2022.

[Lalikowski]

Hallo Ekkehard,

habe Deine code heute zum ersten Mal komplett durchlaufen lassen.
Dabei fiel dann doch noch ein Fehler auf.

Die Schleife hat kein Ende, d.h. wenn die Tabelle mit den Mailadressen abgearbeitet ist, fängt es wieder von vorne an....

Kannst Du bitte nochmal schauen, woran das liegt?

Viele Grüße

Andreas