Automatischer Versand von Mails aus Excel per Makro

Question

Hey die Runde!

Hoffe, es findet sich jemand, der mir bei meinem Wunsch behilflich sein kann:

Ich möchte aus einer Datei heraus bei erreichen einer bestimmten Menge automatisch eine Mail aus Outlook heraus versenden. Es geht um die Verwaltung von Verbrauchsgütern. Wird eine bestimmte geringe Menge erreicht und damit Nachbestellung fällig, sollte eine Mail an den Einkäufer erfolgen.
Ich behelf mir momentan mit bedingter Formatierung, die aber den Nachteil hat, dass man immer nachsehen muss, ob die Mindestmenge erreicht ist.

Kann man das so automatisieren, dass Excel selbst anhand eines Makros die Sollmenge überprüft und dann eine Mail automatisch verschickt, ohne mein Zutun?

Wäre super, wenn das überhaupt ginge.

Gracias, Mandy

Answer 1

Hallo Karin,

Wirklich dufte, dass du in so kurzer Zeit so eine prächtige Lösung hinkriegtest!
Herzlichen Dank dafür!

Es passt nun alles wunderbar, doch habe ich noch eine Frage:

Da ich den VBA-Code leider nicht verstehe, interessiert es mich, wie das Makro erkennt, um welchen Toner es sich handelt. Wo steht das im VBA-Code?
Ich müsste den Code entsprechend abändern, da die Datei im Büro etwas größer ist, aber in der Grundfassung doch gleich meinem Beispiel.

Abgeändert hab ich es inzwischen schon in einigen Bereichen, es klappte auch. Probierte, eine CC-Mailadresse einzugeben und es gelang, uff.... ;-)

Dummy ist nicht die Beispieldatei, sondern ich, seufz.
Werde es wohl nie schaufeln, wie man mit AS Long, Integer, String usw. so umgeht, dass was vernünftiges rauskommt. Tauge anscheinend nur mehr für Sandkastenspiele ;-)
Also Rückkehr in die Vergangenheit, denn inzwischen steuere ich schon auf den 57er zu.

Ich hätte noch viele Wünsche, die Excel und VBA-Code betreffen, doch jedes Mal hier darum betteln, wäre doch zu lästig, Gibt es nicht irgendwo vorgefertigte Makros mit VBA-Code, die man nur abzuändern braucht? Natürlich auch leicht zu verstehen, sonst könnt ich gleich mit Hinterindisch als Sprachstudium anfangen ... :-)

Kannst mir diesbezüglich vielleicht noch mit einem Tipp aushelfen?
Vielen Dank nochmals!

LG, Mandy

Answer 2

Hi Mandy,

ich habe den Code noch ein wenig kommentiert, so wird vielleicht einiges besser verständlich. Die relevante Zeile für das Ermitteln des Toners und Aufrufen des Mail-Makros habe ich außerdem mit ******* markiert.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngLetzte As Long ' Variable für letzte belegte Zeile
Dim intLetzte As Integer ' Variable für letzte belegte Spalte
Dim strFrage As String ' Variable für Abfrage
' letzte belegte Zeile in Spalte A ermitteln
lngLetzte = IIf(IsEmpty(Cells(Rows.Count, 1)), Cells(Rows.Count, 1).End(xlUp).Row, Rows.Count)
' letzte belegte Spalte in Zeile 1 ermitteln
intLetzte = IIf(IsEmpty(Cells(1, Columns.Count)), Cells(1, Columns.Count).End(xlToLeft).Column, Columns.Count)
' Eingabe erfolgt im Bereich Zeile6/Spalte3 (C6) bis letzte Zeile/letzte Spalte
If Not Intersect(Target, Range(Cells(6, 3), Cells(lngLetzte, intLetzte))) Is Nothing Then
' es wurde nur in 1 Zelle etwas eignetragen
If Target.Count = 1 Then
' Inhalt Zelle(Zeile3, Eingabe-Spalte) ist < 4
If Cells(3, Target.Column) < 4 Then
' Abfrage
strFrage = MsgBox("Bestellung auslösen?", vbYesNo + vbQuestion, "Tonerbestellung")
' Frage mit "Ja" beantwortet
If strFrage = vbYes Then
' ********************************************
' Prozedur MailSenden aufrufen mit Inhalt aus Zelle(Zeile1, Eingabe-Spalte) für Tonerbezeichnung
MailSenden Cells(1, Target.Column).Value
' ********************************************
End If
End If
End If
End If
End Sub

