Sending an email when a value/name is entered into a cell

[rider_01]

I have a spreadsheet for work that gets populated with various issues. I have to notify each person who is in charge of resolving the issue, there are so many at this point I am running around all day. Is there a feature in excel or a way to send out an email to a person when their name is populated into a cell?

For example in cell G5 John Doe is entered. I need to automatically send an email to John Doe to let him know he needs to address the issue. Thanks for the help.

 

[Firesword]

Hi Rider_01 I'm only a basic user but this code should work till a more advanced user can help you.
Due to my basic level there are some restrictions to using this.
1) in column G you will need to enter their name as it is for the email account IE if their name on the email is jonathan.doe then john.doe will not work
2) in your example G5, type in the name and press enter (assuming that excel is set to move the cursor down on each press of enter) if you press the right arrow key then this code will not work.
3) you can also include the subject mater as part of your email, i've guessed this to be in column C please change as needed.
4) you can also include the body of the email as well, again i've guessed this to be in column D please change as needed.
5) change as needed for your company name and your name.

'Copy this code to I.E Module 1
Sub EmailMe(lngRowNumber As Long)
    
    Dim OutApp, OutMail As Object
        
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With OutMail
         .To = Cells(lngRowNumber, "G") & "@yourcompanynamehere.com"
         .CC = ""
         .BCC = ""
         .Subject = Cells(lngRowNumber, "C")
         .Body = "Dear " & Cells(lngRowNumber, "G") & vbCr & vbCr & "Please action:-" & vbCr & vbCr _
         & Cells(lngRowNumber, "D") & vbCr & vbCr & "Regards" & vbcr & cbcr & "Your name here"
         .Display 'to view the email before sending or .Send to send the email without viewing it.
     End With

    Set OutApp = Nothing
    Set OutMail = Nothing

End Sub

'Copy this code to "Microsoft Excel Object" Sheetx where x is the sheet number that contails the data
'This will keep an eye on column G and everytime there is a value entered in this column it will run the code to create an email.
Sub Worksheet_Change(ByVal Target As Range)

Dim lngRowNumber As Long
    
    If Not Application.Intersect(Target, Range("G:G")) Is Nothing Then
           
           lngRowNumber = ActiveCell.Row - 1
           Call EmailMe(lngRowNumber)
           
    End If
End Sub

 

[Firesword]

Sorry there was a typo in the first reply, please use this one.

'Copy this code to I.E Module 1
Sub EmailMe(lngRowNumber As Long)
    
    Dim OutApp, OutMail As Object
        
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With OutMail
         .To = Cells(lngRowNumber, "G") & "@yourcompanynamehere.com"
         .CC = ""
         .BCC = ""
         .Subject = Cells(lngRowNumber, "C")
         .Body = "Dear " & Cells(lngRowNumber, "G") & vbCr & vbCr & "Please action:-" & vbCr & vbCr _
         & Cells(lngRowNumber, "D") & vbCr & vbCr & "Regards" & vbcr & vbcr & "Your name here"
         .Display 'to view the email before sending or .Send to send the email without viewing it.
     End With

    Set OutApp = Nothing
    Set OutMail = Nothing

End Sub

'Copy this code to "Microsoft Excel Object" Sheetx where x is the sheet number that contails the data
'This will keep an eye on column G and everytime there is a value entered in this column it will run the code to create an email.
Sub Worksheet_Change(ByVal Target As Range)

Dim lngRowNumber As Long
    
    If Not Application.Intersect(Target, Range("G:G")) Is Nothing Then
           
           lngRowNumber = ActiveCell.Row - 1
           Call EmailMe(lngRowNumber)
           
    End If
End Sub