Mailversand aus Excel mit Subject und Body

Computer: Software & Programmierung Office Software
#1

Hallo,

ich habe hier eine grosse Bewerber-DB als Excel Tabelle vor mir.

  • In der Spalte A wird der Name eingetragen,
  • automatisch wird dann dann der Name aus Spalte A in Vor- und Nachname in Spalte B und C getrennt
  • Spalte D = email Adresse
  • Spalte E - K = diverse Spalten fuer Skills (einfach ein x in der entsprechenden Spalte machen)

All diese Bewerber sollen nun angeschrieben werden.
Je nach Skills variiert jedoch der Betreff und auch der Body. (Nach dem Motto: WENN( SpalteE +„x“; Body A,WENN(SpalteE =„x“,BodyB, WENN(…)))
Um das nicht immer manuell eingeben zu muessen, wollte ich das gerne mit Excel machen.
Bisher verwende ich die folgende Formel: (habe die mal vereinfacht, damit man erkennen kann, wie ich die Mail zusammenbaue …)
=Hyperlink(verketten(„mailto:“,Zelle mit Emailadresse, ?subject=, Zelle mit entsprechendem Subject, ?body=,VORNAME_DES_EMPFAENGERS, Zelle mit entsprechendem Body))

Eigentlich laeuft das auch super, ABER: oft erhalte ich auch einen #Value!- Fehler.
Ich habe herausgefunden, dass die Funktion =Hyperlink() anscheinend eine Zeichenbegrenzung hat.
Ist der Body nur ca. 1 Zeile, dann klappt das wunderbar, ist der Body Text jedoch laenger (mehr als ca. 180 Zeichen), dann erhalte ich den oben beschrieben Value! Fehler.

Im Internet habe ich schon diverse Moeglichkeiten gesehen, um aus Excel eine Mail per VBA zu erzeugen.
Allerdings habe ich von VBA keine Ahnung.
Waere es denn damit moeglich, laengere Body Texte zu verwenden, so dass ich nicht auf den #Value! Fehler laufe?
Was muesste ich denn beachten, dass ich hinter jeder Zeile, als hinter jedem Bewerber einen Link / Button haette, der mir den entsprechenden Mailto - Link aufruft.
Ich moechte naemlich die Generierung der Mails gerne Manuell aufrufen und manuell absenden, damit ich sie zur Kontrolle nochmal durchlesen kann…

Ach ja,
Tabellenkalkulation: Ich verwende hier Excel 2002 (US-Version)
Mailprogramm: Lotus Notes 6.5.5

P.S.:
Da ich in den USA bin, habe ich leider keine Umaluet - ich hoffe, die „ae“ sind nicht allzu schwer zu lesen…

P.P.S:
Weiss vielleicht auch noch jemand, wie ich einen Zeilenumbruch in meine Hyperlink-Funktion einbauen kann???
Char(10) bzw. Char(13) wird in Notes nicht als Zeilenumbruch erkannt.

Vielen Dank

Alex

#2

Crossposting mit Bild
http://www.kmbuss.de/Excel-CD/Pics/OH_Crossposting_0…
Gruß
Reinhard

#3

Hi,

weil ich die gleiche Frage bei Herber.de im Forum auch gestellt habe?
Wuerde die Antwort ja auch im jeweils anderen Forum veroeffentlichen.
Habe jeweils die Foren, Google und FAQ durchsucht und leider nichts gefunden.
Auch auf xlam.ch habe ich auch keine Limitation fuer =hyperlink()gefunden.

Crossposting wird nicht mehr vorkommen - Hilfe waere trotzdem toll :wink:

Gruss Alex

http://www.kmbuss.de/Excel-CD/Pics/OH_Crossposting_0…
Gruß
Reinhard

#4

Crossposting wird nicht mehr vorkommen - Hilfe waere trotzdem
toll :wink:

Hi Alexander,
ich habe kein Lotus Notes, kann also nichts testen.
Aus http://www.fabalou.com/VBandVBA/lotusnotesmail.asp
habe ich nachfolgenden Code, dort sind noch einige Erläuterungen dazu.

Alt+F11, Einfügen–Modul, Code reinkopieren, Die parameter in „testen“ eingeben, dann, muss nur der Cursor beliebig irgendwo
in dem Code von „Testen“ stehen, auf F5 drücken.
Was passiert denn dann?

Gruß
Reinhard

Sub Testen()
Betr = "Betreff"
PD = "Pfad/Dateiname des Anhangs"
Empf = "Empfänger"
Etext = "Emailtext"
Speichern = True
Call SendNotesMail(Betr, PD, Empf, Etext, Speichern)
End Sub

Public Sub SendNotesMail(Subject As String, Attachment As String, Recipient As String, BodyText As String, SaveIt As Boolean)
'Set up the objects required for Automation into lotus notes
 Dim Maildb As Object 'The mail database
 Dim UserName As String 'The current users notes name
 Dim MailDbName As String 'THe current users notes mail database name
 Dim MailDoc As Object 'The mail document itself
 Dim AttachME As Object 'The attachment richtextfile object
 Dim Session As Object 'The notes session
 Dim EmbedObj As Object 'The embedded object (Attachment)
 'Start a session to notes
 Set Session = CreateObject("Notes.NotesSession")
 'Next line only works with 5.x and above. Replace password with your password
 Session.Initialize("password")
 'Get the sessions username and then calculate the mail file name
 'You may or may not need this as for MailDBname with some systems you
 'can pass an empty string or using above password you can use other mailboxes.
 UserName = Session.UserName
 MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
 'Open the mail database in notes
 Set Maildb = Session.GETDATABASE("", MailDbName)
 If Maildb.ISOPEN = True Then
 'Already open for mail
 Else
 Maildb.OPENMAIL
 End If
 'Set up the new mail document
 Set MailDoc = Maildb.CREATEDOCUMENT
 MailDoc.Form = "Memo"
 MailDoc.sendto = Recipient
 MailDoc.Subject = Subject
 MailDoc.Body = BodyText
 MailDoc.SAVEMESSAGEONSEND = SaveIt
 'Set up the embedded object and attachment and attach it
 If Attachment "" Then
 Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
 Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment, "Attachment")
 MailDoc.CREATERICHTEXTITEM ("Attachment")
 End If
 'Send the document
 MailDoc.PostedDate=Now() 'Gets the mail to appear in the sent items folder
 MailDoc.SEND 0, Recipient
 'Clean Up
 Set Maildb = Nothing
 Set MailDoc = Nothing
 Set AttachME = Nothing
 Set Session = Nothing
 Set EmbedObj = Nothing
