Adding Email Addresses From Named Ranges to Lotus Notes

[Marhier]

Good morning, I hope you're all well.
Please note that I have also posted this quetion to the following locations:
https://www.mrexcel.com/forum/genera...ml#post5108366
http://www.vbaexpress.com/forum/show...to-Lotus-Notes
https://www.ozgrid.com/forum/forum/...il-addressed-from-names-ranges-to-lotus-notes

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.

 

[Stevie]

Hi Marhier,
a little googling returned this:
https://www.ozgrid.com/forum/forum/...help/82193-sending-attachment-via-lotus-notes
I do not have the libraries required for manipulating lotus notes as we run outlook, but it appears all you need to do is add these lines:

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

I would suggest doing this before the line:

Call UIdoc.GotoField("Body")

I would assume that the line:

Call UIdoc.GotoField("To")

is redundant in your code as well.

Let me know if this works for you as like I said I do not have the setup to test it.
If this was a useful comment, please click 'Like' below!

Stevie

 

[Marhier]

Stevie, you're an absolute LEGEND!!!!
That was spot on!

You're obviously better at Google than me, as I couldn't pin point that bit of code.

You were right, the code:

Call UIdoc.GotoField("To")

was redundant... That was me experimenting and forgot to remove it before posting.

I can't thank you enough for this mate.

Kind regards
Marhier.

 

[Stevie]

No worries, best of luck with Lotus Notes.