Frage stellen

Aus Tabellenblatt E-Mails versenden

563 Aufrufe
Gefragt in Tabellenkalkulation von peters Mitglied (522 Punkte)
Hallo zusammen,

ich habe eine Kundendatei in Excel angelegt. Sehr rudimentär und einfach.

Nun möchte ich meinen Kunden E-Mails zusenden und das Tabellenblatt mit den Kundendaten dafür nutzen.
Ich stelle mir vor, dass ich je Kunden 2 Felder nutze.
Sagen wir einmal in Spalte A die E-Mail-Adresse und Spalte B den Wert "j" für JA und "n" für NEIN
(nicht jeder möchte Newsletter bekommen).

Nun würde ich gerne eine Routine starten, die jede einzelne Zeile ausliest, bei "n" in Spalte B oder leerem Feld A weiterspringt und ansonsten dem Adressaten eine E-Mail mit einem PDF im Anhang sendet.

Einen Text für den Body der E-Mail möchte ich in einer Textdateil hinterlegen.

Und entweder soll der Text neutral gehalten werden ohne persönliche Anrede, oder die ebenfalls in der Tabelle hinterlegten Ansprechpartner genutzt werden.

Hat da jemand eine Idee zu?

Gruß

Peter

30 Antworten

0 Punkte
Beantwortet von m-o Profi (22.9k Punkte)

Hallo Peter,

welches E-Mail-Programm nutzt du?

Mit einem entsprechenden VBA-Code kann man das lösen. Den Text der E-Mail kannst du auch direkt in den Code schreiben (wenn er z.B. immer gleich sein soll).

Wenn Anrede und Nachname in der Tabelle enthalten sind, kann man die E-Mail auch personalisieren.

Dies könnte dann so aussehen:

Sehr geehrter Herr Peter,

in der Anlage erhalten Sie unseren neuesten Newsletter.

Mit freundlichen Grüßen

Heißt die Anlage immer gleich und liegt immer am gleichen Ort oder ändert sich z.B. der Name. Dann könnte man den Namen und ggf. auch den Pfad z.B. in einer Zelle deiner Excel-Datei hinterlegen.

Gruß

M.O.

0 Punkte
Beantwortet von peters Mitglied (522 Punkte)
Hallo M.O.,

da das Ganze aus Office erfolgen soll, nutze ich dafür Outlook.

Der Text soll nicht immer gleich sein, weshalb ich Textkorpus aus einer Textdatei auslesen möchte (oder alternativ aus einem Excal-Sheet, je nachdem, was einfacher ist; das wäre evtl ja auch formatierbar).

Grundsätzlich heißt die Anlage immer gleich, obschon ich überlege, sie mit Tagesdatum in einfacher Form zu benennen (z.B. "2024-06-27.pdf"). Zum einen ließe sich das im Code ja so hinterlgen, dass nach der mit dem aktuellen Tagesdatum gesucht wird, zum anderen wäre nachvollziehbar, was an welchem Tag verschickt wurde.

Gruß

Peter
0 Punkte
Beantwortet von m-o Profi (22.9k Punkte)

Hallo Peter,

anbei mal ein Code für dich zum Testen:

Sub newsletter()
'Achtung: Für dieses Makro müssen im VBA-Editor unter Extras - Verweise bei Microsoft Outlook XX.0 Object Library entsprechende Haken gesetzt werden

Dim olApp As Object
Dim WsShell
Dim lngZeile As Long
Dim lngLetzte As Long
Dim strBody As String
Dim strAnlage As String
Dim oFileApp As Object: Set oFileApp = CreateObject("Scripting.FilesystemObject")
Dim oStream As Object
Dim oFile As Object

'Outlook definieren
Set olApp = CreateObject("Outlook.Application")

'Mailtext aus Textdatei "Mailtext" einlesen - Name und Pfad anpassen
'UTF8-Text mit Sonderzeichen einlesen
Set oStream = CreateObject("ADODB.Stream")
oStream.Charset = "utf-8"
oStream.Open
oStream.LoadFromFile ("C:/Test/Mailtext.txt")
strBody = oStream.ReadText()
oStream.Close
Set oStream = Nothing
Set oFileApp = Nothing

