Drop down menu to automatically send email

Hello,

I am trying to create a sheet of a list of jobs, I will fill in the sheet and when the job is completed somebody else will select 'Complete' from the drop down menu in column E.

Is there a way that I can get excel to automatically send an email when 'complete' is selected? I need to send an email to the email address which I have matched to the initials in the 'Raised By' column. I need the email to contain the info from the cells i.e description, ref no. and date raised.

Thanks!

Hi,

Try this.

Place the code in the sheet code:

Private Sub Worksheet_Change(ByVal Target As Range)
     
    Dim Cell As Range
    Dim rngCheck As Range
     
    uRows = Sheets(1).UsedRange.Rows.Count
     
    Set rngCheck = Intersect(Target.EntireRow, Range("E2:J" & uRows))
    If Not rngCheck Is Nothing Then
        For Each Cell In rngCheck.Cells
            If Cell = "Complete" Then
            
                Dim OutlookApp As Object
                Dim Mess As Object, Recip
                Recip = Target.Offset(0, 4)
               Set OutlookApp = CreateObject("Outlook.Application")
               Set Mess = OutlookApp.CreateItem(olMailItem)
               With Mess
                    .Subject = "Subject"
                    .Body = Target.Offset(0, -1) & Chr(10) & Target.Offset(0, -3) & Chr(10) & Target.Offset(0, -4)
                    .To = Recip
                    .Send
                End With
                Exit For
            End If
        Next Cell
    End If
    
End Sub

Steffen Thomsen

Thanks for the help.

I have used Richard's way, although I am having a few problems transferring the code from the test sheet to my actual sheet. I.e I will have to add a few more names and email addresses and the data will be in different columns.

Do you mind explaining what I need to change in order to do this?

Thanks.

Hi Jonny981,

You have code in 2 places you need to copy,

1 - From sheet1 code module
2 - From Module 1

These two you need to copy into the respective modules in your workbook.

The part you need to change to address different cells

is the following in Sheet1 module

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("E:E")) Is Nothing Then
        If Target = "Complete" Then
            Application.EnableEvents = False
            stDesc = Target.Cells(1, 0) 
            Range("LastSel") = Target.Cells(1, -1) ' this is the offset ref for description
            dt = Target.Cells(1, -2) ' this is the offset ref for date
            stRefNo = Target.Cells(1, -3) ' this is offset ref for Ref No
            steMail = Range("eMail")
            Call Module1.eMailCompleted
        
        End If

    End If
    Application.EnableEvents = True
End Sub

Hope you get it to work

Steffen Thomsen

Richard this would work for me as well...

Can I ask if there might be a way to this that would summarize by the "Raised By" and then sent either with a button press or perhaps on the save of the worksheet...

In my example I would have a similar worksheet but it could have 20 entries by 1 person and 6 by someone else and 1 by a 3rd... I'd like to only send 1 email to each person and have their 20, 6 or 1 entry respectively.

Great example... thanks for your help!!!!

Sorry guys I didn't realize this especially since it really is the same thing as the original post just an extension of what it does.

I have posted a reply in your thread.

Thanks again, although If I try to copy and paste anything in the sheet then I get a run time error. any ideas why?