Email File Through Lotus Notes


New Member
Dec 12, 2005
I found some code on the web that will allow me to email a file through my company's email program, Lotus Notes (which for the record, I can't stand). All I want to do is to send a file as an attachment to each physician in my company separately, with the physician's name in the subject heading. I have the physicians' email addresses and names listed in the spreadsheet. In the main body of my program, I loop through each of the physicians and call the below function:

SendMail (Email_Address, Dr_Name)

When I try to compile the program, it stops on the line above and says the syntax is invalid. I tried some testing and the function will work fine if I try to use only ONE parameter, either Email_Address or Dr_Name. I don't see why it shouldn't work if I tried to pass both parameters. Does anyone have any ideas? Below is the function I am borrowing. I appreciate your help!

'Based on code from:

Dim objNotesSession As Object
Dim objNotesMailFile As Object
Dim objNotesDocument As Object
Dim objNotesField As Object

Function SendMail(Email_Add As String, Dr_Name As String)

On Error GoTo SendMailError

EMailSendTo = Email_Add '' Required - Send to address
EMailCCTo = "" '' Optional
EMailBCCTo = "" '' Optional
EmailSubject = "LVC Incentive Comp-" & Dr_Name

''Establish Connection to Notes
Set objNotesSession = CreateObject("Notes.NotesSession")

''Establish Connection to Mail File
Set objNotesMailFile = objNotesSession.GETDATABASE("", "")
''Open Mail

''Create New Memo
Set objNotesDocument = objNotesMailFile.CREATEDOCUMENT

''Create 'Subject Field'
Set objNotesField = objNotesDocument.APPENDITEMVALUE("Subject", EmailSubject)

''Create 'Send To' Field
Set objNotesField = objNotesDocument.APPENDITEMVALUE("SendTo", EMailSendTo)

''Create 'Copy To' Field
Set objNotesField = objNotesDocument.APPENDITEMVALUE("CopyTo", EMailCCTo)

''Create 'Blind Copy To' Field
Set objNotesField = objNotesDocument.APPENDITEMVALUE("BlindCopyTo", EMailBCCTo)

''Create 'Body' of memo
Set objNotesField = objNotesDocument.CREATERICHTEXTITEM("Body")

With objNotesField
.APPENDTEXT "Inform_Title"
.APPENDTEXT "Attached is the quarterly incentive for the following physician. Please feel free to contact me should you have any questions."
.APPENDTEXT "This e-mail is generated by an automated process."
End With

''Attach the file --1454 indicate a file attachment
''objNotesField = objNotesField.EMBEDOBJECT(1454, "", "C:\Temp\test.xls")
objNotesField = objNotesField.EMBEDOBJECT(1454, "", ActiveWorkbook.FullName)

''Send the e-mail
objNotesDocument.Send (0)

''Release storage
Set objNotesSession = Nothing
Set bjNotesSession = Nothing
Set objNotesMailFile = Nothing
Set objNotesDocument = Nothing
Set objNotesField = Nothing

''Set return code
SendMail = True

Exit Function

Msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext

SendMail = False

End Function

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Watch MrExcel Video

Forum statistics

Latest member