Outlook-E-Mail per Makro in Excel erstellen-Format

Computer: Software & Programmierung Office Software
#1

Hallo zusammen,

ich komme bei meinem Problem nicht mehr weiter und habe schon das halbe Wochenende vergeblich nach einer Lösung gesucht.

Ich habe vor, in Excel mit Hilfe eines Makro eine E-Mail zu erstellen. Der Empfänger der E-Mail, Betreff, Text und Priorität wird aus der Exceltabelle entnommen (sind veränderliche Angaben, die per Formel berechnet werden). Klappt alles auch einwandfrei wie ich es möchte - nur mein Problem ist nun, dass in der erstellten E-Mail die Formatierungen gleich sind (Standard; Arial Schriftgröße 10) und ich absolut nicht weiß, wie ich diese ändern kann. Ich möchte, dass in der E-Mail die erste Zeile von meinem Text fett ist, und dass die letzten beiden Zeilen Schriftgröße 8 haben und blau sind.

Hier mein Makro:

Sub E_Mail_senden()
Quelle = ActiveWorkbook.ActiveSheet.Name
Dim outl, Mail As Object
Set outl = CreateObject(„Outlook.Application“)
Set Mail = outl.CreateItem(0)
Mail.Subject = Sheets(Quelle).Range(„C14“)
Mail.body = Sheets(Quelle).Range(„D25“) & Sheets(Quelle).Range(„B26“) & Sheets(Quelle).Range(„B27“)
Mail.To = Sheets(Quelle).Range(„D4“) & "; " & Sheets(Quelle).Range(„D5“) & "; " & Sheets(Quelle).Range(„D6“)
Mail.CC = Sheets(Quelle).Range(„D7“) & "; " & Sheets(Quelle).Range(„D8“) & "; " & Sheets(Quelle).Range(„D9“)
Mail.BCC = Sheets(Quelle).Range(„D10“) & „; " & Sheets(Quelle).Range(„D11“) & „; " & Sheets(Quelle).Range(„D12“)
'Wichtigkeit Hoch (1 = normal, 0 = niedrig)
Mail.Importance = Sheets(Quelle).Range(„C16“)
'Standardtext
'oder: die aktive Exceldatei als Anhang mitsenden…
'Mail.Attachments.Add ThisWorkbook.FullName
'Mail anzeigen
Mail.Display
'Ein sofortiger Mail-Versand geht in Firmen wegen Sicherheitseinstellungen oft nicht:
'Mail.Send
'aber es gibt eine Lösung mit SendKeys per Windows Scripting Host (Verweis ins VB-Projekt einfügen!):
Dim WshShell
Set WshShell = CreateObject(„WScript.Shell“)
WshShell.AppActivate Mail
'Sendet ein „Alt-S“, Outlook sendet Mail sofort ohne Sicherheitsabfrage:
WshShell.SendKeys (““)
Set Mail = Nothing
Set outl = Nothing
Set WshShell = Nothing
End Sub

Weiß da jemand weiter Vielen Dank für Eure Hilfe!

Gruß
Christian

#2

möchte, dass in der E-Mail die erste Zeile von meinem Text
fett ist, und dass die letzten beiden Zeilen Schriftgröße 8
haben und blau sind.

Soweit so gut. Wie willst du sicherstellen, dass diese Formatierungen auch beim Empfänger der Mail ankommen? E-Mail ist ein pures Text-Medium.

Sobald du in der Mail gestalten willst, bist du entweder darauf angewiesen, dass der Empfänger genau den gleichen Mailclient einsetzt wie du (wirklich genau den gleichen, Outlook x.y ist prinzipiell inkompatibel zu Outlook y.x).

Oder du druckst oder speicherst den Text in ein portables Format, z. B. pdf, und schickst ihn als Anhang.

Gruss
Schorsch

#3

Also kurz zur Erklärung:
Eine E-Mail, die ich sonst 100-fach im Jahr im Jahr genauso schreibe, will ich nun per Mausklick erstellen. Die 5 Angaben, die in der E-Mail immer varrieren, trage ich in die Exceltabelle ein.
Die E-Mail, die ich erstelle, muss auf jeden Fall einige bestimmte Formatierungen haben (Z. Bsp. fett markierte erste Zeile von meinem Text (Body)); ob diese Formatierungen so beim Empfänger ankommen, ist erst einmal zweitrangig!

Vielen Dank.

Gruß
Christian

#4

Hallo Chrsitian,
So als Schuss aus der Hüfte:
ich denke dein Problem wäre eher etwas für einen Serienbriefg in WORD, dort kannst Du eine Email aus einer Excel Datei erstellen. Und Du hast alle formatierungen.
Grüße
Winter

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

#5

So geht´s.
Hier ist eine Lösung, die zudem ein wenig transparenter ausgeführt ist, weil das Outlook-Objekt verwendet wird. Dazu muss man im VBA-Editor auf „Extras/Verweise…“ gehen und dort einen Haken bei „Microsoft Outlook x.y Object Library“ machen. Darüber hinaus würde ich das zu verwendende Arbeitsblatt als Parameter übergeben und die eigentliche Prozedur in ein separates Modul verschieben. Im Tabellenblatt-Code bleibt dann nur noch die folgende Prozedur stehen:

