sending workbooks with Lotus Notes

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

<font face=Courier New><SPAN style="color:darkblue">Private</SPAN> <SPAN style="color:darkblue">Declare</SPAN> <SPAN style="color:darkblue">Function</SPAN> FindWindow <SPAN style="color:darkblue">Lib</SPAN> "user32" Alias "FindWindowA" ( _
    <SPAN style="color:darkblue">ByVal</SPAN> lpClassName <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>, _
    <SPAN style="color:darkblue">ByVal</SPAN> lpWindowName <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>) <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Long</SPAN>

<SPAN style="color:darkblue">Private</SPAN> <SPAN style="color:darkblue">Sub</SPAN> Test_Notes()
<SPAN style="color:darkblue">Dim</SPAN> h <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Long</SPAN>
<SPAN style="color:darkblue">Let</SPAN> h = FindWindow("Notes", vbNullString)
<SPAN style="color:darkblue">If</SPAN> <SPAN style="color:darkblue">CBool</SPAN>(h) <SPAN style="color:darkblue">Then</SPAN>
    MsgBox "L.N. is running."
    Else: MsgBox "L.N. is not running."
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

This approach removes the need for error generation/trapping. :)
 
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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
 
Upvote 0
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
 
Upvote 0
You're welcome. :)

Did you try to get handle (literally) on the situation as I mentioned? 'Tis a very efficient approach to the quandary.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top