anyone knows if I can use macro to send out the reports by email through Lotus? If yes, how

i.e. I've to send hugh amount of reports out each day by running macro in Excel which always takes me 1 hour to d
at this moment, I can only wait to finish splitting reports, then go to Lotus and send those reports ou
I wanna to check if I can do it at the same time in one code, so that I can send one report out once it's ready

Thanks for hel

Stel wrote:
anyone knows if I can use macro to send out the reports by email through Lotus? If yes, how?


Take a look at
http://www.rondebruin.nl/sendmail.htm
which has a lot of options for sending mail from Excel, some of which shold work with Lotus
Notes.


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

Try this - I currently have used Lotus Notes at several client sites and
this has worked for me with little or no amendment:

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

Function SendMail()

On Error GoTo SendMailError

EMailSendTo = " '' 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 follow established contact procedures should
you have any questions."
.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


--

Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"


"Stel" wrote in message
...
anyone knows if I can use macro to send out the reports by email through
Lotus? If yes, how?

i.e. I've to send hugh amount of reports out each day by running macro in
Excel which always takes me 1 hour to do
at this moment, I can only wait to finish splitting reports, then go to
Lotus and send those reports out
I wanna to check if I can do it at the same time in one code, so that I
can send one report out once it's ready

Thanks for help

Andy
Do you have any help for me....I am using LotusNotes and I get your program to work except for attaching files
I generate Error 438 which says Objects doesn't support this method or property....or something like that. Can you help me

-Beverl

----- Andy Wiggins wrote: ----

Try this - I currently have used Lotus Notes at several client sites an
this has worked for me with little or no amendment

Dim objNotesSession As Objec
Dim objNotesMailFile As Objec
Dim objNotesDocument As Objec
Dim objNotesField As Objec

Function SendMail(

On Error GoTo SendMailErro

EMailSendTo = " '' Required - Send to addres
EMailCCTo = "" '' Optiona
EMailBCCTo = "" '' Optiona

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

''Establish Connection to Mail Fil
'' .GETDATABASE("SERVER", "FILE"
Set objNotesMailFile = objNotesSession.GETDATABASE("", ""
''Open Mai
objNotesMailFile.OPENMAI

''Create New Mem
Set objNotesDocument = objNotesMailFile.CREATEDOCUMEN

''Create 'Subject Field
Set objNotesField = objNotesDocument.APPENDITEMVALUE("Subject"
EMailSubject

''Create 'Send To' Fiel
Set objNotesField = objNotesDocument.APPENDITEMVALUE("SendTo"
EMailSendTo

''Create 'Copy To' Fiel
Set objNotesField = objNotesDocument.APPENDITEMVALUE("CopyTo"
EMailCCTo

''Create 'Blind Copy To' Fiel
Set objNotesField = objNotesDocument.APPENDITEMVALUE("BlindCopyTo"
EMailBCCTo

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

With objNotesFiel
.APPENDTEXT "This e-mail is generated by an automated process.
.ADDNEWLINE
.APPENDTEXT "Please follow established contact procedures shoul
you have any questions.
.ADDNEWLINE
End Wit

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

''Send the e-mai
objNotesDocument.Send (0

''Release storag
Set objNotesSession = Nothin
Set bjNotesSession = Nothin
Set objNotesMailFile = Nothin
Set objNotesDocument = Nothin
Set objNotesField = Nothin

''Set return cod
SendMail = Tru

Exit Functio

SendMailError
Msg = "Error # " & Str(Err.Number) & " was generated by "
& Err.Source & Chr(13) & Err.Descriptio
MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContex

SendMail = Fals

End Functio


--

Regard
Andy Wiggin
www.BygSoftware.co
Home of "Save and BackUp"
"The Excel Auditor" and "Byg Tools for VBA


"Stel" wrote in messag
..
anyone knows if I can use macro to send out the reports by email throug
Lotus? If yes, how
i.e. I've to send hugh amount of reports out each day by running macro i
Excel which always takes me 1 hour to d
at this moment, I can only wait to finish splitting reports, then go t
Lotus and send those reports ou
I wanna to check if I can do it at the same time in one code, so that
can send one report out once it's read
Thanks for hel