Empfänger variable in einer Excelliste eintragen

Sub Mail_workbook_Outlook_1() 'Working in Excel 2000-2016 'This example send the last saved version of the Activeworkbook 'For Tips see: http://www. _ rondebruin.nl/win/winmail/Outlook/tips.htm Dim OutApp As Object Dim OutMail As Object Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .to = "xyz" .CC = "" .BCC = "" .Subject = "This is the Subject line" .Body = "Hi there" .Attachments.Add ActiveWorkbook.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Send 'or use .Display End With On Error GoTo 0 Set OutMail = Nothing Set OutApp = Nothing End Sub

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Sub Mail_workbook_Outlook_1() 'Working in Excel 2000-2016 'This example send the last saved version of the Activeworkbook 'For Tips see: http://www. _ rondebruin.nl/win/winmail/Outlook/tips.htm Dim OutApp As Object Dim OutMail As Object Dim rngEmail As Range Dim rngZelle As Range Dim sTo As String Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) Set rngEmail = Workbooks("x").Worksheets("x").Range("x") 'Quelle für Email Range anpassen For Each rngZelle In rngEmail sTo = sTo & ";" & rngZelle.Value Next rngZelle sTo = Mid(sTo, 2) On Error Resume Next With OutMail .to = "sTo" .CC = "" .BCC = "" .Subject = "This is the Subject line" .Body = "Hi there" .Attachments.Add ActiveWorkbook.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Send 'or use .Display End With On Error GoTo 0 Set OutMail = Nothing Set OutApp = Nothing End Sub

Hi,
das folgende funktioniert für mich sehr gut.
Muss es nur noch schaffen auch den Body, ein Teil der Datei und vielleicht noch 1-2 Dinge variablke zu gestalten =)

Sub Sent_Email()
'Setting up the Excel variables.
Dim olApp As Object
Dim olMailItm As Object
Dim iCounter As Integer
Dim iiCounter As Integer
Dim Dest As Variant
Dim SDest As String
Dim SSDest As String
'Create the Outlook application and the empty email.
Set olApp = CreateObject("Outlook.Application")
Set olMailItm = olApp.CreateItem(0)
'Using the email, add multiple recipients, using a list of addresses in column A.
Worksheets("Sent to").Activate
With olMailItm
SDest = ""
For iCounter = 1 To WorksheetFunction.CountA(Columns(1))
If SDest = "" Then
SDest = Cells(iCounter, 1).Value
Else
SDest = SDest & ";" & Cells(iCounter, 1).Value
End If
Next iCounter
'Using the email, add multiple recipients, using a list of addresses in column A.
SSDest = ""
For iCounter = 1 To WorksheetFunction.CountA(Columns(2))
If SSDest = "" Then
SSDest = Cells(iCounter, 1).Value
Else
SSDest = SSDest & ";" & Cells(iCounter, 1).Value
End If
Next iCounter
'Do additional formatting on the BCC and Subject lines, add the body text from the  _
spreadsheet, and send.
.To = SDest
.cc = SSDest
.Subject = "FYI"
.HTMLBody = "Hallo Meine Damen und Herren, ich wünsche Ihnen einen wunderschönen Guten  _
Abend!" & .HTMLBody
.Attachments.Add ("blabla.xlsx")
.Display
End With
'Clean up the Outlook application.
Set olMailItm = Nothing
Set olApp = Nothing
End Sub

Beliebteste Forumthreads (12 Monate)

Empfänger variable in einer Excelliste eintragen

7 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread