AW: Hilfe: E-Mail senden in Excel, ABER ...
10.06.2014 19:41:41
Mullit
Hallo,
hier mal ein erster Ansatz,
es sollten nach jedem Senden oder Schließen alle Mailfenster
nacheinander aufgerufen werden...
Option Explicit
Private Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Sub Sleep Lib "kernel32.dll" ( _
ByVal dwMilliseconds As Long)
Private Declare Function SetTimer Lib "user32.dll" ( _
ByVal hwnd As Long, _
ByVal nIDEvent As Long, _
ByVal uElapse As Long, _
ByVal lpTimerFunc As Long) As Long
Private Declare Function KillTimer Lib "user32.dll" ( _
ByVal hwnd As Long, _
ByVal nIDEvent As Long) As Long
Private Const GC_CLASSNAMEMSOUTLOOK = "rctrl_renwnd32"
Private Const INSPEC_CAP As String = " - Nachricht (HTML) " ''" - Message (HTML) " bei engl. Excel-Version
Private Const olByValue = 1
Private Const olMailItem = 0
Private objOLApp As Object
Private blnExit As Boolean
Private blnExitTimer As Boolean
Private lngCount As Long
Private lngArrRow() As Long
Private strCaption As String
Public Sub prcMailListFile() 'hier aufrufen...
Dim lngIndex As Long
Dim strPath As String
Dim objCell As Range
If Not blnExit Then
blnExit = Not blnExit
If MsgBox("Soll der automatische eMail Versand gestartet werden ?", _
vbYesNo + vbQuestion, "Frage") = vbNo Then
blnExit = Not blnExit
Exit Sub
End If
With Worksheets("Tabelle1")
If Intersect(.Range(.Cells(1, 1), _
.Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, _
.Cells(1, .Columns.Count).End(xlToLeft).Column)), Selection) Is Nothing Then
MsgBox "Die activen Zellen befinden sich nicht in der CommandZone!", _
vbExclamation, "NoCommand"
blnExit = Not blnExit
Exit Sub
End If
End With
Set objOLApp = CreateObject(Class:="Outlook.Application")
ReDim lngArrRow(Selection.Count - 1) As Long
For Each objCell In Selection
lngIndex = lngIndex + 1
lngArrRow(lngIndex - 1) = objCell.Row
Next
strPath = Worksheets("Tabelle1").Cells(lngArrRow(lngCount), 8)
With objOLApp.CreateItem(olMailItem)
.To = Worksheets("Tabelle1").Cells(lngArrRow(lngCount), 1)
.CC = Worksheets("Tabelle1").Cells(lngArrRow(lngCount), 2)
.BCC = Worksheets("Tabelle1").Cells(lngArrRow(lngCount), 3)
.Subject = Worksheets("Tabelle1").Cells(lngArrRow(lngCount), 4)
strCaption = .Subject & INSPEC_CAP
.Body = Worksheets("Tabelle1").Cells(lngArrRow(lngCount), 7)
If strPath <> "" Then _
.Attachments.Add strPath, olByValue
.Display
End With
Call prcStartTimer
End If
End Sub
Private Sub prcStartTimer()
SetTimer Application.hwnd, 0&, 200&, AddressOf TimerProc
End Sub
Private Sub prcStopTimer()
KillTimer Application.hwnd, 0&
End Sub
Private Sub TimerProc(ByVal hwnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long)
If Not blnExitTimer Then
If FindWindow(GC_CLASSNAMEMSOUTLOOK, strCaption) = 0 And lngCount < UBound(lngArrRow) Then
blnExitTimer = Not blnExitTimer
DoEvents
Sleep 200&
Set objOLApp = CreateObject(Class:="Outlook.Application")
Call prcDisplay
ElseIf FindWindow(GC_CLASSNAMEMSOUTLOOK, strCaption) = 0 And lngCount >= UBound(lngArrRow) Then
Call prcStopTimer
Set objOLApp = Nothing
MsgBox "eMail Versand abgeschlossen", _
vbInformation, "E-Mail delivery successfully completed"
lngCount = 0
blnExit = Not blnExit
End If
End If
End Sub
Private Sub prcDisplay()
Dim strPath As String
lngCount = lngCount + 1
strPath = Worksheets("Tabelle1").Cells(lngArrRow(lngCount), 8)
With objOLApp.CreateItem(olMailItem)
.To = Worksheets("Tabelle1").Cells(lngArrRow(lngCount), 1)
.CC = Worksheets("Tabelle1").Cells(lngArrRow(lngCount), 2)
.BCC = Worksheets("Tabelle1").Cells(lngArrRow(lngCount), 3)
.Subject = Worksheets("Tabelle1").Cells(lngArrRow(lngCount), 4)
strCaption = .Subject & INSPEC_CAP
.Body = Worksheets("Tabelle1").Cells(lngArrRow(lngCount), 7)
If strPath <> "" Then _
.Attachments.Add strPath, olByValue
.Display
If blnExitTimer Then _
blnExitTimer = Not blnExitTimer
End With
End Sub
Zu Infoquellen:
natürlich neben den hier vorhandenen Sparten (z.B. Excel-FAQ)
bieten
http://www.online-excel.de/
Grundlagentutorials(s. Outlook & Excel)
http://www.office-loesung.de/
Tipps und Tricks bes. von Nepumuk & Isabelle
http://vbanet.blogspot.de
komplette Codevorlagen mit sehr guten Kommentierungen (Case)
Gruß,