Office: VBA: Wenn der Wert >2 dann soll eine E-Mail versendet werden. Überprüfung von ca. 100 Zeilen.

Hallo Zusammen,

ich habe das Problem das wir in unserem Unternehmen eine Bestandsliste unserer Toner in Excel führen. Aktuell bin verusche ich diese übersichtlicher und mit einen Upgrades zu verbessern.
Sobald sich mal ein Toner dem Ende neigt fände ich es super, wenn Excel eine Mail an unseren Verteiler sendet mit dem Inhalt, dass der Toner XYZ (je nachdem welcher Toner leer ist) folgenden Bestand aufweist und hier nachbestellt werden muss. Die Mail sollte bei einem Bestand von nur noch einem oder keinen Toner versendet werden

Die Tabelle setzt sich wie folgt zusammen:
Drucker, Toner (2 Zeilen), Anzahl, Farbe

Da wir viele Toner haben müssten aktuell ca. 100 Zellen mit diesem Makro überprüft werden.

Ist sowas denn möglich oder gibt es hier eine andere Möglichkeit?

Wir verwenden im übrigen Office 2013.

Kenn mich da leider nicht wirklich aus.

Habe bisher mal etwas zusammengestellt was allerdings nur für eine Zeile gut ist:

Sub auto_open()

Dim OutApp As Object
Dim EmailText As String
Dim rZelle As Range

' überwacht jetzt als Beispiel die Zelle von D3
' in Spalte A (3) steht im Beispiel der Terminbetreff

For Each rZelle In Range("D3") ' gegebenenfalls anpassen
If rZelle.Value > 2 Then

Set OutApp = CreateObject("Outlook.Application")
With OutApp.CreateItem(0)
'Empfänger
.To = "Vorname.Nachname@abc.de" ' Mailadresse anpassen
'Betreff
.Subject = "Tonerstand kritisch" & " = " & ThisWorkbook.Worksheets("Logbuch").Range("A3") & " - " & ThisWorkbook.Worksheets("Logbuch").Range("B3") & " - " & ThisWorkbook.Worksheets("Logbuch").Range("C3")
'Nachricht Cells(rZelle.Row, 3) -> hier wird automatisch der Terminbetreff eingetragen
.Body = "Der Tonerbestand ist unter den Wert 2 gefallen, bitte bestellen"
' Cells(rZelle.Row, 3) -> hier wird automatisch der Terminbetreff eingetragen
'Lesebestätigung aus
.ReadReceiptRequested = False
' Dateianhang hinzufügen
'.Attachments.Add Name
' Mail Anschauen
'.Display
' Mail Senden
.Send ' Die Mail sofort senden!
End With
Set OutApp = Nothing

End If
Next

End Sub


Wäre super wenn mir jemand helfen kann

Liebe Grüße

 

Hallo S.Kraft,

probier doch mal

Code:

For Each rZelle In Range("D3:D100")

Aber Du solltest Deine Vorgabe nochmal prüfen. Im Betreff schreibst Du >2, im Text<=2 und im Code wieder >2.

Gruß,
Lutz

 

Danke für die schnelle Antwort :)
Ich hab es nun mal so angepasst wie du es mir mitgeteilt hast. Bekomme allerdings nun zu viele E-Mails :D

Wo siehst du das mit dem Text <=2? Die Mail soll erst versendet werden wenn bei der Zeile Bestand die 1 oder 0 steht.

Und dann habe ich noch das Problem mit dem variablen Betreff. Kann der Betreff so eingestellt werden das für jede Mail immer die Zeile ausgewählt wird, welche den Wert von 2 unterschreitet?


Sub auto_open()

Dim OutApp As Object
Dim EmailText As String
Dim rZelle As Range

' überwacht jetzt als Beispiel die Zelle von D3:D100
' in Spalte A (3) steht im Beispiel der Terminbetreff

For Each rZelle In Range("D3:D100") ' gegebenenfalls anpassen
If rZelle.Value > 2 Then

Set OutApp = CreateObject("Outlook.Application")
With OutApp.CreateItem(0)
'Empfänger
.To = "Vorname.Nachname@abc.de" ' Mailadresse anpassen
'Betreff
.Subject = "Tonerstand kritisch" & " = " & ThisWorkbook.Worksheets("Logbuch").Range("A3") & " - " & ThisWorkbook.Worksheets("Logbuch").Range("B3") & " - " & ThisWorkbook.Worksheets("Logbuch").Range("C3")
'Nachricht Cells(rZelle.Row, 3) -> hier wird automatisch der Terminbetreff eingetragen
.Body = "Der Tonerbestand ist unter den Wert 2 gefallen, bitte bestellen"
' Cells(rZelle.Row, 3) -> hier wird automatisch der Terminbetreff eingetragen
'Lesebestätigung aus
.ReadReceiptRequested = False
' Dateianhang hinzufügen
'.Attachments.Add Name
' Mail Anschauen
'.Display
' Mail Senden
.Send ' Die Mail sofort senden!
End With
Set OutApp = Nothing

