Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
VBA Mailversand ohne leere Zellen
07.03.2018 13:43:06
Klaus
Hallo zusammen.
Ich hätte da für die Experten hier mal wieder eine kleine Aufgabe.
Als erstes mal mein "Problem-Makro":
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("J37:S38")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
Call Send_Notice
End If
End Sub
Private Sub Send_Notice()
Dim objOutlook As Object
Dim objMail As Object
Dim RngBereich As Range
Set RngBereich = Range("J37:J38")
Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)
With objMail
.to = "xxx@xxx-xxx.de"
.Subject = "Mehrarbeit Jahresschichtplan Logistik 2018"
.Body = Cells(37, 3) & " arbeitet am " & Cells(37, 6) & " länger, weil " & Cells(37, 10) _
& vbLf & _
Cells(38, 3) & " arbeitet am " & Cells(38, 6) & " länger, weil " & Cells(38, 10)
.Send
End With
End Sub

(Ja, ich weiß, dass der Code sicherlich optisch verbessert werden könnte und bestimmt auch Teile enthält, die unwichtig sind. Aber ich bin noch ein Anfänger ;-))
So. Das Makro tut, was es soll. Es schickt mir eine Email, wenn ein User eine bestimmte Zeile ausfüllt und in der letzten Zelle (J:37 bzw. J:38) Enter drückt.
Als Problem sehe ich, dass:
1. auch wenn nur in der 1. Zeile Inhalt steht in der Email die 2. Zeile geschrieben wird.
Mailinhalt:
XXXXX arbeitet am 05.03.2018 länger, weil XXXX länger gemacht (Arbeits-) Zeit5.48-16.00 arbeitet am länger, weil (Arbeits-) Zeit Wie muss ich also den Code anpassen, dass er mir nur die Zeilen in der Mail anzeigt, die gefüllt sind?
2. Nachdem die Mail versendet wurde, was ja im Hintergrund passiert und für den User nicht sichtbar ist, soll der Inhalt der beschriebene Zeile gelöscht werden. Super wäre auch noch eine Msg-Box mit der Bestätigung, dass die Email versandt wurde. (Vlt. funktioniert das ja auch in Kombination? Msg-Box mit Bestätigung, Button mit OK, Zellen-/Zeileninhalt löschen?)
Es soll aber wie gesagt nur dewr Inhalt, nicht die Formatierung, Rahmen, etc. entfernt werden.
Vielen Dank schonmal für eure Hilfe :)

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA Mailversand ohne leere Zellen
07.03.2018 15:35:21
Oberschlumpf
Hi Klaus
ersetz mal diese Codezeile

.Body = Cells(37, 3) & " arbeitet am " & Cells(37, 6) & " länger, weil " & Cells(37, 10) & vbLf  _
& _
Cells(38, 3) & " arbeitet am " & Cells(38, 6) & " länger, weil " & Cells(38, 10)

durch diesen Code

