Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

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

  1. #1
    New Member
    Join Date
    Aug 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  2. #2
    New Member
    Join Date
    Oct 2018
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  3. #3
    New Member
    Join Date
    Oct 2018
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •