Fabbby-San
03-14-2018, 02:30 AM
Hi all,
I got a little problem that I canīt quite solve with my VBA knowledge, unfortunatley. I want a macro that automatically sends an e-mail if two conditions are met 1. that the workbook was saved and 2. that the name of this workbook was changed aswell. I used the following code, but it does not seem to do the trick. As highlighted I tried it with an If-statement with two conditions. I open and thankful for any helping suggestions.
Option Explicit
'Send email if name of workbook is changed
Private Sub WorkbookName_Change()
Dim xOutApp As Object
Dim xMailItem As Object
Dim xMailBody As String, xName As String, wbName As String
Dim Change
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
wbName = ThisWorkbook.Name
If ThisWorkbook.Saved = True Then
If wbName <> ActiveWorkbook.Name Then
Set xOutApp = CreateObject("Outlook.Application")
Set xMailItem = xOutApp.CreateItem(0)
xMailBody = "Hallo," & Chr(13) & Chr(13) & "Fyi, die Datei wurde geupdated :)."
With xMailItem
.To = ""
.CC = ""
.Subject = "Worksheet modified in " & ThisWorkbook.FullName
.Body = xMailBody
.Attachments.Add (ThisWorkbook.FullName)
.Display
End With
Set xOutApp = Nothing
Set xMailItem = Nothing
End If
End If
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
kind regards,
Felix
I got a little problem that I canīt quite solve with my VBA knowledge, unfortunatley. I want a macro that automatically sends an e-mail if two conditions are met 1. that the workbook was saved and 2. that the name of this workbook was changed aswell. I used the following code, but it does not seem to do the trick. As highlighted I tried it with an If-statement with two conditions. I open and thankful for any helping suggestions.
Option Explicit
'Send email if name of workbook is changed
Private Sub WorkbookName_Change()
Dim xOutApp As Object
Dim xMailItem As Object
Dim xMailBody As String, xName As String, wbName As String
Dim Change
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
wbName = ThisWorkbook.Name
If ThisWorkbook.Saved = True Then
If wbName <> ActiveWorkbook.Name Then
Set xOutApp = CreateObject("Outlook.Application")
Set xMailItem = xOutApp.CreateItem(0)
xMailBody = "Hallo," & Chr(13) & Chr(13) & "Fyi, die Datei wurde geupdated :)."
With xMailItem
.To = ""
.CC = ""
.Subject = "Worksheet modified in " & ThisWorkbook.FullName
.Body = xMailBody
.Attachments.Add (ThisWorkbook.FullName)
.Display
End With
Set xOutApp = Nothing
Set xMailItem = Nothing
End If
End If
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
kind regards,
Felix