LoNo Mail per Makro aus Excel

Computer: Software & Programmierung Visual Basic
#1

Hallo alle zusammen.

Erst einmal, ein großes Lob an das Forum, habe bis dato sehr sehr viele Lösungen für meine Probleme gefunden, ohne einen Beitrag erstellen zu müssen. Daher erst meine jetzige Registrierung

Bei meinem jetzigen Problem komme ich jedoch leider nicht weiter.

Vom Prinzip her, denke ich, dürfte es nicht kompliziert sein, da bei mir jedoch VBA alles learning by doing ist, und das doing leider nicht so häufig vorkommt, bekomme ich es einfach nicht hin :confused:

Folgendes:

Ich habe eine Excel Tabelle, die mehrere Arbeitsblätter enthält.
Für meine Abfrage ist jedoch immer nur das aktive Arbeitsblatt interessant.
Das Makro soll nun folgendes bewerkstelligen:

Eine Spalte der Tabelle enthält einen Status (open, done, postponed) und eine Spalte eine e-Mail Adresse (als Text). Weitere Spalten enthalten weitere Daten, jedoch sind für das Makro nur die beiden Spalten interessant. Ein „Datensatz“ entspricht sozusagen einer Zeile. Jetzt würde ich gerne, dass ich über das (oder die )Makro(s) folgendes passiert:

Makro open --> Alle Zeilen, wo in der Spalte „open“ steht UND dieselbe E-Mail Adresse steht, sollen in eine E-Mail gepackt werden mit der jeweiligen E-Mail direkt als Adressaten. Der Betreff kann dabei offen bleiben, genau wie der Body Text. Dies Soll für jede E-Mail Adresse passieren. Sprich, wenn dort 5 verschiedene E-Mails stehen, und jede der Personen hat 3 offene („open“) Aufträge, sollen insgesamt 5 neue E-Mails geöffnet werden, wo in jeder die offenen Projekte der dazugehörigen E-Mail reinkopiert wurden.

Makro Postponed --> Dasselbe wie bei open, nur lediglich bei der Spalte „Status“ das Wort „postponed“

Makro not done --> die beiden oberen addiert, sprich alle wo NICHT „done“ in der Status Spalte steht.

Meine jetzige Lösung ist nicht zufriedenstellen, da ich die Tabelle per Hand filtere, die E-Mail Adresse händich eingeben muss, und auch den Kopiervorgang nur so realisiere, dass ich das Arbeitsblatt in gefilterter Ansicht an die E-Mail anghänge. Die Tabelle soll auch an die E-Mail abgehängt werden, das habe ich aber bereits hinbekommen, sprich das kann ich selber bewerkstelligen
Da diese Tabelle sich aufblähen wird und bald mehrere hundert Einträge (respektive Zeilen) haben wird und dementspricht auch sehr viele Kombinationen von E-Mail Adressen und Status, wäre mir ein Makro wie oben beschrieben halt sehr hilfreich.

Ich hoffe mein Problem ist irgendwie verständlich, aber ich denke, ich habe es ganz gut beschrieben.

Ich wäre für jeden Lösungsvorschlag dankbar

MfG
Jackosis / Ralf

#2

Makro open --> Alle Zeilen, wo in der Spalte „open“ steht UND
dieselbe E-Mail Adresse steht, sollen in eine E-Mail gepackt
werden mit der jeweiligen E-Mail direkt als Adressaten. Der
Betreff kann dabei offen bleiben, genau wie der Body Text.
Dies Soll für jede E-Mail Adresse passieren. Sprich, wenn dort
5 verschiedene E-Mails stehen, und jede der Personen hat 3
offene („open“) Aufträge, sollen insgesamt 5 neue E-Mails
geöffnet werden, wo in jeder die offenen Projekte der
dazugehörigen E-Mail reinkopiert wurden.

Hallo Ralf,

du willst da mehrere Zeilen in eine Email schreiben lassen aber Body Text soll leer bleiben genauso wie Betreff!?
Wie soll das gehen?
Oder soll das als Text-Datei oder xls-Datei als Anhang mitgeschickt werden?

Gruß
Reinhard

#3

Hallo Reinhard.

