PDA

View Full Version : Help creating lotus email with attachments from excel look up list



ecologyemiss
05-14-2014, 07:40 AM
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
05-14-2014, 01:49 PM
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