End Sub
#5

Hi Reinhard,

vielen Dank erstmal. Leider kenne ich mich mit VBA ueberhaupt nicht aus. Werde mal nen bisschen rumprobieren.
Vielleicht kannst du mir noch einen Tipp geben: Wie verwende ich als Parameter eine Variabel - als Beispiel die Empfaenger EMail Adresse steht in Spalte E - wie gebe ich dem VBA Script dann mit, dass er sich aus der Zeile, in die ich geklickt habe, die entsprechende Empfaenger Adresse aus Spalte E in der entsprechenden Zeile holt - also im VBA eine Referenz auf die Excel Tabelle?
Vielen Dank

Alex

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

#6

vielen Dank erstmal. Leider kenne ich mich mit VBA ueberhaupt
nicht aus. Werde mal nen bisschen rumprobieren.
Vielleicht kannst du mir noch einen Tipp geben: Wie verwende
ich als Parameter eine Variabel - als Beispiel die Empfaenger
EMail Adresse steht in Spalte E - wie gebe ich dem VBA Script
dann mit, dass er sich aus der Zeile, in die ich geklickt
habe, die entsprechende Empfaenger Adresse aus Spalte E in der
entsprechenden Zeile holt - also im VBA eine Referenz auf die
Excel Tabelle?

Hi Alex,
das geht mit dem Eintrag in der „Testen“ so:

Empf=cells(activecell.row,5)

Aber für mich wäre es erstmal wichtiger zu wissen ob es denn überhaupt klappt mit dem Mehlen.
Gruß
reinhard

#7

Hallo Reinhard,

bislang hatte ich leider noch keine Zeit das ausgiebig zu testen.
Bei dem ersten Versuch, habe ich einen Kompilierungsfehler mit den Paramtern erhalten. Könnte mir aber vorstellen, das sih irgendetwas falsch gemacht habe - daher wollte ich mich damit erst ein wenig genauer auseinandersetzen.

Allerdings habe ich aber aus meinem Crossposting eine Info erhalten, warum mein Unterfangen mit den normalen Excel-Funktionen nicht klappt:

