Forum Discussion

slw_wills's avatar
slw_wills
Copper Contributor
Feb 19, 2025

Send emails from a multiple user spreadsheet

Hi

I'm not a proficient VBA user so apologies in advance if my request isn't clear.

We have a spreadsheet that users can log 'issues' into.  The spreadsheet is on SharePoint and is not check in/out, to allow multiple users to update/add new rows at the same time.

I've been requested to set up a macro that will send out an email each time when either a new row (issue) is logged or a previous issue has been updated by determining the 'status' column changes.  For example, if a new issue is logged the 'status' is set the 'New', send an email to the issue owner, but if an issue status on a different row is then set to 'In-Progress', send another email to perhaps a different issue owner.  Owners are also logged on the spreadsheet so easy to pickup.  Sounds simple but here are my concerns.

  1. My code identifies when data on a row has been updated but how do I identify when a particular cell has been updated?
  2. I have written some code that on the event 'BeforeSave', however the spreadsheet is set to AutoSave therefore sends the emails multiple times before the users has finished adding all the columns of data.  Is there a different event that can be used or should I be looking for a different option for sending emails?  
  3. If the user has started to enter a row (issue) but they haven't yet entered their email address as the owner, how do I delay sending the email until the row of information has been fully completed.

Many thanks for any advice.  All advice appreciated.

excel
Macros and VBA
  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Feb 20, 2025

    You can write codes inside Worksheet_Change event. See below example.

    Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo HarunErrHandler

    If Not Application.Intersect(Target, Range("B:B")) Is Nothing Then
        If Target = "In-Progress" Then
            MsgBox "Write codes here for sending mail."
        End If
    End If
  
Exit Sub
HarunErrHandler:
MessageBox = MsgBox("Error Number: " & Err.Number & vbCrLf & Err.Description, vbCritical, "Error")
End Sub

     

Resources