Adding Email Addresses From Names Rages to Lotus Notes

[Marhier]

Good morning, I hope you're all well.
I'd first like to point out that I originally posted this question on the Mr. Excel forum last week
https://www.mrexcel.com/forum/genera...ml#post5108366


This might be a tricky one and hoping someone can help.

I'd first like to point out that my VBA knowledge basic and the majority of the code I've used came from the final post at the following link:
http://www.utteraccess.com/forum/cop...-t1419237.html

What I'm trying to achieve:
Filter my worksheet by column I for anything with the text "O" in.
If there isn't anything there, bring up a notification and Exit Sub
Otherwise, copy that selection as a picture, create a new email in Lotus Notes putting the email address from the named range "BuyerEmail" in the To: field, and the email addresses from the named ranges "ccBuyer1" & "ccBuyer2" in the Cc: field
Paste the picture, add some text to the body of the email and then unfilter the worksheet back to how it was.
End Sub

The following code works fine, but the issue I'm having is figuring out how to amend the code so it takes the email addresses I've got in my named ranges and putting them into the To: and Cc: fields in Lotus Notes - as mentioned above.

So far, all I've done is set EmailAddress and ccEmailAddress As Strings and then referenced them to the named ranges I have set.

I don't know where to go from here.

Any support would be greatly appreciated.
Thank you.

Sub NotifyBuyer()
Application.ScreenUpdating = False
Dim wsSheet As Worksheet, rRng As Range
Set wsSheet = ActiveSheet
Set rRng = wsSheet.Range("$A$9:$AC$1009")
Dim Notes As Object, db As Object, WorkSpace As Object
Dim UIdoc As Object, UserName As String, MailDbName As String
Dim AttachMe As Object, EmbedObj As Object
Dim EmailAddress As String
Dim ccEmailAddress As String

'Set email addresses
EmailAddress = Range("BuyerEmail").Value
ccEmailAddress = Range("ccBuyer1").Value & "; " & Range("ccBuyer2").Value

'Unprotect sheet
Call PR_UnProtect

'Filter column I by "O" and copy the selection as a picture
With rRng
    .AutoFilter Field:=9, Criteria1:="O"
    If .SpecialCells(xlCellTypeVisible).Address = .Rows(1).Address Then
    MsgBox "There are no lines set as 'To Order' - Status 'O'."
    wsSheet.AutoFilter.ShowAllData
    Range("A1").Select
    Application.ScreenUpdating = True
Exit Sub
Else
End If
End With
Range("A9:I9").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.CopyPicture

'Open Lotus Notes & Get Database
Set Notes = CreateObject("Notes.NotesSession")
UserName = Notes.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, _
    (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
Set db = Notes.GetDatabase(vbNullString, MailDbName)

'Create & Open New Document
Set WorkSpace = CreateObject("Notes.NotesUIWorkspace")
Call WorkSpace.COMPOSEDOCUMENT(, , "Memo")
Set UIdoc = WorkSpace.CURRENTDOCUMENT
Call UIdoc.GotoField("To")

'Add Picture & text
Call UIdoc.GotoField("Body")
Call UIdoc.INSERTTEXT(WorksheetFunction.Substitute( _
    "Hello@@The following has been released on the plant register for review:@@", _
    "@", vbCrLf))
Call UIdoc.Paste
Call UIdoc.INSERTTEXT(Application.Substitute( _
    "@@Thank you", "@", vbCrLf))

'Unfilter active sheet
wsSheet.AutoFilter.ShowAllData
Range("A1").Select
    
'Protect sheet
Call PR_Protect
Application.ScreenUpdating = True
End Sub

Kind regards
Marhier.

[mancubus]

check this out:
http://www.vbaexpress.com/forum/show...gh-Lotus-Notes

[Marhier]

Thanks mancubus, but turned out all I needed to add was the following:

Call UIdoc.FieldSetText("EnterSendTo", EmailAddress)
Call UIdoc.FieldSetText("EnterCopyTo", ccEmailAddress)

This needed to go just before the following:

Call UIdoc.GotoField("Body")

This has now solved my issue.

Regards
Marhier