Sub Mail\_Schicken()
 Call E\_Mail\_senden(Me)
End Sub 'Mail\_Schicken

Vorteil der Auslagerung des eigentlichen Codes in ein Modul: Du kannst ihn von beliebigen Arbeitsblättern aus verwenden, also quasi wiederverwenden. Das „Me“ steht für das Arbeitsblatt, in dem der VBA-Code steht und entspricht „ActiveSheet“, wenn es gerade das aktive Blatt ist. Im separaten Modul steht dann folgendes:

Sub E_Mail_senden(ws As Worksheet)
Dim objOL As Outlook.Application
Dim objMail As Outlook.MailItem
Dim sBodyHeader As String
Dim sBodyText As String
Dim sBodyFooter As String

sBodyHeader = „“
sBodyFooter = „“

sBodyText = " Dies ist die erste Zeile.

" & _
"Und dies ist die zweite Zeile.

" & _
ws.Range(„D25“) & ws.Range(„B26“) & ws.Range(„B27“) & "

" & _
„Und dies ist eine blaue Zeile.“

Set objOL = CreateObject(„Outlook.Application“)
Set objMail = objOL.CreateItem(olMailItem)

With objMail
.Subject = ws.Range(„C14“)
.BodyFormat = olFormatHTML
.HTMLBody = sBodyHeader & sBodyText & sBodyFooter
.To = ws.Range(„D4“) & „;“ & ws.Range(„D5“) & „;“ & ws.Range(„D6“)
.CC = ws.Range(„D7“) & „;“ & ws.Range(„D8“) & „;“ & ws.Range(„D9“)
.BCC = ws.Range(„D10“) & „;“ & ws.Range(„D11“) & „;“ & ws.Range(„D12“)
.Importance = ws.Range(„C16“)
.Display
End With 'objMail

Set objMail = Nothing
Set objOL = Nothing
End Sub 'E_Mail_senden
Die Sache mit dem Senden habe ich weggelassen, um den Code auf das Wesentliche zu konzentrieren. Aber das kannst Du dann leicht wieder einbauen.

Kristian

#6

Vielen Dank!!!
Hallo Kristian,

Tausend Dank für deine Antwort, im ersten Versuch hat’s super geklappt!! Vielen, vielen Dank für deine Hilfe :smile:

Gruß
Christian

#7

Tausend Dank für deine Antwort, im ersten Versuch hat’s super
geklappt!! Vielen, vielen Dank für deine Hilfe :smile:

Freut mich. Du musst Dich natürlich ein kleines bisschen mit HTML auskennen, also mit den Grundlagen der Textformatierung mittels CSS. Aber das ist sicher nicht das große Problem. Dafür gibt´s ja notfalls http://de.selfhtml.org/css/eigenschaften/schrift.htm

Kristian

#8

Hallo Kristian,

habe noch zwei Frage zu dem Ganzen:

  1. Die Zeilenumbrüche aus meiner Excel-Zelle werden nicht übernommen, sondern der Zellentext wird an einem Stück hintereinander geschrieben. Kann man Excel sagen, dass die Zeilenumbrüche der Zelle mit übertragen werden sollen?

Sonst:
Da die Zeilenumbrüche aus meiner Excel-Zelle nicht übernommen werden, muss ich nun jedes Satz meines Textes einzeln einbringen. Soweit auch kein Problem. Aber: in meinem Text aus der Excel-Zelle waren auch Formeln hinterlegt, denn es kann sein - je nach Fall - dass es mal zwei Sätze sind, mal drei oder auch vier Sätze. So würde ja dann letztlich meine Formatierung nicht mehr stimmen… . Kann man sagen, wenn z. Bsp. Zelle B28 = „“ dann soll er gar nichts machen und das ignorieren?

Und 2.
Ich habe einen Text am Ende meines Textes, der verlinkt ist mit einer Homepage. In der Mail soll nur der Text stehen, aber wenn ich den Text anklicke, soll er als Hyperlink funktionieren. Kann man das bei der Übergabe zur Mail sagen, dass der Text, wie auch in Excel, ein Hyperlink zu einer Homepage ist??

Vielen, vielen Dank im Voraus!!

Gruß
Christian

#9

Kein Problem - HTML
Hallo Christian,

da ist es nun also passiert: Die HTML-Kenntnisse fehlen, die ja wie gesagt Voraussetzung sind :wink: Aber kein Problem, die beiden Punkte lassen sich wahrscheinlich sehr leicht lösen.

  1. Die Zeilenumbrüche aus meiner Excel-Zelle werden nicht
    übernommen, sondern der Zellentext wird an einem Stück
    hintereinander geschrieben. Kann man Excel sagen, dass die
    Zeilenumbrüche der Zelle mit übertragen werden sollen?

