Hi,

I am trying to email a list of people using Macro. I have the list in a column and I am looping through the column to email each one with a specified message coming from another column on the same worksheet.

I do have the email macro going well but I have a question. If the email is incorrect, the macro halts on that email. I want to do some exception handling for the same. Can you suggest something ?

Also I am a newbie to the excel macro world, so i am pasting my code for your verification and suggestion for modification and enhancement.


Sub Send_Excel_Cell_Content_To_Lotus_Notes()

Dim Notes As Object
Dim WorkSpace As Object
Dim UIdoc As Object
Dim UserName As String
Dim PI_name As String
Dim i As Integer

i = 0
On Error Resume Next

ThisWorkbook.Sheets("EMAIL_LIST").Activate

Cells.Find(What:="PI_Name", LookIn:=xlValues, MatchCase:=False).Activate

ActiveCell.Offset(1, 0).Activate

PI_name = ActiveCell.Value

ActiveWorkbook.Names.Add name:="Bookmark", RefersTo:=ActiveCell

Do While PI_name <> ""
    
    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 = ActiveCell.Offset(1, 1).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
    
Loop

End Sub