Sorry, das war vllt unglücklich formuliert :smile:

Es muss ausser den Zeilen nichts in den Body Text, jedenfalls nichts vorgefertigtes.

Im Body Sollen quasi die Zeilen rein, und im Anhang eine neue Excel Datei, die nur das betreffende Arbeitsblatt beinhaltet (so wie es mein bisheriges Skript auch tut):

Sub Worksheet_senden()

Dim strTabelle As String ’ Variable für den Tabellennamen
Dim wsTabelle As Worksheet ’ Variable für die Tabelle als Objekt
Dim strBetreff As String ’ Variable für den Betreff
Dim email As String

’ Tabelle2 (CM) als Standard festlegen
strTabelle = „CM“

’ Abfrage des Betreffs
strBetreff = InputBox(„Geben Sie die Betreff-Zeile ein“)

’ Abfrage der E-Mail
email = InputBox(„Geben Sie bitte die gewünschte E-Mail Adresse ein“)

’ Name der zu versendenen Tabelle abfragen
strTabelle = InputBox(„Welches Blatt möchten Sie senden?“ & vbCrLf & _
vbCrLf & „Bitte den Tabellennamen eingeben“, , strTabelle)

’ kein Abbruch der Eingabe
If strTabelle „“ Then
’ Schleife über alle Arbeitsblätter
For Each wsTabelle In ThisWorkbook.Sheets
’ Name der Tabelle entspricht dem der zu versendenen Tabelle
If wsTabelle.Name = strTabelle Then
’ Bildschirmaktualisierung aus
Application.ScreenUpdating = False
’ Tabelle komplett kopieren
Sheets(strTabelle).Copy
’ aktive Arbeitsmappe mit Mailbenachrichtigung aus der Variable strBetreff versenden
ActiveWorkbook.SendMail email, strBetreff
’ aktive Arbeitsmappe schließen ohne Speichern
ActiveWorkbook.Close False
’ Bildschirmaktualisierung ein
Application.ScreenUpdating = True
’ Schleife verlassen
Exit For
Else
’ Tabelle mit dem eingegebenen Namen ist nicht vorhanden
If wsTabelle.Name = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name Then MsgBox „Diese Tabelle gibt es nicht“
End If
Next wsTabelle
End If
End Sub

MfG
Ralf

#4

Es muss ausser den Zeilen nichts in den Body Text, jedenfalls
nichts vorgefertigtes.

Im Body Sollen quasi die Zeilen rein, und im Anhang eine neue
Excel Datei, die nur das betreffende Arbeitsblatt beinhaltet
(so wie es mein bisheriges Skript auch tut):

Hallo Ralf,

es tut mir leid, ich habe privaten Stress und irgendwie keinen Kopp frei.

Wenn du den von dir gezeigten Code selbst erstellt hast oder ihn zumindest in wesentlichen Teilen verstanden hast, so wirst du hier garantiert fündig:

http://www.rondebruin.nl/sendmail.htm

Wenn nicht melde dich gerne nochmal und beschreib wo’s hängt o.ä.

Gruß
Reinhard

#5

Hallo.
Danke dir. Werde mir jetzt mal die Seite anschauen, hatte am Wochenende keine Zeit dafür. Aber jetzt sitze ich wieder im Büro und habe „das Problem“ vor mir :smile:

#6

Ok.
Habe jetzt wieder 2 Tage darum rumgebastelt, aber irgendwie schein ich auf dem Schlauch zu stehen. Deshalb versuche ich einen anderen Ansatz.

Was ich versucht habe, ist folgendes:

Eine Msg Box zu öffnen, wo Der Nutzer die Auswahl treffen kann, welche Daten er angezeigt haben will. Die Auswahl in dieser Box sollte sich auf die Spalte L (oder 12) beziehen. Dort stehen zwar im Moment nur 3 verschiedene Einträge, aber möglicherweise wird es ja mal erweitert.
Nach dieser Filterung (sprich die Zeilen, in denen die Asuwahl aus der 1. MsgBox nicht enthalten sind sollen ausgeblendet werden), sollte eine weitere MsgBox mit der Auswahl der JETZT noch sichtbaren Einträge aus Spalte i (oder 9) kommen. Dort stehen die jeweiligen Ansprechpartner bzw ihre E-Mail Adressen drin. Nach diesen 2 aufeinanderfolgenden Filtern soll alles, was jetzt noch sichtbar ist als ein neues Arbeitsblatt an eine Lotus Notes Mail angehängt werden. Die Mail Adresse soll automatisch aus Spalte i (oder 9) übernommen werden, da dort ja nach der Filterung in jeder Zeile nur noch immer dieselbe E-Mail Adresse steht.

