Excel VBA: Mehrere Zellen auswählen mit .Range

Hallo zusammen

Bin ein Anfänger in VBA und habe ich durch etliche Foren geschlagen, um zu lernen :) Über Excel-Makro eine E-Mail erstellen funktioniert relativ einfach. Nun habe ich in der Spalte D (ab Zelle D4) die E-Mail Adressen aufgeführt (ca. 200 Stk,). Diese sollen nun alle ins Bcc Feld.

Mail.bcc = Sheets(Quelle).Range("D4:D250")

-> das funktioniert nicht. Fehlermeldung "Die untere Array Grenze muss null sein". Ich kann zwar einzelne Zellen mit "&" hinzufügen, das wäre aber mühsam bei 250 Adressen.

Frage: Gibt es eine Lösung, welche ich an dieser Stelle einbauen könnte, damit alle Zellen in der Spalte D ins Bcc Feld kopiert werden?

Danke für eure Hilfe.

Grüsse Roman

Answer 1

[myasker]

Danke für eure Lösungsansätze. Die Lösung habe ich nun gefunden. mit folgendem Skript füge ich in Zelle N3 (welche ausgeblendet ist) alle E-Mail Adressen mit einem Semikolon ein:

AbZeile = 4

Spalte = "D"

Trenn = ";"

Ergebniszelle = "N1"

Zeile = AbZeile

Do Until Zeile = 999

Gesamt = Gesamt & ";" & Cells(Zeile, Spalte)

Zeile = Zeile + 1

Loop

Range(Ergebniszelle).Value = Mid(Gesamt, 2)

Somit fragt es die Spalte D ab bis zur Zelle D999, welches ich auch noch erweitern könnte. Die unnötigen ";" werden von Outlook gelöscht.

Der Rest sieht dann wie folgt aus (sorry wegen den unnötigen Kommentaren):

Quelle = ActiveWorkbook.ActiveSheet.Name
Dim outl, Mail As Object
Set outl = CreateObject("Outlook.Application")
Set Mail = outl.CreateItem(0)
Mail.Subject = Sheets(Quelle).Range("G7")
Mail.body = Sheets(Quelle).Range("G8") & vbCrLf & vbCrLf & Sheets(Quelle).Range("G9")
Mail.To = Sheets(Quelle).Range("G4")
Mail.CC = Sheets(Quelle).Range("G5")
Mail.bcc = Sheets(Quelle).Range("N1")
'& "; " & Sheets(Quelle).Range("A2") & "; " & Sheets(Quelle).Range("A3")
'Wichtigkeit Hoch (1 = normal, 0 = niedrig)
'ausgelassen: 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

Comments

[Iamiam]

Nun, ob eine Do Until...-Schleife oder eine For ... next -Schleife ist eigentlich egal. Und dass das Trennzeichen in Outlook der Semicolon ist, wusste ich nicht, mein Mailprogramm (Arcor) verlangt Komma.
Aber Hauptsache, Du hast jetzt eine funktionierende Lösung. Vllt solltest Du Dein VBA soweit ausbauen, dass Du einfache Strukturen verstehst?
Ich schreibe derart viele Antworten, wo die Leute keinen ausreichenden Hointergrund haben: es macht keinen Spaß, umsonst zu arbeiten!

Answer 2

[schmiddi1967]

Naja, ich würde sagen das er ja gar nicht weiss was er mit D4-D250 machen soll. Da es nur ein Teilstück vom Code ist, ist das schwer nachzuvollziehen.

Eventuell hilft dir da schon ein kleines .Select. Versuche mal:

Mail.bcc = Sheets(Quelle).Range("D4:D250").Select

Comments

[myasker]

Hallo schmiddi1967...danke für deine Antwort! Ist ein guter Ansatz, funktioniert nicht ganz. So fügt er im Bcc Feld ein "-1" ein.

-> Habe die Lösung gefunden: siehe Kommentar unten.

[schmiddi1967]

@myasker

Super, hauptsache es hat noch geklappt. Dann ist die Antwort von Iamiam doch eher das was dir geholfen hat.

Answer 3

[Iamiam]

Hab nie Mails per µ erstellt und vllt gibts einfachere Wege, aber so sollte es funktionieren:

Du führst eine Variable C und eine AllAdr ein (musst nicht definieren als was, überlass das VBA) und füllst sie so:

AllAdr = ""
For each C in Sheets(Quelle).Range("D4:D250").Cells 'vermutlich gehts auch ohne .Cells
AllAdr = AllAdr & ", " & C.value
Next C
AllAdr = mid(AllAdr, 3, 55555) '<- eliminiert das erste , und Leerzeichen wieder

Viel Erfolg!

das fügst Du dann ins BCC-Feld ein

Comments

[maximilianus7]

---

[maximilianus7]

@maximilianus7

mid(AllAdr, 3) genügt

[Iamiam]

@maximilianus7

Damit nicht unerwünschte , (Komma+Leer) drinstehen, noch eine Prüfung (und verschiedene Möglichkeiten dafür):

if C <> "" then AllAdr = AllAdr & ", " & C.value

oder

if not isempty(C) then AllAdr = AllAdr & ", " & C.value

oder

if len(C.value) > 0 then AllAdr = AllAdr & ", " & C.value

Das .value kann man sich im Prinzip sparen (ist Standardargument), ich schlepp's der Klarheit wegen aber meist mit.

[myasker]

@Iamiam

hallo lamiam...danke für deine Antwort! Das mit deinem Ansatz oben hätte sicher funktioniert, leider bin ich etwas zu unerfahren in VBA. Habe aber die Lösung gefunden, siehe sep. Kommentar.

Vielen Dank!!