Thema: Rundschreiben mit vorgegenem Text an Verteiler senden

StandardModule: basMain

Sub Sample()
   Dim olApp As Object
   Dim olMailItm As Object
   Dim iCounter As Integer, iRow As Integer
   Dim Dest As Variant
   Dim SDest As String, txt As String, sSubject As String
   sSubject = Range("Betreff").Value
   Set olApp = CreateObject("Outlook.Application")
   Set olMailItm = olApp.CreateItem(0)
   With Worksheets("MyText")
      iRow = .Cells(Rows.Count, 1).End(xlUp).Row
      For iCounter = 1 To iRow
         If Not IsEmpty(.Cells(iCounter, 1)) Then
            txt = txt & .Cells(iCounter, 1).Text & vbNewLine
         Else
            txt = txt & vbNewLine
         End If
      Next iCounter
   End With
   With olMailItm
       SDest = ""
       For iCounter = 1 To _
         Range("A1").End(xlDown).Row
           If SDest = "" Then
               SDest = Cells(iCounter, 1).Value
           Else
               SDest = SDest & ";" & Cells(iCounter, 1).Value
           End If
       Next iCounter
       .BCC = SDest
       .Subject = sSubject
       .Body = txt
       .Send
   End With
   Set olMailItm = Nothing
   Set olApp = Nothing
End Sub

Beiträge aus dem Excel-Forum zu den Themen Extern und Outlook