Die Abfrage des Betreffs der Mail hatte ich ja vorher schon, das is kein Problem.

Ich kann mir nicht vorstellen, dass es so kompliziert sein kann, aber wirklich weiter komme ich auch nicht :frowning:

Hat jmd vllt einen guten Ansatz für mich?

Von meiner bisherigen Programmiererfahrung, die sich jedoch auf OOP bezieht, ist mein geschildertes Problem eigentlich mit 2 bis 3 Schleifen abzuhandeln und sollte kein großer Akt sein … aber als VBA Neuling isses scheinbar doch kompliziertert :confused:

Ich hoffe jmd kann mir helfen oder mir wenigstens auf die Sprünge helfen.

MfG
Ralf

#7

Habe jetzt wieder 2 Tage darum rumgebastelt, aber irgendwie
schein ich auf dem Schlauch zu stehen. Deshalb versuche ich
einen anderen Ansatz.

Hallo Ralf,

speichere deine Mappe unter einem anderen namen ab.
schreib mal in die oberste Zelle der Spalte wo die emailadressen stehen rein
Email1
Dann klick auf die rechte untere Ecke der markierten Zelle und zieh das nach unten.
Jetzt sind deine Adressen anonymisiert.

Genauso verfährst du mit anderen Spalten wo intimme Daten stehen.
Und wir brauchen keine 10.000 Zeilen, 30-50 reichen

Dann lade diese Mappe hoch, mit bisherigen makros usw.
Alles was nicht zum Problem gehört, unwichtige Blätter löschst du.

Hochladen z.B. mit fileupload, s. FAQ:2606

Gruß
Reinhard

#8

Hallo.
Das werde ich machen, sobald ich wieder im Büro bin. Bin den Rest der Woche nicht dort :smile:

In der ganzen Mappe stehen aber keine vertraulichen Daten, von daher ist es auch kein Problem.
Es sind Testdaten, die ich ja extra dazu angelegt habe.

Schonmal tausend Dank für die Mühe soweit :smile:

MfG
Ralf

#9

So.

Wieder im Büro und hier nun die Datei:

http://www.file-upload.net/download-3469074/VBA-Skri…

Ich hoffe es wird dadurch ersichtlich.

MfG
Ralf

#10

http://www.file-upload.net/download-3469074/VBA-Skri…

Hallo Ralf,

teste mal den folgenden Code auf deinem PC. Welche Excelversione hast du?

Bei meinem XL 2000 klappt der Spezialfilter nicht, kommt ne Fehlermeldung. In XL 2007 klappt der Code.
Das kann aber an meinem XL 2000 liegen.
Oder an was auch immer k.A.

Setze im VB-Editor mit F9 oder per Maus einen Haltepunkt bei:
Next Z
dann drücke jeweils F5, schau dir dann jeweils das neu aungelegte Blatt an ob da nacheinenader das Gewünschte kommt was dann später der Inhalt der mails sein soll.

Gruß
Reinhard

Option Explicit

