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