sending workbooks with Lotus Notes - Page 3
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Page 3 of 3 FirstFirst 123
Results 21 to 26 of 26

Thread: sending workbooks with Lotus Notes

  1. #21
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: sending workbooks with Lotus Notes

     
    Hello,
    Quote Originally Posted by AJW
    Do you know of a way to check if Notes is running first and then proceed with the emial? {snip}
    Another possibility:

    Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
    ByVal lpClassName As String, _
    ByVal lpWindowName As String) As Long

    Private Sub Test_Notes()
    Dim h As Long
    Let h = FindWindow("Notes", vbNullString)
    If CBool(h) Then
    MsgBox "L.N. is running."
    Else: MsgBox "L.N. is not running."
    End If
    End Sub


    This approach removes the need for error generation/trapping.

  2. #22
    New Member
    Join Date
    Feb 2003
    Location
    Australia
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: sending workbooks with Lotus Notes

    Ivan / Nate O,

    Thanks guys for the ideas, I liked Ivans approach with the forced error the only drawback is that using "Set objNotes = Getobject ("Notes.NotesSession")" at the begining forces Notes to launch.

    This was my main problem so I used Ivans idea of forcing an error and using that error to determin the next step. My end result which works a treat is as below, I used AppActivate to check if Notes is Active on the Desktop, if it is we then jump down to the else statement and continue. If not then we get the message and exit the routine. Very similar to Nates approach.


    Sub ConformationMail3()
    On Error GoTo error:
    Dim s As Object
    Dim db As Object
    Dim doc As Object
    Dim KO As Object
    On Error Resume Next
    AppActivate "Lotus Notes"
    If Not Err.Number = 0 Then
    Err.Clear
    MsgBox "Notes is Not Running"
    Else
    UserName = s.UserName
    MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
    Set db = s.GETDATABASE("", MailDbName)
    Call db.OPENMAIL
    mycell = Sheet1.Cells(1, 4)
    Set doc = db.CREATEDOCUMENT
    Msg = "WSM-Marketing Package has been installed. " & Date & " " & Time & Chr(10)
    Call doc.REPLACEITEMVALUE("SendTo", "anton.water@whereever.com")
    Call doc.REPLACEITEMVALUE("Subject", "WSM-Marketing User")
    Call doc.REPLACEITEMVALUE("Body", Msg)
    Call doc.Send(False)
    Set s = Nothing
    End If
    Exit Sub
    error:
    End Sub

    Regards

    AJW

  3. #23
    New Member
    Join Date
    Feb 2003
    Location
    Australia
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: sending workbooks with Lotus Notes

    All,

    This worked good also:

    Sub ClientInfoEmailNew()
    Dim strSendTo, strSubject, strText, strcc, strbcc As String
    Dim strFile As String
    strSendTo = ""
    strSubject = ""
    strText = ""
    strFile = ""
    NotesMailNewDraft strSendTo, strSubject, strText, strcc, strbcc, strFile
    Exit Sub
    20 Msg = "A draft E-Mail was not created! Please check your network connection and ensure you are logged into Lotus Notes."
    MsgBox Msg, vbInformation, "Notesmail Draft..."
    End Sub

    Public Function NotesMailNewDraft(strSendTo As Variant, strSubject As Variant, strText As Variant, strcc As Variant, strbcc As Variant, strFilename As String)
    Dim objNotes As Object, objNotesDB As Object, objNotesMailDoc As Object
    Dim SendItem, NCopyItem, BlindCopyToItem, i As Integer, rtitem
    Dim Msg As String
    On Error Resume Next
    AppActivate "Lotus Notes"
    If Not Err.Number = 0 Then
    Err.Clear
    GoTo ExitF
    Else
    On Error GoTo ExitF
    Set objNotes = GetObject("", "Notes.Notessession")
    Set objNotesDB = objNotes.GETDATABASE("", "")
    Call objNotesDB.OPENMAIL
    Set objNotesMailDoc = objNotesDB.CREATEDOCUMENT
    objNotesMailDoc.Form = "Memo"
    Call objNotesMailDoc.Save(True, False)
    Set SendItem = objNotesMailDoc.APPENDITEMVALUE("SendTo", "")
    Set NCopyItem = objNotesMailDoc.APPENDITEMVALUE("CopyTo", "")
    Set BlindCopyToItem = objNotesMailDoc.APPENDITEMVALUE("BlindCopyTo", "")
    objNotesMailDoc.SendTo = Application.Worksheets("Client Information").Range("EmailAddress").value
    objNotesMailDoc.Subject = Application.Worksheets("Client Information").Range("ProjectName").value & " " & Application.Worksheets("Client Information").Range("ProjectNumber").value
    Set rtitem = objNotesMailDoc.CREATERICHTEXTITEM("Body")
    objNotesMailDoc.Body = Application.Worksheets("Client Information").Range("ContactName").value & vbCr & " " & vbCr & " " & vbCr & " " & vbCr & " " & vbCr & " " & vbCr & " " & vbCr & " " & vbCr & " " & vbCr & " " & vbCr & " " & vbCr & " " & vbCr & "Regards" & vbCr & " " & vbCr & Application.Worksheets("Client Information").Range("SalesEng").value & vbCr & "WWL"
    rtitem.ADDNEWLINE (1)
    Call objNotesMailDoc.Save(True, False)
    objNotesMailDoc.RemoveItem ("DeliveredDate")
    Call objNotesMailDoc.Save(True, False)
    Msg = "A draft E-Mail was successfully created and can be found in your Notes Drafts folder!"
    MsgBox Msg, vbInformation, "Notesmail Draft..."
    Call objNotes.Close
    Set objNotes = Nothing
    Exit Function
    End If
    ExitF:
    Msg = "A draft E-Mail was not created! Please check your network connection and ensure you are logged into Lotus Notes."
    MsgBox Msg, vbInformation, "Notesmail Draft..."
    End Function

  4. #24
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: sending workbooks with Lotus Notes

    You're welcome.

    Did you try to get handle (literally) on the situation as I mentioned? 'Tis a very efficient approach to the quandary.

  5. #25
    New Member
    Join Date
    Feb 2003
    Location
    Australia
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: sending workbooks with Lotus Notes

    NateO / Ivan,

    I've got one more little hicup.

    I also need to launch to a Notes Database, (not a specific document within the database but the database view) from Excel but am having trouble getting it to work. Attached is my code with my problem areas highlighted, if you've got any ideas/suggestions I would be immensly grateful.

    Thanks in advance AJW

    Sub LotusNotes_CVXLib()
    Application.ScreenUpdating = False
    On Error GoTo error:
    Dim s As Object
    Dim db As Object
    Dim doc As Object
    On Error Resume Next
    AppActivate "Lotus Notes"
    If Not Err.Number = 0 Then
    Err.Clear
    MsgBox "Notes is Not Running", vbInformation, "WSM-Market"
    Else
    Set s = CreateObject("Notes.Notessession")
    ' Function GETDATABASE(SERVER As String, FILE As String, [CREATEONFAIL])
    Set db = s.GETDATABASE("WARRIOR1/AU/WAR/Were/WARRAU/Market/", "CVXLby.nsf")
    ' Call db.OPENMAIL '?????
    ' Call db.CVXLby '?????
    ' Set doc = db.CREATEDOCUMENT
    Set s = Nothing
    Set db = Nothing
    Set doc = Nothing
    End If
    Exit Sub
    error:
    End Sub

  6. #26
    New Member
    Join Date
    Feb 2003
    Location
    Australia
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: sending workbooks with Lotus Notes

      
    NateO / Ivan,

    Save yourselves the headache, the penny dropped.

    Figured it out, fairly simple once I stopped heading down one track and took another path. Must say that seems to be the way I've learnt most of my Excel VBA skills............ TRIAL & ERROR !

    So here's the cure.......

    I'm running Notes 6.5, by right mouse clicking on the toolbar area you get a list of available toobars (like with Excel).

    1. Select the one that says address.
    2. Open your database.
    3. Go to the address toolbar and click the dropdown, the address for your database will show in the toolbar.
    4. Copy the address.
    5. Paste the address in the following code, close your database (not Notes) and run the code.
    6. Hey presto up pops the database.
    7. Also works for Documents within a database.
    8. Enjoy !

    Sub LotusNotes_CVXDocLib()
    Application.ScreenUpdating = False
    On Error Resume Next
    AppActivate "Lotus Notes"
    If Not Err.Number = 0 Then
    Err.Clear
    MsgBox "Notes is Not Running", vbInformation, "WSM-Marketing"
    Else
    Application.ActiveWorkbook.FollowHyperlink Address:="Notes://WARRIORSYD1/4A2565BF00171D6E/8525608C005E322585255D7C00545AF7", NewWindow:=True
    End If
    Exit Sub
    End Sub


    Hope this helps others.

    Regards

    AJW

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com