Sub Mailen()
Dim Zei1 As Long, wks1 As Worksheet, wks2 As Worksheet, wkb As Workbook
Dim Zei2 As Long, colC As New Collection, colPostp As New Collection
Dim Spa1 As Long, Such, S As Integer, Z As Long
Such = Array("open", "postponed")
Worksheets.Add after:=Worksheets(Worksheets.Count)
Set wks2 = ActiveSheet
Set wks1 = Worksheets("CM")
With wks1
 Zei1 = .Cells(Rows.Count, 9).End(xlUp).Row
 Spa1 = .Cells(1, Columns.Count).End(xlToLeft).Column
 .Range(.Cells(1, 1), .Cells(1, Spa1)).Copy Destination:=wks2.Cells(1, 30)
 For S = 0 To 1
 wks2.Cells(2, 41).Value = Such(S)
 For Z = 2 To Zei1
 If .Cells(Z, 12).Value = Such(S) Then
 On Error Resume Next
 colC.Add key:=.Cells(Z, 9).Value, Item:=.Cells(Z, 9).Value
 On Error GoTo 0
 End If
 Next Z
 For Z = 1 To colC.Count
 wks2.Range("A:X").Clear
 wks2.Cells(2, 38).Value = colC(Z)
 .Range(.Cells(1, 1), .Cells(Zei1, Spa1)).AdvancedFilter Action:=xlFilterCopy, \_
 CriteriaRange:=wks2.Range(wks2.Cells(1, 30), wks2.Cells(2, 29 + Spa1)), \_
 CopyToRange:=wks2.Range("A1"), Unique:=False
 wks2.Range("A:X").WrapText = False
 wks2.Range("A:X").EntireColumn.AutoFit
 Next Z
 Next S
End With
End Sub
#11

Hallo Reinhard.

Ja das klappt super. Genial :smile:
Er zeigt zwar eine Spalte zuviel an (Spalte O , die Tage), aber das is sicherlich kein Ding, diese dann auszulassen.

Jedoch ist es im Moment so, dass er ohne wenn und aber alles nacheinander durchrasselt, aber ich denke, deshalb sollte ich es ja auch erstmal testen, mit dem Haltepunkt, oder ?

Jetzt muss das Ganze nur noch so passieren, dass er mir zu jeder zugehörigen E-Mail auch eine Mail öffnet und die Werte dort überträgt bzw dieses dann neu gestaltete Tabellenblatt an diese Mail anhängt. Kann man auch den Anhang benennen. Weil so wie ich es im Moment habe, heissen die immer stumpf „Anhang xy.xls“? Ob am Anfang noch eine Abfrage per Textbox rein soll, ob nun nur die postponed, open und/oder done - Elemente angezeigt werden sollen, muss ich nachfragen.

Ich nutze hier auf dem Firmen PC Excel 2003. Zu Hause nutze ich Office2011:Mac , von daher ist es immer etwas kompliziert, da zu Hause dran zu basteln.

Schonmal tausend Dank, das bringt mich schon um Welten weiter. Ich hatte auch überlegt es über ein Array laufen zu lassen, aber die Realisierung in Excel, war mir nicht klar … bin halt Anfänger in dem Ganzen. Zum Glück kann ich aber deinen Code weitesgehend nachvollziehen, und dadurch lernt man ja auch Recht viel.

Viele Grüße und einen schönen Feiertag morgen. Bis kommenden Montag … wenn ich wieder im Büro bin und hoffentlich weiterbasteln kann :smile:

Ralf

#12

Soderle.
Nachdem meine Klausur und Lernphase nun vorbei ist, bin ich auch wieder im Büro und konnte mich weiter dem Skript widmen.
Wie gesagt, es klappt alles soweit ganz gut, nur dass ich jetzt den Sprung nicht hinbekomme, dass er das neu angelegte Tabellenblatt als Anlage an eine neue EMail packt, die direkt den Adressaten drin hat, nach welcher sortiert wurde.
Ich habe versucht meine vorigen Ansatz einfach nach einem Durchlauf der Schleife anzusetzen, erhalte jedoch immer Fehlermeldungen, die ich nicht interpretieren kann. Lotus Notes öffnet sich zwar, aber es wird keine neue Mail geöffnet, keine E-Mail Adresser eingetragen und dementsprechen auch kein Anhang an die nicht vorhandene E-Mail gepackt :smile:
Kann ich überhaupt nach jedem Schleifendurchlauf einen Befehl aufrufen, der mir eine neue Mail erstellt? Kann ich das ggf noch beschränken, mit einer vorherigen Abfrage, bei der ich ggf abfrage, nach welchen Kriterien nur gesucht werden soll?

Ich hoffe ich bekomme wieder so erstklassige Hilfe :smile:

LG
Ralf