email only when a certain cell is populated

[rooster05]

i have code to email when an address (several in dropdown list) which is housed in C51, but what i need is for this to only be active or be able to be sent when C50 is populated or at least contain certain text.

would this be possible to do

many thanks

Private Sub CommandButton1_Click()Dim OutApp As Object
Dim OutMail As Object
Dim mess As Object, recip As String
recip = [c51].Value
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.Recipients.Add recip
.CC = ""
.BCC = ""
.Subject = ""
.Body = ""
.Attachments.Add ActiveWorkbook.FullName
.display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[CalcSux78]

Should be able to achieve by adding proper code to the sheet level of your workbook.

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range


Set rng = Intersect(Target, [C51])
If Not rng Is Nothing Then
    Dim OutMail As Object
    Dim mess As Object, recip As String
    
    recip = [C51].Value
    If recip.Value Like "?*@?*.?*" Then
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        On Error Resume Next
        With OutMail
            .Recipients.Add recip
            .CC = ""
            .BCC = ""
            .Subject = ""
            .Body = ""
            .Attachments.Add ActiveWorkbook.FullName
            .display
        End With
        On Error GoTo 0
        Set OutMail = Nothing
        Set OutApp = Nothing
    End If
End If
End Sub

 

[rooster05]

Many thanks for this, however when i use this code i get a compile error: invalid qualifier, i kept the code as is and i have also substituted the ?*@?*.?*please select. could you advise what i'm doing wrong or missing

many thanks

 

[CalcSux78]

With Option Explicit, all variables must be declared before using them. I over looked it too, but we are missing declaration of OutApp.

    Dim OutMail As Object
[COLOR=#ff0000]    Dim OutApp As Object  [/COLOR][COLOR=#008000]'This was missing from code for post #2[/COLOR][COLOR=#ff0000][/COLOR]
    Dim mess As Object, recip As String

 

[rooster05]

thank you for the speedy quick reply, again i'm having issues with the line " If recip.Value Like "?*@?*.?*" Then "- recip is highlighted blue, is there something i'm doing wrong

 

[CalcSux78]

I think recip is throwing things off... Try replacing the whole thing with this:

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
'Working in Excel 2000-2016
'This example send the last saved version of the Activeworkbook
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm


Dim OutApp As Object
Dim OutMail As Object
Dim rng As Range
Dim Whom$


Set rng = Intersect(Target, [C51])
If Not rng Is Nothing Then
    Whom = [C51]
    If Not Whom Like "?*@?*.?*" Then Exit Sub
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)


    On Error Resume Next
    With OutMail
        .to = Whom
        .CC = ""
        .BCC = ""
        .Subject = ""
        .Body = ""
        .Attachments.Add ActiveWorkbook.FullName
        .Display    'or use .Send
    End With
    On Error GoTo 0


    Set OutMail = Nothing
    Set OutApp = Nothing
End If
End Sub

 

[rooster05]

Works a treat, thank you very much

 

[rooster05]

i have a quick question re this code, how do i code it to send a completed form without saving it