Help creating lotus email with attachments from excel look up list

[ecologyemiss]

Hello,

Out of my depth here. I know what I want but cannot find the exact*VBA*to solve my query. Hopefully one of you clever sparks has some code that can help.

I have a basic excel document which has the following columns:

A- Address(s)- To send email to
B- CC Address- To CC email to*
C- Subject- Subject of email
D- Body of the email (Text)
E- File path- What lotus needs to attach

The theory is Open Lotus Notes -> Create Draft -> To (lookup column a) -> CC (lookup column b) -> Subject (lookup column c) -> Body (lookup column D) -> Attach file (s) (lookup column e)

I would put this macro on a button, and copy paste on 78 rows so if necessary I could draft specific rows rather than a loop through all of them

Any ideas

[ranman256]

I dug up my old LOTUS code...start here....

    'email all OnCall emps
'************************
Public Sub Send(pvSendTo,pvSubject,pvBody, pvFile)
'************************
Dim doc As Object  'NOTESUIDOCUMENT
Dim richText As Object   'NOTESRICHTEXTITEM
Dim bflag As Boolean
Dim sMsg As String
Dim vFilepath, vName
On Error GoTo errSend
mvError = 0
Set ns = CreateObject("Notes.Notessession")     'create notes session
Set db = ns.CURRENTDATABASE
'Set ws = createworkspace
Dim bflag As Boolean
Call db.OPENMAIL                              'set database to default mail database
'bFlag = True
If Not (db.ISOPEN) Then bflag = db.Open("", "")
'If Not bFlag Then
   MsgBox "Can't open mail file: " & db.Server & " " & db.FilePath
   mvError = mkERR_SERVER
'End If

Select Case True
 Case mvSendTo = ""
    mvError = mkERR_NOSENDTO
    MsgBox "No 'Send To' address.", vbCritical, "Error"
    Exit Sub
 Case mvFile = ""
   ' mvError = mkERR_NOATTACH
   ' MsgBox "Attachment:'" & pvFile & "' not found.", vbCritical, "Error"
   ' Exit Sub
End Select
If mvSubject = "" Then mvSubject = "No Subject"
  
 'Set ws = New NOTESUIWORKSPACE
 
 'fill in document specs
     '   ' Set doc = db.Documents
     Set doc = db.CREATEDOCUMENT                   'notesdocument '.New  '(db)   ' create a mail document
     
  'creat rich text box so we can attach
      Set richText = doc.CREATERICHTEXTITEM("BODY")
      With doc
         .Subject = pvSubject
         .SendTo = pvSendTo
         .Body = pvBody
      End With
 
  If mvFile <> "" Then
  'attach the doc
      Call richText.EMBEDOBJECT(1454, "", pvFile)
  End If
  
 'send the message
     doc.Send False
resumeErr:
    'recover memory
Set doc = Nothing
Set richText = Nothing
Exit Sub
errSend:
If Err = 7296 Then Resume Next
mvError = Err
'MsgBox Err.Description, , Err
Resume resumeErr
End Sub