Sub MailOpenLotus_Notes()
Dim Notes As Object, Maildb As Object, WorkSpace As Object, UIdoc As Object, UserName As String
Dim MailDbName As String
With Sheets("weekf")
wbname = .Range("I1").Value
iTotalPh = .Range("C65536").End(xlUp).Value
iCSF = .Range("D65536").End(xlUp).Value
iwIEP = .Range("E65536").End(xlUp).Value
End With
Set Notes = CreateObject("Notes.NotesSession")
UserName = Notes.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
Set Maildb = Notes.GetDatabase(vbNullString, MailDbName)
Set WorkSpace = CreateObject("Notes.NotesUIWorkspace")
Call WorkSpace.ComposeDocument(, , "Memo")
Set UIdoc = WorkSpace.CurrentDocument
'If cells are null, such as email address, cc, etc, then ignore and dont paste into email
On Error Resume Next
'TO: field in Lotus Notes
Recipient = Sheets("Lookup Table").Range("M1").Value
Call UIdoc.FieldSetText("EnterSendTo", Recipient)
'CC: field in Lotus Notes
ccRecipient = Sheets("Lookup Table").Range("M2").Value
Call UIdoc.FieldSetText("EnterCopyTo", ccRecipient)
'BCC: field in Lotus Notes
'bccRecipient = Sheets("Sheet1").Range("C21").Value
'Call UIdoc.FieldSetText("EnterBlindCopyTo", bccRecipient)
'SUBJECT: field in Lotus Notes
Subject1 = "Print head consumption trends"
Call UIdoc.FieldSetText("Subject", Subject1)
'BODY in Lotus Notes.
Call UIdoc.gotofield("Body")
body1 = ("Chris," & Chr(10) & Chr(10) & "Last week we ordered " & iTotalPh & " print heads; " & iCSF & " as CSF items, and " & iwIEP & " with IEPs." & Chr(10) _
& Chr(10) & "*******ATTACH FILE HERE*********" & Chr(10))
Call UIdoc.inserttext(body1)
'***ATTACHMENT Hopefully***
'Call UIdoc.gotofield("Body1")
'UIdoc.editmode = True
'Call UIdoc.CreateObject("EMBED_ATTACHMENT", "", "G:\Path\" & wbname)
'Set UIdoc = Nothing
'***End ATTACHMENT***
'Insert some carriage returns at the end of the email
Application.CutCopyMode = False
Set UIdoc = Nothing: Set WorkSpace = Nothing
Set Maildb = Nothing: Set Notes = Nothing
Set body = Nothing
End Sub
Bookmarks