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
Bookmarks