Nein, nicht direkt. Das Problem ist, dass Zeilenumbrüche in HTML nicht als solche dargestellt werden. Stattdessen muss man sie mit dem BR-Tag erzwingen (http://de.selfhtml.org/html/text/zeilenumbruch.htm).
Das heißt, dass die vorhandenen DOS-Umbrüche, die Excel und jeder Texteditor darstellen kann, durch HTML-Umbrüche ersetzt werden. Dazu muss man wissen, wie ein DOS-Zeilenumbruch in Excel aussieht: Es ist das ASCII-Zeichen mit der Nummer 10 (bei Windows ist es die Kombination aus den Zeichen 13 und 10, aber Excel lässt das erste weg). Das Ersetzen erledigt die VBA-Funktion Replace:Sub Test()
With ActiveCell
.Offset(1, 0).Value = Replace(.Value, Chr(10), "
")
End With 'ActiveCell
End Sub 'TestKopiere diese kleine Prozedur mal in ein VBA-Fenster und markiere eine Zelle mit Umbrüchen. Die Zelle darunter sollte leer sein, denn die obige Prozedur schreibt dort die HTML-Variante vom Text darüber rein.
Das musst Du dann entsprechend in Deinen Code übertragen, also das rechts vom Gleichheitszeichen. Statt des „.Value“ kommt dann Dein Zellbezug dort rein.
Deine „Sonst“-Frage müsste sich damit erübrigen.

Und 2.
Ich habe einen Text am Ende meines Textes, der verlinkt ist
mit einer Homepage. In der Mail soll nur der Text stehen, aber
wenn ich den Text anklicke, soll er als Hyperlink
funktionieren. Kann man das bei der Übergabe zur Mail sagen,
dass der Text, wie auch in Excel, ein Hyperlink zu einer
Homepage ist??

Siehe hier: http://de.selfhtml.org/html/verweise/definieren.htm
Damit solltest Du das eigentlich alleine hinkriegen. Du musst Dir nur diese Link-Nomenklatur mit Hilfe des „&“-Operators in VBA zusammenbasteln.

Kristian

#10

Noch eine weitere Frage…
Hi Kristian,

vielen Dank für die Hilfe bei den Zeilenumbrüche, klappt wieder alles bestens.

hab noch eine weitere Frage, habe gestern bis Mitternacht rumprobiert, aber war wohl schon zu dunkel für den hellen Augenblick…: ich will meinen Text nun mit verschiedenen Formaten belegen. Wenn ich von Hand einen Text schreibe - wie auch in deinem Code - wird das auch alles in die Mail transportiert. Aber nun will ich wieder die Daten aus meiner Zelle in Excel holen. So habe ich das probiert:

sBodyText = "Range(„D29“)

" & _
ws.Range („B29“) & "
" & _
„Hier steht blauer Text“

Bei der Methode tritt ein Syntaxfehler auf. Kannst du mir sagen, wie ich nun in Zeile 1 im gerade gesehenen Beispiel korrekt auf meinen Excel-Zellenbezug verweise?

Noch mal vielen, vielen Dank für deine Bemühen; du hilfst mir echt weiter!!

Gruß
Christian

#11

Moin,

da liegt ein grundsätzliches Verständnisproblem vor. Hier die beiden entscheidenden Punkte:

&-Operator:
Mit diesem Operator werden Texte miteinander verknüpft. Syntax: A1 & A2. Dabei sind A1 und A2 beliebige Ausdrücke. Wichtig ist, dass sie jeweils klar separat notiert werden.

Beispiel 1:

"Hallo" & " Welt" & "!"

Hiermit werden drei reine Texte miteinander verknüpft. Zu erkennen an den Anführungszeichen.

Beispiel 2:

sZeile1 & "
" & sZeile2

Hier werden zwei Variablen und ein Text miteinander verknüpft. Ob in den Variablen Text, eine Zahl oder was auch immer steht, ist erstmal egal. Das Ergebnis enthält die bestmögliche Konvertierung des Wertes ins Textformat.

Beispiel 3:

ws.Range("A1").Value & "
" & 15

Hier wird nun auf den Inhalt einer Zelle verwiesen, dann kommt ein fester String und dann eine Zahl. Alles wird miteinander verknüpft. Auch hier sieht man wieder, dass die Ausdrücke zwischen den &-Zeichen immer für sich genommen vollständig und ohne jede Ergänzung da stehen.

Dein Fehler war nun, dass Du in der ersten Zeile das Range(…) in Anführungszeichen gesetzt hast. Damit hast Du das zu einem festen Text gemacht, der eben genau das darstellt, was Du zwischen diese Anführungszeichen geschrieben hast. Richtig ist es im Beispiel 3 gezeigt und auch in meinem originalen Code.

_-Zeichen:
Dieses Zeichen dient nur dazu, lange Zeilen im Code umzubrechen. Normalerweise kann man in VBA eine Zeile nicht aufteilen. Wenn man das trotzdem machen will, muss man dieses Fortsetzungszeichen verwenden.

Kristian

#12

Danke!
Hi Kristian,

habe nun meine E-Mail so beieinander, wie ich sie haben mag.

Ich danke dir vielmals, du hast mir echt sehr viel weitergeholfen, vielen vielen Dank!!!

Wünsche ein schönes Wochenende,

Gruß
Christian