End If
Next

End Sub


Gruß Sascha

 

Hallo Sasha

Vielleicht eine seltsame Frage:
Warum machst du das nicht über die Serien-Email-Funktion von Word?

UX-Bedenken:
Vor allem bekommt der Einkauf dann nur eine Email pro Zyklus und nicht eine pro Edit.
Im Worst Case kriegt der Einkauf bei jedem Öffnen der Datei immer wieder eine Mail für
einen Tonertypus der zwar bestellt aber nicht nicht eingetroffen ist.

Sicherheits-Bedenken:
Für auto_open() in einer nicht signierten VBA würde mich die IT erschlagen
und 80% meiner User würden das enable Macro gar nicht wahrnehmen.

Komplett anderer Weg:
Wenn die Menschen die den Toner einsetzen und damit den Bestand reduzieren und die Menschen die das Lager auffüllen, statt den Wert in der Tabelle zu ändern, eine Protokolle-Tabelle schreiben: Kannst du im Hintergrund den aktuellen Lagerbestand berechnen und weil alle historischen Daten vorhanden sind: Vorhersagen darüber machen welcher Toner wie oft benötigt wurde und wer die letzten Änderungen gemacht hat.

Pan

 

Hallo Pan danke für deine Antwort.

Auf diese Tabelle greifen nur 2 bzw. 3 Leute zu. Es wird also nicht jeder Mitarbeiter aus dem Unternehmen auf die Datei zugreifen und hier eine Entnahme dokumentieren

Es wurde so gewünscht, dass diese Excel Datei verwendet wird.

Ich habe Sie mal angehängt. Evtl. hilft das hier weiter.

Wie gesagt mit der Programmierung von sowas hab ich wirklcih keine Ahnung :D.

Wie würde das über die Serienmail Funktion von Word funtkionieren?

 

Hallo Sascha,

Du hast Recht, im Text steht 1 oder 0, also <2.
Im Makro steht jedoch immer noch

Code:

If rZelle.Value > 2 Then

Damit bekommst Du für alle Toner mit 3 und mehr Bestand eine Mail.

Der Betreff sollte mit

Code:

.Subject = "Tonerstand kritisch" & " = " & ThisWorkbook.Worksheets("Logbuch").Cells(rZelle.row,1) & " - " & ThisWorkbook.Worksheets("Logbuch").Cells(rZelle.row,2)  & " - " & ThisWorkbook.Worksheets("Logbuch").Cells(rZelle.row,3) 

variabel sein.

Ansonsten muss ich natürlich Pan beipflichten. AutoOpen ist etwas problematisch. Musst Du auf jeden Fall bei jedem User bei der Einführung kommunizieren.
Warum eigentlich beim Open? Aus meiner Sicht wäre bei WorkbookClose der richtige Zeitpunkt.
Und du solltest bedenken, dass Dein Einkauf bei JEDEM Öffnen für JEDEN zu bestellenden Toner eine Mail bekommt. Als Einkauf würde ich Dir das Weihnachtsgeschenk kündigen :-)

Gruß,
Lutz

 

Hallo Sascha

Manchmal muss man den Anwendern geben was sie brauchen und nicht was sie wollen.
Ich hab dir mal angehängt was ich meine, dann wirst du sehen das die gleichen Informationen nur anders angeordnet sind.

Pan

 

Die Tabelle ist perfekt. Habe es nun so wie es sein soll. Vielen Dank an alle für die schnelle Hilfe :)

 

Hallo in die Runde,

kann man den Thread nochmal "aufleben" lassen?

Ich nutze so ziemlich den von S. Kraft erstellen Code mit der variablen Anpassung von Lutz Fricke.
Ich würde gerne noch einen Schritt "weitergehen" und nur Emails an den Zuständigen ab Spalte E5 mit der hinterlegten Email aus Tabellenblatt "Setup" ab Spalte E5 verschicken. Zuständige können "Maik" "Stefan" "Ralf" oder "Maik / Ralf" "Ralf / Stefan" sein. Also Wenn = "Maik / Stefan", Dann Mail an "Maik / Stefan" sonst wenn "Maik", Dann Mail "Maik" usw..

Vielen Dank im Voraus