Adding Email Addresses From Names Rages to Lotus Notes

Marhier

Board Regular
Joined
Feb 21, 2017
Messages
128
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
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.


Code:
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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Turned out all I needed to add was the following:

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

This needed to go just before the following:
Code:
Call UIdoc.GotoField("Body")

This has now solved my issue.

Regards
Marhier
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top