[Quote von
Philipp von Wartburg]

  • Die HYPERLINK-Funktion selbst besitzt keine Limitation, aber die aufzurufende Adresse (egal ob http, file, mailto usw.). Diese kann nur bis zu ca. 255 Zeichen lang sein.
  • Die E-Mail-Adresse inklusive aller Argumente wie Subject, CC, Body usw. kann maximal 239 Zeichen lang sein. Zusammen mit den 7 Zeichen für „mailto:“ sind somit 246 Zeichen möglich. Je länger also beispielsweise die E-Mail-Adresse ist, desto kürzer muss der Body sein. Diese Anzahl gilt für Excel 2003, wenn man einen Hyperlink zu einer E-Mail-Adresse einfügt. Bei Excel 97-2002 wird die Anzahl gleich oder zumindest ähnlich sein, da die Limitation generell für mailto-Links gilt.
  • Einen Zeilenumbruch lässt sich mit „%0a“ erreichen (0a ist der Hex-Code von Dec 10, d.h. LF/LineFeed). Beispiel: „body=Hallo Alex%0aDas ist ein Test“.
  • Eine sehr gute Webseite mit VBA-Mail-Lösungen ist www.rondebruin.nl/sendmail.htm.
    [/Quote]

Sobald ich weitere Ergebnisse zum Sendmail aus Excel habe, werde ich das hier schreiben.

Vielen Dank und gruß

Alex

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

#8

Hallo,

ich habe jetzt das mal versucht.
Ich weiß nicht, ob ich etwas falsch gemahct habe - auf jeden Fall habe ich beim Ausführen mit F5 immer einen Fehler erhalten. „Fehler beim Kompilieren: Argumenttyp ByRef unverträglich“

Bestimmt weißt du jetzt sofort was falsch war.
Die Variablendeklarationin in Testen() fehlte:
Ich habe in der Funktion Testen() dann die hinzugefügt (z.B. Dim Betr As String) - danach habe ich dann auch Mails bekommen.

Trotzdem habe ich noch ein paar Fragen:

  1. Die Mail wird sofort gesendet - ich bekomme davon jedoch nichts mit. Kann ich das auch so einstellen, dass die Mail erzeugt wird, ich aber noch den Senden Knopf betätigen muss? Bei der Excel =Hyperlink() Funktion wird die Mail generiert und das Standard Mailprogramm auch aufgerufen - aber die Mail muss manuell gesendet werden. So würde ich das gerne beibehalten!

  2. Gibt es eine Möglichkeit, dass ich in meiner Excel Tabelle, die ich ja erst einmal füllen muss in eine Spalte eine Formel / Button einbaue, auf die ich klicken muss, damit für die aktuelle Zeile (also in meiner Bewerberliste der aktuelle Kandidat) eine Mail erzeugt wird.
    Bislang sieht man in der Spalte nur eine Formel, wenn vorher alle Felder ausgefüllt sind.
    Vielleicht habe ich auch nur ein Verständnisproblem, wie würde man dein Makro denn in der Tabelle starten, um dem Bewerber in Zeile 273 eine Mail schicken möchte.

  3. wie realisiere ich eine normale Excel =Wenn(Bedingung,True,False) Abfrage in VB mit Bezug auf meine Exceltabelle - ein kleines Beispiel dafür wäre auch noch klasse!

Danke für deine Geduld und deine Hinweise.

Viele Grüße und einen schönen Sonntag

Alex

#9

Hi Alexander,

Fall habe ich beim Ausführen mit F5 immer einen Fehler
erhalten. „Fehler beim Kompilieren: Argumenttyp ByRef
unverträglich“

