Excel and Lotus Notes

    Hi All,


    One of the biggest issues I am having is producing VBA code to email the current workbook as an attachment in LOTUS NOTES. I know you can reference the object, but I don't know how to actually get it started. I can't use the application.sendmail method because it uses the useless Microsoft Exchange app (I need to send it via Lotus Notes)


    Any assistance would be appreciated


    Thanks


    DW

    I can't say I've tried this but here's a solution I found elsewhere:


    Public Sub SendMail(strAddressee As String, strSubject As String, strBody As String)


    Dim objNotesWS As Object
    Dim notesdb As Object


    Set objNotesWS = CreateObject("Notes.NotesUIWorkspace")


    Set notesdb = objNotesWS.COMPOSEDOCUMENT(, , "memo")


    notesdb.FIELDSETTEXT "EnterSendTo", strAddressee notesdb.FIELDSETTEXT
    "Subject", strSubject notesdb.FIELDSETTEXT "Body", strBody


    notesdb.Send


    notesdb.Close Set notesdb = Nothing Set objNotesWS = Nothing


    End Sub




    Hope this helps!


    Regards


    Jeff

    Thanks for you prompt reply Jeff, but unfortunately I recieve an error at the


    Set notesdb = objNotesWS.COMPOSEDOCUMENT(, , "memo")


    statement


    Here is how I have it set out in a module


    Public Function SendMail(strAddressee As String, strSubject As String, strBody As String)


    Dim objNotesWS As Object
    Dim notesdb As Object


    Set objNotesWS = CreateObject("Notes.NotesUIWorkspace")


    Set notesdb = objNotesWS.COMPOSEDOCUMENT(, , "memo")


    notesdb.FIELDSETTEXT "EnterSendTo", strAddressee
    notesdb.FIELDSETTEXT "Subject", strSubject
    notesdb.FIELDSETTEXT "Body", strBody


    notesdb.Send


    notesdb.Close
    Set notesdb = Nothing
    Set objNotesWS = Nothing


    End Function


    Public Sub testmail()
    Dim imail
    Set imail = SendMail("[email protected]", "Test", "Text goes here")
    End Sub

    Here is the error and the description


    If anyone can help it would be GREAT!!


    Automation error (Error 440)


    When you access Automation objects, specific types of errors can occur. This error has the following cause and solution:


    An error occurred while executing a method or getting or setting a property of an object variable. The error was reported by the application that created the object.
    Check the properties of the Err object to determine the source and nature of the error. Also try using the On Error Resume Next statement immediately before the accessing statement, and then check for errors immediately following the accessing statement.



    DW

    My apologies......


    I just put a On Error Resume Next statement in and it works...


    One last question though..


    How do you get the activeworkbook as an attachment???


    Thanks once again for the help


    DW

    Sorry - I should have read the full text of your post before replying!


    Unfortunately, I don't have Notes installed on my PC so I can't give you the exact answer.


    However, the following url's should be helpful for you:


    http://groups.google.com/group…tes%2522%2Bemail%2Battach


    http://groups.google.com/group…tes%2522%2Bemail%2Battach


    They seem to have quite a bit of useful info there. You'd think it would be quite a simple thing to do!


    The examples they show all require the filename to be passed as a string to the functions so you may need to modify them slightly if you want to attach the active workbook.


    I don't know whether you could simply specify the ActiveWorkbook name or would need to use ThisWorkbook.FullName as the argument.


    Hope this is enough info to figure it out.


    Jeff

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login