Email File Through Lotus Notes

ssh

New Member
Joined
Dec 12, 2005
Messages
34
Hello,
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:
'http://www.bygsoftware.com/Excel/VBA/email_from_lotus_notes.htm

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
'' .GETDATABASE("SERVER", "FILE")
Set objNotesMailFile = objNotesSession.GETDATABASE("", "")
''Open Mail
objNotesMailFile.OPENMAIL

''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"
.ADDNEWLINE 1
.APPENDTEXT "Attached is the quarterly incentive for the following physician. Please feel free to contact me should you have any questions."
.ADDNEWLINE 2
.APPENDTEXT ""
.ADDNEWLINE 1
.APPENDTEXT "This e-mail is generated by an automated process."
.ADDNEWLINE 2
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

SendMailError:
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
 

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

Forum statistics

Threads
1,140,912
Messages
5,703,128
Members
421,276
Latest member
davidfrommke

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
Top