das bezieht sich auf den Aufruf der Sub, schau mal in die Hilfe zu Sub, ist da gut erklärt. Abhilfe ist Umwandlung von ByRef zu ByVal, also so:
Public Sub SendNotesMail(ByVal Subject As String, ByVal Attachment As String, ByVal Recipient…

Die Variablendeklarationin in Testen() fehlte:
Ich habe in der Funktion Testen() dann die hinzugefügt (z.B.
Dim Betr As String) - danach habe ich dann auch Mails
bekommen.

Wenn nicht mit Dim deklariert wird, oder nur
Dim Betr
dann sind die Variablen variant deklariert. Verstehe da jetzt nicht den Zusammenhang mit ByRef. Aber schön dass es funktioniert.

  1. Die Mail wird sofort gesendet - ich bekomme davon jedoch
    nichts mit. Kann ich das auch so einstellen, dass die Mail
    erzeugt wird, ich aber noch den Senden Knopf betätigen muss?

In Lotus kenne ich mich nicht aus. Ein guter workaround wäre wenn du mailen willst dass eine Usrform erscheint die wie die abzuschickende Mail aussieht und da bestätigen musst/kannst.
Bastle bitte eine kleine Beispieltabelle, als Empänger reicht a,b,c, usw, als emailadresse [email protected] usw. beim Body wäre schon wichtig die Größe und Länge zu erkennen.
Dann Alt+F11, Einfügen Userform.
Ggfs, Ansicht Werkzeugsammlung falls die nicht sichtbar ist.
Dann Objekt in der Werkzeugsammlung anklicken und in der UF „aufziehen“. Größen, Positionen anpassen bis es wie ein Emailformular aussieht. Für Das Wort „Betreff“ nimmste ein bezeichnungsfld in das du schon Betreff reinschreiben kannst. Für den eigentlichen Betreffinhalt nimm ein Textfeld. Zum Bestätigen nimm einen CommandButton, dort die Aufschrift über Ansicht—Eigenschaften—Caption die Bezeichnung ändern
Mt F5 sieht du jeweils wie es später rüberkommt.
Lade das dann bei http://www.badongo.com oder sonstwo hoch und poste hier den Link. Dann kann man gezielt auf die Tabelle abgestellt Code schreiben der die UF bei Klick aufruft und mit den Werten füllt.

  1. Gibt es eine Möglichkeit, dass ich in meiner Excel Tabelle,
    die ich ja erst einmal füllen muss in eine Spalte eine
    Formel / Button einbaue, auf die ich klicken muss, damit für
    die aktuelle Zeile (also in meiner Bewerberliste der aktuelle
    Kandidat) eine Mail erzeugt wird.
    Bislang sieht man in der Spalte nur eine Formel, wenn vorher
    alle Felder ausgefüllt sind.
    Vielleicht habe ich auch nur ein Verständnisproblem, wie würde
    man dein Makro denn in der Tabelle starten, um dem Bewerber in
    Zeile 273 eine Mail schicken möchte.

Empf = Range(„G“ & activecell.row) 'Empfänger in Spalte G der Zeile markierten Zelle

  1. wie realisiere ich eine normale Excel
    =Wenn(Bedingung,True,False) Abfrage in VB mit Bezug auf meine
    Exceltabelle - ein kleines Beispiel dafür wäre auch noch
    klasse!

? =Wenn(A1=„x“;„BodyA“;wenn(A1=„y“;„BodyB“;""))

Gruß
Reinhard

#10

zu 1)

  1. Die Mail wird sofort gesendet - ich bekomme davon jedoch
    nichts mit. Kann ich das auch so einstellen, dass die Mail
    erzeugt wird, ich aber noch den Senden Knopf betätigen muss?
    Bei der Excel =Hyperlink() Funktion wird die Mail generiert
    und das Standard Mailprogramm auch aufgerufen - aber die Mail
    muss manuell gesendet werden. So würde ich das gerne
    beibehalten!

Hi Alexander,
ändere mal versuchshalber
MailDoc.SEND 0, Recipient
in
MailDoc.SEND 1, Recipient
oder
MailDoc.SEND -1, Recipient
Gruß
Reinhard

#11

Hallo,

ich habe jetzt mal eine Excel Datei zur Verfügung gestellt.
Ich habe die insgesamt so gelassen, wie ich sie momentan benutze. Am Ende habe ich zwei Spalten (Body, Subject) eingefügt, in denen der entsprechende Text für diesen Eintrag steht.
Einige Spalten sind ausgeblendet. In den überprüfe ich per =vergleich(), ob dieser Wert schon in der Tabelle vorhanden ist:

Der Link zu der Tabelle: http://www.badongo.net/file/618805

Mittlerweile bin ich sogar der Überzeugung, dass es ok ist, wenn die Mail nicht mehr angesehen werden kann! Habe es aber irgendwie nicht hinbekommen, dass anstatt der Funktion =Hyperlink(…)in Spalte X das Macro für diese Zeile aufgerufen wird.
Wäre sehr dankbar, wenn du mir da nochmal den richtigen Denkanstoss geben würdest.

Gruß

Alex