Exception Handling of invalid email address

In general the way that you do exception handling is

On Error Resume Next ' turn on error handling
' perform some function
If (Err.Num <> 0) Then
    ' do the error handling here
Else
    ' do normal processing
End If
On Error GoTo 0 ' turn off error handling

wrap this around the code you want to handle. Hope this helps.

Hello ncduke,

In this case, you don't need error handling. The range Find method returns one of two possible values. If the value is found then the cell, itself a Range object, will be returned. If not then the special Object value Nothing is returned.

By assigning a range object variable to the Find operation, you can then test the result and avoid an error. I have made the needed changes to your code and included comments. Here is the revised macro.

Sub Send_Excel_Cell_Content_To_Lotus_Notes()

Dim NameRange As Range
Dim Notes As Object
Dim RngRnd As Range
Dim WorkSpace As Object
Dim UIdoc As Object
Dim UserName As String
Dim PI_name As Variant
''Dim i As Integer

''i = 0

ThisWorkbook.Sheets("EMAIL_LIST").Activate

' Locate the header
  Set NameRange = Cells.Find(What:="PI_Name", LookIn:=xlValues, MatchCase:=False)
 
' If the header was not found, exit the macro with a message
  If NameRange Is Nothing Then MsgBox "Header 'PI_name' Not Found.": Exit Sub

' Change the NameRange to point to the first name in the list
  Set NameRange = NameRange.Offset(1, 0)

' Find the last name in the list
  Set RngEnd = Cells(Rows.Count, NameRange.Column).End(xlUp)

' If there are no names, exit the macro with a message
  If RngEnd.Row < NameRange.Row Then MsgBox "No Names were found.": Exit Sub

' Change the NameRange to include all the names in the list
  Set NameRange = Range(NameRange, RngEnd)

''ActiveCell.Offset(1, 0).Activate

''PI_name = ActiveCell.Value

''ActiveWorkbook.Names.Add Name:="Bookmark", RefersTo:=ActiveCell

' Step through each name and send an email
  For Each PI_name In NameRange
    
    Set Notes = CreateObject("Notes.NotesSession")
    UserName = Notes.UserName
    
    Set WorkSpace = CreateObject("Notes.NotesUIWorkspace")
    
    Call WorkSpace.ComposeDocument(, , "Memo")
    
    Set UIdoc = WorkSpace.CurrentDocument
    
    ' Recipient = ThisWorkbook.Sheets("EMAIL_LIST").Range("A2").Value
      Recipient = PI_name.Value
    
      Call UIdoc.FieldSetText("EnterSendTo", Recipient)
    
      Subject1 = "Testing from Macro"
      Call UIdoc.FieldSetText("Subject", Subject1)
    
      Call UIdoc.GotoField("Body")
      Body1 = "Hi This is a test"
      Call UIdoc.InsertText(Body1)
    
    ' Insert some carriage returns at the end of the email
      Call UIdoc.InsertText(vbCrLf & vbCrLf)
    
      UIdoc.send (True)
      UIdoc.Close
     
    Set UIdoc = Nothing: Set WorkSpace = Nothing: Set Notes = Nothing
    
    ''Application.Goto Reference:="Bookmark"
    ''i = i + 1
    ''PI_name = ActiveCell.Offset(i, 0).Value
    
  Next PI_name

End Sub

Here's a UDF that tests for a valid email address

Option Explicit

Public Function ValidEmail(pAddress As String) As Boolean
     '-----------------------------------------------------------------
    Dim oRegEx As Object
    Set oRegEx = CreateObject("VBScript.RegExp")
    With oRegEx
        .Pattern = "^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"
        ValidEmail = .Test(pAddress)
    End With
    Set oRegEx = Nothing
End Function