'Name für Newsletter generieren
'ggf. führende Nullen für Monat und Tag hinzufügen
If Month(Now) < 10 Then
  strNewsletter = Year(Now) & "-0" & Month(Now)
 Else
  strNewsletter = Year(Now) & "-" & Month(Now)
End If

If Day(Now) < 10 Then
  strNewsletter = strNewsletter & "-0" & Day(Now) & ".pdf"
 Else
  strNewsletter = strNewsletter & "-" & Day(Now) & ".pdf"
End If

'Pfad für Newsletter ergänzen - Pfad anpassen
strNewsletter = "C:\Test\" & strNewsletter

'alle Zeilen der aktuellen Tabelle durchlaufen
lngLetzte = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

For lngZeile = 1 To lngLetzte

  'prüfen, ob in Spalte B ein j steht
  'alles in Kleinschrift prüfen
  If LCase(Cells(lngZeile, 2).Value) = "j" Then
    'falls ein Newsletter gewünscht,
     'prüfen, ob etwas in Spalte A steht
     If Cells(lngZeile, 1) <> "" Then
       'E-Mail generieren
         With olApp.CreateItem(0)
          .to = Cells(lngZeile, 1) 'Empfänger"
          .Subject = "Newsletter" 'Betreff
          .Body = strBody 'Nachricht
          .ReadReceiptRequested = False 'Lesebestätigung aus
          .Attachments.Add strNewsletter    'Anhang        '
          .Display 'Email anzeigen
          'und hier wird die Nachricht gesendet
          '.Send
        End With
     End If
  End If
Next lngZeile

Set olApp = Nothing

End Sub

Beachte bitte, dass für das Makro eine Verweis auf Outlook gesetzt werden muss. XX steht dabei für die aktuelle Outlook-Version.

Die Pfade für den Mailtext und den Anhang musst du natürlich auf deine Bedürfnisse anpassen.

Ich habe das Senden erst einmal mit einem Hochkomma deaktiviert. So bekommst du die Mails angezeigt und kannst nachsehen, ob alles so klappt wie du willst. Sollen die Mails gesendet werden, dann lösche das Hochkomma vor .Send.

Gruß

M.O.

0 Punkte
Beantwortet von peters Mitglied (522 Punkte)
Hallo M.O.,

wow, vielen Dank dafür!

Ich werde das erst Anfang der Woche testen können, gebe aber auf jeden Fall Rückmeldung.

Dir ein schönes Wochenende.

Gruß

Peter
0 Punkte
Beantwortet von peters Mitglied (522 Punkte)

Hallo M.O.,

kannst Du mit der Meldung etwas anfangen?

Und wenn ich die Spalte "A" gegen Spalte "V" austauschen möchte, an welcher Stelle schreibe ich die "22"?

Und statt Spalte "B" möchte ich Spalte "J" nutzen, dann müsste vo die "10" hin?

Grüße

Peter

0 Punkte
Beantwortet von m-o Profi (22.9k Punkte)

Hallo Peter,

welche Meldung ? wink

Also prüfen, ob ein Newsletter gewünscht ist - in Spalte J ein "j" vorhanden? - dann die entsprechende Zeile im Code so ändern:

If LCase(Cells(lngZeile, 10).Value) = "j" Then

Und E-Mail-Adresse aus Spalte V einfügen, dann so:

.to = Cells(lngZeile, 22) 'Empfänger"

Gruß

M.O.

0 Punkte
Beantwortet von peters Mitglied (522 Punkte)

Hallo M.O.,

da hat vorhin das Hochladen nicht geklappt, damit tue ich mich hier etwas schwer,

Diese Meldung hier wird gezeigt, wenn ich das Makro laufen lasse (die beiden Pfade hatte ich bereits angepasst).

Die Meldung erscheint beim Abarbeiten der Schleife. Wenn ich es richtig erkenne, beim 2. Durchlaufen der Schleife und Erreichen des Auslesens der E-Mail-Adresse.

Gruß

Peter

0 Punkte
Beantwortet von m-o Profi (22.9k Punkte)

Hallo Peter,

