Hi All, Newbie on this board, but have found a lot of useful information on here so far, so thought i would pose my question for you lovely people

I have been at this for days now, so i may well be missing the obvious. I have a module which works completely fine if i hardcode email addresses within it, however as this is a report the receipients may well change, therefore i have set up a recipient list on a worksheet, Cell B2 contains the start range for the 'vaRecipTo' email addresses and Cell C2 contains the start range for the 'vaRecipCopy' email addresses. Now i did intially have my loop working but that is no longer the case and i seem to have frazzled my brain so any help on this would be much appreciated.

When my loop was working i wasn't receiving any errors, but the email was only being sent if there was a to recipient or CC recipient on there own. Otherwise, Lotus was returning a deliver failure notice which formated the 2 emails addresses like: <[email protected], FirstName.Surname>@Email.com which obviously meant that they were not being received. I have seen many issues with this multiple recipients all over the net, but cannot seem to find anything which relates directly to my code.

Firstly i was hoping someone could help me fix my loop? Once this is done, i will hopefully be able to specify more about my original issue with the email addresses appearing wrong.

Sub SendEmail()

'MsgBox ActiveWB
If MsgBox("If you would like this report to automatically be emailed to the recipients please click Yes, otherwise Click No.", vbYesNo) = vbNo Then Exit Sub

Dim Maildb As Object
Dim MailDoc As Object
Dim Body As Object
Dim Session As Object
Dim AttachME As Object
Dim EmbedObj As Object 'The embedded object (Attachment)
Dim Signature As String
Dim vaRecipTo As Variant
Dim vaRecipCopy As Variant
Dim amountOfRecipientsTo As Integer
Dim amountOfRecipientsCopy As Integer

Sheets("Run Analysis").Select
ActiveWB = Range("F20").Value

'Create Static Signature
'Signature removed for privacy

'Store the Recipients in an arr
Sheets("Recipients").Activate

For Each Cell In Columns("C")
        If Cell.Value Like "*@*" Then
        vaRecipTo = (vaRecipTo & ", ")
        End If
Next
'MsgBox vaRecipTo

    
Set Session = CreateObject("Lotus.NotesSession")
Call Session.Initialize("")

Set Maildb = Session.GetDatabase("Server", "Filename.nsf")
If Not Maildb.IsOpen = True Then
Call Maildb.Open
End If

Set MailDoc = Maildb.CreateDocument

'Populate the Header of the mail
Call MailDoc.ReplaceItemValue("Form", "Memo")
Call MailDoc.ReplaceItemValue("SendTo", vaRecipTo)
Call MailDoc.ReplaceItemValue("CopyTo", vaRecipCopy)

'The line below contains the hardcoded email address for the report to be sent to, uncomment If necessary and use instead of array.
'Lines commented out for privacy

Call MailDoc.ReplaceItemValue("Subject", "Monitoring Report " & Format(Now(), "dd mm yyyy "))

'Populate the body of the mail
Set Body = MailDoc.CREATERICHTEXTITEM("Body")
Call Body.AppendText("All," & vbCrLf & vbCrLf & "Please find attached the Orbita Alert Monitoring Report " & Format(Now(), "dd mm yyyy ") & "." & vbCrLf & vbCrLf & Signature)
Call Body.AddNewLine(2)

'Add an attachment
'If Attachment <> "" Then
Set AttachME = MailDoc.CREATERICHTEXTITEM(ActiveWB)
Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", ActiveWB)
'MailDoc.CREATERICHTEXTITEM (ActiveWB)
'End If

MailDoc.SaveMessageOnSend = True
Call MailDoc.ReplaceItemValue("PostedDate", Now())


Call MailDoc.Send(False)

Set Maildb = Nothing
Set MailDoc = Nothing
Set Body = Nothing
Set Session = Nothing

MsgBox "The Report has been sent"

Application.ScreenUpdating = True
Application.DisplayAlerts = True
ActiveWorkbook.Close False

End Sub
Thank you all in advance

Regards
Donna