Adding Email Addresses From Names Rages to Lotus Notes

[Marhier]

Good morning, I hope you're all well.
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/copy-paste-and-lotus-notes-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.

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

[COLOR=#008000]'Set email addresses[/COLOR]
EmailAddress = Range("BuyerEmail").Value
ccEmailAddress = Range("ccBuyer1").Value & "; " & Range("ccBuyer2").Value

[COLOR=#008000]'Unprotect sheet[/COLOR]
Call PR_UnProtect

[COLOR=#008000]'Filter column I by "O" and copy the selection as a picture[/COLOR]
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

[COLOR=#008000]'Open Lotus Notes & Get Databas[/COLOR]e
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)

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

[COLOR=#008000]'Add Picture & text[/COLOR]
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))

[COLOR=#008000]'Unfilter active sheet[/COLOR]
wsSheet.AutoFilter.ShowAllData
Range("A1").Select
    
[COLOR=#008000]'Protect sheet[/COLOR]
Call PR_Protect
Application.ScreenUpdating = True
End Sub

Kind regards
Marhier.

 

[Marhier]

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

 

[Macropod]

Cross-posted at: http://www.vbaexpress.com/forum/sho...ail-Addresses-From-Names-Rages-to-Lotus-Notes
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[Marhier]

Hi Macropod.
I'd just like to point out that I created this post 20/07/18 and waited nearly 3 days before posting on VBA Express.
If you read the post I made at VBA Express, I clearly link to this one, which I thought was fair.
I didn't realise I had to update here.
Apologies.