Trigger to send email automatically from Excel not working

[ryeire]

Hi all,

I am creating a database for logging data in excel. I have a lot of it completed but I am encountering a problem when trying to send an email.

So ideally what I want is that when "Yes" is entered into any cell in column E it will trigger an email to be sent to certain people. However I have encountered an error everytime I have tried it. I'm not sure if this is relevant info but I also have this section of my code in a sheet.

I will have my code attached below. Can anyone offer any insight or help for my problem?

Thanks in advance.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("E:E")) Is Nothing Then
        Select Case Range("E:E")
            Case "X": Email_from_Excel
        End Select
    End If
    End Sub


Sub Email_from_Excel()

Dim emailApplication As Object
Dim emailItem As Object

Set emailApplication = CreateObject("Outlook.Application")
Set emailItem = emailApplication.CreateItem(0)

'building the email

emailItem.to = ""    'use a ; to seperate email address'
'emailItem.CC = ""
emailItem.Subject = "Enter Subject"            'For example: station needs repair
emailItem.Body = "Enter message"

'sending the email
'emailItem.Send

emailItem.Display  '- choose send or display

Set emailItem = Nothing
Set emailApplication = Nothing

End Sub

 

[mumps]

You don't need two macros. Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet 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 "Yes" in column E and press the RETURN key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 5 Then Exit Sub
    Application.ScreenUpdating = False
    Dim OutApp As Object, OutMail As Object
    If Target = "Yes" Then
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .To = ""
            .Subject = "Enter Subject"
            .HTMLBody = "Enter message"
            .Display
        End With
    End If
    Application.ScreenUpdating = True
End Sub

 

[ryeire]

@mumps Thank you very much for your help it works perfectly

 

[mumps]

You are very welcome.