Das "Zauberwort" ist Target.Column - also die Ziel-Spalte (bzw. Eingabe-Spalte).

Ein Forum ist doch dazu da, dass man Fragen stellt - also mach dir absolut keine Gedanken, sie alle hier zu stellen. Du belästigst damit niemanden und jeder Helfer wird dir gerne bei der Lösung deiner Probleme und beantwortung deiner Fragen zur Seite stehen.

"Vorgefertigte" Makros gibt es sicher viele - dazu musst du dich nur in den Excel-Foren und auf den verschiedenen Homepages/Webseiten umschauen. Natürlich gibt es auch ausreichend Literatur zu VBA mit Code-Beispielen, die man als Grundlage verwenden kann - die sind dann aber meist nicht gerade billig.

Bis später,
Karin

Answer 3

Hallo Karion,

Echt toll, deine Erklärung und vielen Dank auch, dass ich weiterhin Fragen hier stellen kann.

Werde versuchen, nicht allzu lästig zu werden. Vielleicht aber ist es für manche auch ein Leckerbissen, bei einem Problem zu helfen, von dem man noch nichts hörte, bzw. interessant findet.

In diesem Sinne auf ein baldiges und fröhliches Wiederschreiben.

Cheers, Mandy

Answer 4

Hallo Karin,
ich finde deine Lösung in Antwort 12 auch super,
vielleicht ist für Mandy die Restmenge der Toner in den E-Mails auch noch interessant.

MailSenden Cells(1, Target.Column).Value & vbLf & vbLf & "Restmenge" & _
" " & Cells(3, Target.Column).Value & " " & "St."

Gruß
fedjo

Answer 5

Hallo Karin,


Ich bin jetzt im Büro und versuche, den VBA-Code entsprechend abzuändern.

Leider gelingt es mir nicht, seufz....
Die Datei, die ich von daheim schickte, ist um einige Zeilen größer.
Was mach ich eigentlich falsch und was müsste ich ändern?

Vielen Dank noch an Fedjo für den Tipp mit der Restmenge!

Restmenge kann ich noch nicht eingeben, da ich nicht weiß, wo und an welcher Stelle.
Könntest du bitte so lieb sein und anhand der Originaldatei, die ich jetzt uploade, nochmals nachschauen unf ggf. den Code ändern?

Ich könnte dann vergleichen mit der anderen Beispieldatei und sehen, wo man etwas verändert.



www.file-upload.net/download-4746364/Tonerwechsel.xls.html

LG, Mandy

Answer 6

Hi Mandy,

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngLetzte As Long ' Variable für letzte belegte Zeile
Dim intLetzte As Integer ' Variable für letzte belegte Spalte
Dim strFrage As String ' Variable für Abfrage
' letzte belegte Zeile in Spalte A ermitteln
lngLetzte = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
' letzte belegte Spalte in Zeile 1 ermitteln
intLetzte = IIf(IsEmpty(Cells(1, Columns.Count)), Cells(1, Columns.Count).End(xlToLeft).Column, Columns.Count)
' Eingabe erfolgt im Bereich Zeile6/Spalte3 (C6) bis letzte Zeile/letzte Spalte
If Not Intersect(Target, Range(Cells(6, 3), Cells(lngLetzte, intLetzte))) Is Nothing Then
' es wurde nur in 1 Zelle etwas eignetragen
If Target.Count = 1 Then
' Inhalt Zelle(Zeile5, Eingabe-Spalte) ist < 4
If Cells(5, Target.Column) < 4 Then
' Abfrage
strFrage = MsgBox("Bestellung auslösen?", vbYesNo + vbQuestion, "Tonerbestellung")
' Frage mit "Ja" beantwortet
If strFrage = vbYes Then
' ********************************************
' Prozedur MailSenden aufrufen mit Inhalt aus Zelle(Zeile1, Eingabe-Spalte) für Tonerbezeichnung
MailSenden Cells(1, Target.Column).Value
' ********************************************
End If
End If
End If
End If
End Sub

Ich würde dir empfehlen, die Toner-Bezeichnungen in Zeile1 nicht mit Leerzeichen auf die Zeilen zu verteilen sondern einfach mittels Alt+Enter jeweils einen Zeilenumbruch zu erzeugen. Andernfalls wird der Zellinhalt in der Mail fortlaufend in 1 Zeile und nicht in 3 Zeilen untereinander ausgegeben.

Bis später,
Karin

Answer 7

Toll und superdufte für die prompte Hilfe, Karin!

Herzlichen Dank auch für die ausführliche Erklärung dazu!

LG, Mandy