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

rider_01

New Member
Joined
Aug 17, 2018
Messages
4
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.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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.

Code:
'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
 
Upvote 0
Sorry there was a typo in the first reply, please use this one.

Code:
'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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top