vba email send from excel - ignore second email address if "ENTER EMAIL ADDRESS"

[nigelog]

I am using the below code to email a worksheet from a macro using email addresses in cells chosen from a dropdrown list. The last cell reference is to allow the user to enter email address not available on the dropdown lists, an "ad hoc" address that the code then changes the cell entry back to "ENTER EMAIL ADDRESS".

I would like the macro to test the value of cell [f14] and if "ENTER EMAIL ADDRESS" ignore and do not add to the recipient list along with cell [I6]

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With Destwb
        
        .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
        On Error Resume Next
        With OutMail
            .to = [i6].Value & ";" & [F14].Value
            .CC = [i10].Value
            .BCC = ""
            .Subject = "Incident Report"
            .Body = "Incident Report Attached"
            .Attachments.Add Destwb.FullName
            '.Send
            .DISPLAY

any assistance appreciated. Rgds....

[nigelog]

    TempFilePath = Environ$("temp") & "\"
    TempFileName = Sourcewb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
        
        If [f14].Value = "ENTER EMAIL ADDRESS" Then
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With Destwb
        
        .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
        On Error Resume Next
        

        
        With OutMail
            .to = [i6].Value
            .CC = [i10].Value
            .BCC = ""
            .Subject = "Incident Report"
            .Body = "Incident Report Attached"
            .Attachments.Add Destwb.FullName

            .DISPLAY
        End With
        On Error GoTo 0
        .Close savechanges:=False
    End With


    Kill TempFilePath & TempFileName & FileExtStr

    Set OutMail = Nothing
    Set OutApp = Nothing
        With Application
        .ScreenUpdating = True
        .EnableEvents = True
    ActiveSheet.Protect
    End With
    Call data
    
    
    Else
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With Destwb
        
        .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
        On Error Resume Next
            With OutMail
            .to = [i6].Value & ";" & [f14].Value
            .CC = [i10].Value
            .BCC = ""
            .Subject = "Incident Report"
            .Body = "Incident Report Attached"
            .Attachments.Add Destwb.FullName

            .DISPLAY
        End With
        On Error GoTo 0
        .Close savechanges:=False
    End With


    Kill TempFilePath & TempFileName & FileExtStr

    Set OutMail = Nothing
    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    ActiveSheet.Protect
    End With
    Call data
    End If
End Sub

longwinded way of doing it but does the job....

[cytop]

Assign the first email address to the field including the semi-colon separator; then, if the second cell contains a valid email address, append that to the 'To' field. Alternatively use an inline IIF() function to check the value of the second.

 .To = [I6].Value & ";"

If [F14].Value <> "WHATEVER" Then
   .To = .To & [F14].Value
End If

 .To = [I6].Value & ";" & IIF([F14].Value <> "WHATEVER", [f14].Value, vbNullString)

IF (or the Inline version, IIF()) is the most basic of VBA control structures - Something you need to know, and there's absolutely no need for the 'long winded' version.

[nigelog]

Thank You Cytop, much neater....will look up IIF function now