Hi there,
I have a workbook that is accessed by multiple users.
I am currently working with a macro that will send me an email every time my workbook is saved.
Problem is, I only need to know if 1 specific column is updated. I don't care about the rest of the workbook. I have one user who saves A LOT! and I'd like to try to eliminate some emails.
This is my current macro, is there a way to change it so it only emails if Column F is changed for example? Thanks in advance!
------------------------------------------------------------------
Workbook Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim olapp As Object
Dim olmail As Object
Dim LoggedUserName As String
Dim LoggedUserEmail1 As String
Dim LoggedUserEmail2 As String
LoggedUserName = Environ("username") ' no need for all that gooey code, when this will suffice!
'*****CHANGE EMAIL ADDRESS HERE - MyCompany.com part********
LoggedUserEmail1 = "autumnm" & "@pidherneys.com"
'*****CHANGE EMAIL ADDRESS HERE********
Dim cel As Range
For Each cel In Sheets("LogSheet").UsedRange
strBody = strBody & cel & vbCrLf
Next
Sheets("LogSheet").UsedRange.EntireRow.Delete 'essentially start log off fresh after e-mail
strBody = "The sheets and cells changed are listed below: " & vbCrLf & vbCrLf & strBody
Set olapp = CreateObject("Outlook.Application")
Set olmail = olapp.CreateItem(0)
With olmail
.Subject = LoggedUserName & " has edited " & ThisWorkbook.Name & "Test 100"
.To = LoggedUserEmail1
.CC = LoggedUserEmail2 & ";" & LoggedUserEmail3
.Body = strBody
.Send
End With
If olapp.ActiveExplorer Is Nothing Then
olapp.Quit
Set olapp = Nothing
End If
End Sub
--------------------------------------------------------------
Sheet Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strChanges As String
strChanges = "Sheet: " & Target.Parent.Name & " | " & "Cell: " & Target.Address
Dim lngRows As Long
With ThisWorkbook.Sheets("LogSheet")
If .Cells(1, 1) = vbNullString Then lngRows = 1 Else: lngRows = .UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1
.Cells(lngRows, 1) = strChanges
End With
End Sub
I have a workbook that is accessed by multiple users.
I am currently working with a macro that will send me an email every time my workbook is saved.
Problem is, I only need to know if 1 specific column is updated. I don't care about the rest of the workbook. I have one user who saves A LOT! and I'd like to try to eliminate some emails.
This is my current macro, is there a way to change it so it only emails if Column F is changed for example? Thanks in advance!
------------------------------------------------------------------
Workbook Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim olapp As Object
Dim olmail As Object
Dim LoggedUserName As String
Dim LoggedUserEmail1 As String
Dim LoggedUserEmail2 As String
LoggedUserName = Environ("username") ' no need for all that gooey code, when this will suffice!
'*****CHANGE EMAIL ADDRESS HERE - MyCompany.com part********
LoggedUserEmail1 = "autumnm" & "@pidherneys.com"
'*****CHANGE EMAIL ADDRESS HERE********
Dim cel As Range
For Each cel In Sheets("LogSheet").UsedRange
strBody = strBody & cel & vbCrLf
Next
Sheets("LogSheet").UsedRange.EntireRow.Delete 'essentially start log off fresh after e-mail
strBody = "The sheets and cells changed are listed below: " & vbCrLf & vbCrLf & strBody
Set olapp = CreateObject("Outlook.Application")
Set olmail = olapp.CreateItem(0)
With olmail
.Subject = LoggedUserName & " has edited " & ThisWorkbook.Name & "Test 100"
.To = LoggedUserEmail1
.CC = LoggedUserEmail2 & ";" & LoggedUserEmail3
.Body = strBody
.Send
End With
If olapp.ActiveExplorer Is Nothing Then
olapp.Quit
Set olapp = Nothing
End If
End Sub
--------------------------------------------------------------
Sheet Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strChanges As String
strChanges = "Sheet: " & Target.Parent.Name & " | " & "Cell: " & Target.Address
Dim lngRows As Long
With ThisWorkbook.Sheets("LogSheet")
If .Cells(1, 1) = vbNullString Then lngRows = 1 Else: lngRows = .UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1
.Cells(lngRows, 1) = strChanges
End With
End Sub