Gruppe
Extern
Problem
Beim Öffnen der Arbeitsmappe soll über Outlook eine Mail an einen vorgegebenen Email-Adressaten gesandt werden.
ClassModule: DieseArbeitsmappe
Private Sub Workbook_Open()
Dim oOL As Object
Dim oOLMsg As Object
Dim oOLRecip As Object
Dim sUser As String
Set oOL = CreateObject("Outlook.Application")
Set oOLMsg = oOL.CreateItem(0)
sUser = cptName
If cptName = "" Then sUser = Application.UserName
With oOLMsg
Set oOLRecip = .Recipients.Add("hans@herber.de")
oOLRecip.Type = olTo
.Subject = "LogMeldung Datei " & ThisWorkbook.Name
.Body = "Die Arbeitsmappe " & ThisWorkbook.Name & _
" wurde von " & sUser & " am " & Date & " um " & Time & _
" geöffnet." & vbLf & vbLf
.Send
End With
oOLRecip.Resolve
Set oOLRecip = Nothing
Set oOLMsg = Nothing
Set oOL = Nothing
End Sub
StandardModule: Modul1
Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, _
nSize As Long) As Long
Function cptName()
Dim Buffer As String * 100
Dim BuffLen As Long
BuffLen = 100
GetUserName Buffer, BuffLen
End Function