.Body = Cells(37, 3) & " arbeitet am " & Cells(37, 6) & " länger, weil " & Cells(37, 10)
If Cells(38, 3)  "" Then
.Body = .Body & vbLf & Cells(38, 3) & " arbeitet am " & Cells(38, 6) & " länger, weil " & Cells( _
38, 10)
End If
Jetzt wird die 2. Zeile nur noch hinzugefügt, wenn die Zelle(38, 3) einen Eintrag enthält.
Wenn Zelle(38, 3) = "", aber Zelle(38, 6) oder Zelle(38, 10) NICHT LEER, dann wird die 2. Zeile trotzdem nicht geschrieben.
Hilfts?
Ciao
Thorsten
Anzeige
AW: VBA Mailversand ohne leere Zellen
07.03.2018 16:15:18
Klaus
Hallo Thorsten.
Vielen Dank für deinen Lösungsansatz.
Testen kann ich es leider erst morgen, da ich Zugriff auf die Datei nur auf Arbeit habe.
Was mir aber auffällt :
Derzeit wird die Email nur gesendet, wenn die letzte Zelle J:37 bzw. J:38 verändert wird (durch Enter etc.) Daher gehe ich davon aus, dass ich dein "If" auf diese Zelle ändere.
Da die User immer alle Zellen von links nach rechts (B37 - J37) ausfüllen müssen schien es mir sinnvoll, den Mailversand auf die letzte Zelle zu setzen.
Oder gibt es hier eine andere Möglichkeit?
AW: VBA Mailversand ohne leere Zellen
07.03.2018 16:17:40
Klaus
Huch, hab das Kontrollkästchen vergessen. Ich hoffe der Beitrag ist jetzt noch/wieder offen...
Sorry
Anzeige
AW: VBA Mailversand ohne leere Zellen
07.03.2018 16:22:19
Oberschlumpf
Hi Klaus,
zeig uns bitte per Upload eine Bsp-Datei.
Sonst wird das zum Rätselraten.
Ciao
Thorsten
AW: VBA Mailversand ohne leere Zellen
08.03.2018 06:03:30
Klaus
Guten Morgen liebe Profis.
Hier, wie gewünscht, eine kleine Excel zum Veranschaulichen:
https://www.herber.de/bbs/user/120272.xlsm
Nocheinmal eine kleine Zusammenfassung meiner Vorstellung:
Die Mitarbeiter füllen die Tabelle (Zeile 37 od. 38) aus. Es sind nur diese 2 Zeile zur Eingabe freigegeben. Das restliche Blatt hat einen Blattschutz. Wenn alle 3 Spalten der Tabelle ausgefüllt sind (!) soll automatisch eine Email via Outlook versendet werden. Im .Body soll der Inhalt der Spalten stehen (siehe .Body in meinem Code).
Super wäre auch, dass nach Versand der Email eine Msg-Box kommt. Inhalt hier z.B. "Email versand", OK-Button. Wenn der OK-Button geklickt wird soll der Inhalt der Zeile 37 und 38 gelöscht werden. (Das geht meines Wissens ja mit .ClearContents)
Danke vorab :)
Anzeige
AW: VBA Mailversand ohne leere Zellen
08.03.2018 13:25:24
Klaus
Hallo Thorsten.
Habs mal getestet und den Code kurz überflogen. Sieht super aus und funktioniert. Vielen, vielen Dank ersteinmal dafür.
Eine Kleinigkeit ist mir aber aufgefalle:
Wenn der MA die erste Zeile füllt und in der Sendeabfrage auf nein drückt, weil er die 2.Zeile auch noch füllen will, sich dann aber doch umentscheidet und die 2.Zeile nicht füllt, wird die Mail ja nicht versandt. Kann man da noch etwas einbauen, was diesen Fall abdeckt?
Denkbar wäre (falls möglich), dass er dann einfach nochmal in die letzte Zelle von Zeile 1 doppelklickt und nach einem erneuten Enter die Sendeabfrage wieder kommt.
(Oder kommt es da zu Problemen im Code?!)
Auf jeden fall nochmals vielen Dank bis hierhin. Klasse arbeit und super Code :)
Anzeige
zumindest mein...
08.03.2018 14:57:06
Oberschlumpf
...letzter Versuch
Hi Klaus,
ich hab alles noch mal geändert; guckst du:
https://www.herber.de/bbs/user/120291.xlsm
Wie du merkst, habe ich unterhalb der Eingabezeilen einen weiteren CommandButton eingefügt.
Und nun ist es egal, welche Zellen in den Zeilen 37 oder 38 befüllt sind oder nicht.
Der User muss immer auf den Button klicken, damit eine Mail erstellt und versendet wird.
Nach Klick auf den Button wird weiterhin zuerst geprüft, welche Zeile vollständig ausgefüllt ist.
Wenn beide MA's eingetragen sind, erscheint in der Mail entsprechender Text für beide MA's.
Wenn nur für MA1 Einträge vorhanden, dann in der Mail nur Text für MA1.
Wenn nur für MA2 Einträge vorhanden, dann in der Mail nur Text für MA2.
Wenn für MA1 oder für MA2 auch nur ein Eintrag fehlt, wird die entsprechende Zeile in der Mail nicht berücksichtigt.
Hilfts?
Wenn nicht, bin ich dann raus. Dann erstell bitte einen neuen Thread.
Ciao
Thorsten
Anzeige

371 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige