Automatic sending of email

[damian_r_Home]

Morning all.

I know that the following is possible and is likely done in VBA or a macro (trust me when I say that's the limit of my knowledge on either of them but for the rest of this post I'll refer to it as code) but I'm looking for an email to be sent via Microsoft Outlook when a cell has a certain value entered into it.

But there is a little more to that as well as follows - hopefully the image will help with what I need some serious high level help with

On Sheet 1 name/initials will be entered in column A.
When Y is entered in column B, the code will need to go to sheet 2, scan down the list of names in column A so that it can find the correct email in column B.
The code then needs to open up a Microsoft new email with the correct email address AND in the subject line take the data from sheet 1 cells and place it in the subject of the email

In many ways I know that this may all sound very simple but in reality I know it will be a big thing to get this to work and anyone brave enough to try and get this to work will have to remember that when it comes to this level of code I have absolutely no idea whatsoever of what id be doing.

Best wishes

Damian

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a "Y" in column B and press the RETURN key or TAB key. The email will be automatically created.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim OutApp As Object, OutMail As Object, rName As Range
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    If Target = "Y" Then
        Set rName = Sheets("Sheet2").Range("A:A").Find(Target.Offset(, -1).Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not rName Is Nothing Then
            With OutMail
                .To = rName.Offset(, 1)
                .Subject = Range("D" & Target.Row).Value & ", " & Range("E" & Target.Row).Value & ", " & Range("F" & Target.Row).Value
                .HTMLBody = ""
                .Display
            End With
        End If
    End If
    Application.ScreenUpdating = True
End Sub

 

[damian_r_Home]

Good Morning Mumps.

I have to admit I didn't expect to get a reply so quickly with an actual answer to my query.

Many many thanks for taking the time to help with this - I shall now implement it in time to return to work in the New Year.

Whatever your Christmas brings you this year I hope it at least includes a smile or two and memories to keep.

Stay safe.

D

 

[mumps]

Thank you and the same to you. ?