N
Nigel
I m using the following code to send a worksheet from within excel,
everything works fine except to the point where it is actually attaching the
file then I get a message box informing me that Excel is waiting for another
appilcation to finish an OLE Operation which I have to click on OK to get it
to continue. Any ideas how I can beat the message box
here is the line at which point I get the message
objNotesField = objNotesField.EMBEDOBJECT(1454, "", "c:\spreadsheets\Daily
Sales Report " & strDate & ".xls")
On Error Resume Next
EMailSendTo = "(e-mail address removed)" '' Required - Send to address
EMailCCTo = "" '' Optional
EMailBCCTo = "" '' Optional
''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 "This e-mail is generated by an automated process."
.ADDNEWLINE 1
.APPENDTEXT "Please contact Nigel Bennett at 780-555-8808 should you
have any questions."
.ADDNEWLINE 2
End With
''Attach the file --1454 indicate a file attachment
ActiveSheet.Copy
strDate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss")
ActiveWorkbook.SaveAs "c:\spreadsheets\Daily Sales Report " & strDate &
".xls"
objNotesField = objNotesField.EMBEDOBJECT(1454, "", "c:\spreadsheets\Daily
Sales Report " & strDate & ".xls")
''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
everything works fine except to the point where it is actually attaching the
file then I get a message box informing me that Excel is waiting for another
appilcation to finish an OLE Operation which I have to click on OK to get it
to continue. Any ideas how I can beat the message box
here is the line at which point I get the message
objNotesField = objNotesField.EMBEDOBJECT(1454, "", "c:\spreadsheets\Daily
Sales Report " & strDate & ".xls")
On Error Resume Next
EMailSendTo = "(e-mail address removed)" '' Required - Send to address
EMailCCTo = "" '' Optional
EMailBCCTo = "" '' Optional
''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 "This e-mail is generated by an automated process."
.ADDNEWLINE 1
.APPENDTEXT "Please contact Nigel Bennett at 780-555-8808 should you
have any questions."
.ADDNEWLINE 2
End With
''Attach the file --1454 indicate a file attachment
ActiveSheet.Copy
strDate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss")
ActiveWorkbook.SaveAs "c:\spreadsheets\Daily Sales Report " & strDate &
".xls"
objNotesField = objNotesField.EMBEDOBJECT(1454, "", "c:\spreadsheets\Daily
Sales Report " & strDate & ".xls")
''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