habe gerade gesehen, dass ich nicht alle zu ändernden Zeilen berücksichtigt hatte. Hier das angepasste Makro:

Sub newsletter()
'Achtung: Für dieses Makro müssen im VBA-Editor unter Extras - Verweise bei Microsoft Outlook XX.0 Object Library entsprechende Haken gesetzt werden

Dim olApp As Object
Dim WsShell
Dim lngZeile As Long
Dim lngLetzte As Long
Dim strBody As String
Dim strAnlage As String
Dim oFileApp As Object: Set oFileApp = CreateObject("Scripting.FilesystemObject")
Dim oStream As Object
Dim oFile As Object

'Outlook definieren
Set olApp = CreateObject("Outlook.Application")

'Mailtext aus Textdatei "Mailtext" einlesen - Name und Pfad anpassen
'UTF8-Text mit Sonderzeichen einlesen
Set oStream = CreateObject("ADODB.Stream")
oStream.Charset = "utf-8"
oStream.Open
oStream.LoadFromFile ("C:/Test/Mailtext.txt")
strBody = oStream.ReadText()
oStream.Close
Set oStream = Nothing
Set oFileApp = Nothing

'Name für Newsletter generieren
'ggf. führende Nullen für Monat und Tag hinzufügen
If Month(Now) < 10 Then
  strNewsletter = Year(Now) & "-0" & Month(Now)
 Else
  strNewsletter = Year(Now) & "-" & Month(Now)
End If

If Day(Now) < 10 Then
  strNewsletter = strNewsletter & "-0" & Day(Now) & ".pdf"
 Else
  strNewsletter = strNewsletter & "-" & Day(Now) & ".pdf"
End If

'Pfad für Newsletter ergänzen - Pfad anpassen
strNewsletter = "C:\Test\" & strNewsletter

'alle Zeilen der aktuellen Tabelle durchlaufen, dazu letzte Zeile in Spalte V ermitteln
lngLetzte = ActiveSheet.Cells(Rows.Count, 22).End(xlUp).Row

For lngZeile = 1 To lngLetzte

  'prüfen, ob in Spalte J ein j steht
  'alles in Kleinschrift prüfen
  If LCase(Cells(lngZeile, 10).Value) = "j" Then
    'falls ein Newsletter gewünscht,
     'prüfen, ob etwas in Spalte V (E-Mail-Adresse) steht
     If Cells(lngZeile, 22) <> "" Then
       'E-Mail generieren
         With olApp.CreateItem(0)
          .to = Cells(lngZeile, 22) 'Empfänger"
          .Subject = "Newsletter" 'Betreff
          .Body = strBody 'Nachricht
          .ReadReceiptRequested = False 'Lesebestätigung aus
          .Attachments.Add strNewsletter    'Anhang
          .Display 'Email anzeigen
          'und hier wird die Nachricht gesendet
          '.Send
        End With
     End If
  End If
Next lngZeile

Set olApp = Nothing

End Sub

Schau mal, ob das Makro jetzt richtig funktioniert.

Gruß

M.O.

0 Punkte
Beantwortet von peters Mitglied (522 Punkte)
Hallo M.O.,

das scheint eine Zangengeburt zu werden ...  ;-)
Also, ich habe die Pfade angepasst, die "10" und "22" getauscht (in Spalte J steht die E-Mail-Adresse, in V der NL-Wunsch) und ich bekomme wieder die gleiche Meldung, die ich gestern als Bild gepostet habe (auch exakt die gleichen Zahlenwerte).

Der Fehler tritt in der Schleife auf, nachem Schritt

          .To = Cells(lngZeile, 10) 'Empfänger"

gemacht wurde. Also diese Zeile springt er noch an ohne zu meckern und dann kommt als nächstes dieses Fehlerfenster.

Wo oder was könnte ich hier falsch machen?

Und eine Frage am Rande:
Lässt sich die Abfrage in der Schleife nach dem NL-Wunsch nicht-case-sensitiv gestalten, so dass es egal ist,
ob ein "j" oder ein "J" eingetragen ist?

Gruß

Peter
0 Punkte
Beantwortet von
Großes J sollte auch funktionieren
Seite:
...