Link to home
Start Free TrialLog in
Avatar of Amitava_Mukherjee
Amitava_MukherjeeFlag for India

asked on

Get email address from full name by lotusscript

I am collecting data from "TO" field of lotus notes.

User may use
  1. "tim harris",
  2. "tim@mnd.com",
  3. "tim/IT/KOL/MND"
these 3 types of addresses.

Now my problem is, I can easily collect 2nd and 3rd option that if address is internal address or not as it is ending with @mnd.com or /mnd. But for the 1st option I can not resolve whether that is an internal address or not as it is collecting from either local address book or public address book.

I am using Lotusscript for my customization. Can you please help me to collect the email address from the name that may be in local or public address book? For your information we are using lotus 7.0.2.

Thanx and regards,
Soumen
Avatar of madheeswar
madheeswar
Flag of Singapore image

Some queries:
- From where do you want to run your Lotus Script?
- What is the purpose of it?
- Can you modify your mail template?

Hint:
- If you are running LS locally by the user in there Workstation, then you can get handle to the local address book. If not, this will be difficult to handle.
Avatar of Amitava_Mukherjee

ASKER

I am modifying my default template by writing code at the send button in lotusscript at the client end
You have not answered my other question.
What is the main purpose of this code? What should happen after user clicks on Send button? Do you want to display from where the email address is picked or validate it is an internal email or external email?
Pls provide more details..
I am sorry, I may not be clear in my last comment.

I want to store all outgoing email address in the sql server database. I want to store it when user trying to send any mail from his lotus client. So I am modifying the send button of the lotus notes. I can pick the TO/CC/BCC field values at time of sending. I can store the values at sending time, as I written code at the send button. I have written the code in the Send button click event. I do not want to display the mail addresses but to store those in the sql server.

Am I clear? If you need any other clarification please let me know, I must clarify you my requirement.
I will give it a try on the logic. I would prefer to do the updates in QuerySAve event.
Send an email with the same scenario you explained. Now, go to the sent folder and check the mail you just send. Open the doc properties and look for InternerAddress field. One field will be holding the email address (if it is internet based address). Not sure on the field name.
And to check if it is valid internal email address (to your first query), always you can refer to the names.nsf and validate if the email is available in the $Users view (use view.GetdocumentbykeY method)

I am not sure about this option, whether you can track in server's mail.box. Since all the emails passing domino server will pas though this database.

Let's wait for other experts comments. They would be having different ideas.
Here's the function that gets current user's document from NAB.

So you need to get that document and then get the value from the field InternetAddress (as shown in the example code bellow the function).
Function GetUserDocumentFromNAB() As NotesDocument
	'Mb¤, 19.08.2008
	'The function returns the user document from server's NAB
	Dim s As New NotesSession        
	Dim nab As NotesDatabase
	Dim v As NotesView
	
	Set nab = s.GetDatabase( s.CurrentDatabase.Server, "NAMES.NSF", False)
	If Nab Is Nothing Then Exit Function
	
	Set v = nab.GetView("($Users)")
	If v Is Nothing Then Exit Function
	
	Set GetUserDocumentFromNAB = v.GetDocumentByKey( s.CommonUserName, True )
	
End Function


'How to use the function:
	Dim doc As NotesDocument
	Dim email As String
	
	Set doc = GetUserDocumentFromNAB()
	email = doc.InternetAddress(0)

Open in new window

Avatar of Sjef Bosman
You don't have to modify code at all, you're jumping to the first solution you can think of. Plus, if you build SQL-code into the Send button, it would mean that people with a laptop might have a lot of difficulty to send their mails (slow, errors).

If you set up mail journalling, the Domino server will store ALL mails sent in a Mail journal database. See the Admin Help database about how to set it up (in the Configuration document for your server). One option is to send the mail to an (external) mail address, e.g. a mail collector on the SQL-system.
We are collecting that data in SQL to set a policy to generate white list and black list. We may put a white listed group in our server and filter those mails those are to be sended if all addresses are available in the white list. So, In future I have to check if the mail addresses in the TO/CC/BCC fields are available in white list. Now the white list will carry only the internet addresses. So I have to match the internet addresses from the TO/CC/BCC field with the white list in the server and if all the TO/CC/BCC fields are available in white-list then the mail will send.

My problem is, as I said earlier, if user add any address as a full name from his address book, then I have to deduct the full mail address of that user to compare with the white list. I have to store in sql server also. Its just for clarity of my requirement for the experts. I am testing the code given by "mbonaci" and let you know if it works fine for me. till then thanx for your visions.
The code I posted will get the sender's mail address.
This one will get email of any user's that is in names.nsf:
Function GetUserDocumentFromNAB( strUser As String ) As NotesDocument
        'Mb¤, 19.08.2008
        'The function returns the user document from server's NAB
        Dim s As New NotesSession        
        Dim nab As NotesDatabase
        Dim v As NotesView
        
        Set nab = s.GetDatabase( s.CurrentDatabase.Server, "NAMES.NSF", False)
        If Nab Is Nothing Then Exit Function
        
        Set v = nab.GetView("($Users)")
        If v Is Nothing Then Exit Function
        
        Set GetUserDocumentFromNAB = v.GetDocumentByKey( strUser, True )
        
End Function

Open in new window

email = doc.InternetAddress(0)

this line throws error "Object variable not set"

What I have done
--------------------
1. Add an action button in client named "Test"
2. Copy + Paste the following lines code in the Click event

        Dim doc As NotesDocument
        Dim email As String
         
        Set doc = GetUserDocumentFromNAB()
        email = doc.InternetAddress(0)
3. Also Copy+paste the "GetUserDocumentFromNAB" subroutine in the click event which appears as a function of that button.
4. Create a new mail and the new "Test" button appears.
5. "Object variable not set" error dialog appears.

I am definitely wrong in some way, but can't understand what is that. Kindly suggest.
IMHO black and white lists are not the best solution, on their own. Here's a very good anti-spam solution for Domino that does a great job:
http://www.openntf.org/Projects/pmt.nsf/ProjectHome?ReadForm&Query=kSpam

At first you have to "teach" it what's spam and what's not. Later on, it builds a database of words that are considered spam and filters spam mails into a separate database, which must be inspected regularly for fine tuning (false positives and false negatives).
That means that the document was not found (could be that db was not found or there's no such user in NAB).
The code, in order to work, has to be in database that resides on server.

You'll have to employ some kind of error checking in your code (whether the function returned nothing).
Like this:

Set doc = GetUserDocumentFromNAB()

If doc is Nothing Then             'so you don't get the error when user isn't found in Names.nsf
    'you can log just the name from sendTo field or you can Exit Sub
Else
    email = doc.InternetAddress(0)
End If
Yes, that is true "mbonaci". error trapping is required, but one thing i have to ask you, that i am using the name in the TO field which is available in my local address book. is it the problem?

The whole thing I am trying is to change the local ntf, replacing design in the local pc and trying to debugging that. i am not doing this in domino server. is it a problem?

I have to check local address book as well as server address book. Is this a problem?
Yes, it's a problem, but you can fix only if you have access to the server, then you can provide server name to the function instead of taking server name from the current database, like this:

Remember, server must be available to the client when he runs the code, otherwise, the function will exit in line "If Nab is nothing..." and return Nothing instead of document.

If you tell us more about your setup we can help you do this correctly (to avoid hard-coding the server name you can parametrize it in a profile document).
Function GetUserDocumentFromNAB( strServer As String, strUser As String ) As NotesDocument
        'Mb¤, 19.08.2008
        'The function returns the user document from server's NAB
        Dim s As New NotesSession        
        Dim nab As NotesDatabase
        Dim v As NotesView
        
        Set nab = s.GetDatabase( strServer, "NAMES.NSF", False)
        If Nab Is Nothing Then Exit Function
        
        Set v = nab.GetView("($Users)")
        If v Is Nothing Then Exit Function
        
        Set GetUserDocumentFromNAB = v.GetDocumentByKey( strUser, True )
        
End Function

Open in new window

this problem should not be solved with custom code like this.  Like Sjef said, there is an excellent mail journaling feature, and you can easily extract needed data from that.
Aha, you mean you want to use local address book to search the mail address, not the server's.
I have the code for that ready, also:
Function GetLocalNAB() As NotesDatabase
	Dim session As New notessession
	Dim sAddressBook As String
	Dim nCommaLocation As Integer
	
	On Error Resume Next
	sAddressBook = session.GetEnvironmentString( "NAMES", True )
	nCommaLocation = Instr( sAddressBook, "," )
	If nCommaLocation > 0 Then sAddressBook = Left( sAddressBook, nCommaLocation - 1 )
	
	Set GetLocalNAB = New NotesDatabase( "", sAddressBook )
End Function

Open in new window

Yes, and then setup smtp on the relational db side, create a stored procedure (or trigger) that resolves incoming e-mails, of course, that could be another way to do this, but isn't it much easier to simply add new entry to a table in relational db when the event occurs in Lotus db?

I don't see a reason against this solution, especially given the new circumstances (local address book lookup, which is not possible using journaling).

Usage example:

      Dim doc As NotesDocument
      
      Set doc = GetLocalUserDoc( "Name Surname" )
      Msgbox doc.MailAddress(0)   'in local nab field is called differently
Function getLocalUserDoc( strUser As String ) As NotesDocument
	Dim pnab As NotesDatabase
	Dim v As NotesView
	Dim doc As NotesDocument
	
	Set pnab = GetLocalNAB()
	
	If pnab Is Nothing Then
		Print "Cannot get your local address book."
		Exit Function
	End If
	
	Set v = pnab.GetView("($Users)")
	If v Is Nothing Then Exit Function
	
	Set getLocalUserDoc = v.GetDocumentByKey( strUser, True )
	
End Function

Open in new window

SOLUTION
Avatar of Sjef Bosman
Sjef Bosman
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Give me sometime, I am trying that code, surprisingly

     Dim doc As NotesDocument
     
      Set doc = GetLocalUserDoc( "Name Surname" )
      Msgbox doc.MailAddress(0)   'in local nab field is called differently

in the second line , Set doc = GetLocalUserDoc( "Name Surname" )
doc is always nothing where as in GetLocalUserDoc it has value. May be a little problem, give me couple of hours
You have to declare NotesDatabase (localNAB) in form's Globals section.

In other words, this line of code:

        Set doc = GetLocalUserDoc( "Name Surname" )

needs to see pnab variable (which is currently only the local variable for the function getLocalUserDoc).


So CUT the following line of code from that function:

        Dim pnab As NotesDatabase

and put in the form's Globals - Declarations (so that all form elements can see it)
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
We have 3 Domino servers in 3 different geographic locations. We have around 1200 lotus users in each server. We are running a document management system which is integrated with Lotus as mails are archived right from the lotus. For which entire portion is written in lotusscript. We are using Open NTF and customized that for that purpose.

Now we are trying to impose some white listing and black listing in one of this server location, for which the entire discussion is going on. Yes, probably "sjef_bosman" and "mbonaci" both are right, but as I am the developer of the document archival system which is also integrated with lotus, from my point of view, solution given by "mbonaci" is easier to implement, but I will also test "sjef_bosman" solution.

This is just to make you aware, what I am trying to do, nothing else. Just for information
Yes the problem is solved from local address book. Now I am trying to test from my server address book.
In server I am facing a different problem. In our server, there are some groups named "ALL IT USERS", "ALL STRUCTURAL USERS" etc. etc. The GetUserDocumentFromNAB is working fine on server but doc.InternetAddress(0) portion generating error as under that group many email addresses exists.

Set doc = GetUserDocumentFromNAB("KOLS01", "All KOL IT Users")
email = doc.InternetAddress(0)

Any suggestion? Or I is it require to iterate in columns to make an array list for the available email addresses? Cause for those groups doc.InternetAddress(0) does not working
You can keep trying to do the work of the router.  Or you can try the journaling solution.
In other words: if all you have is a hammer, every problem is a nail.

It's time to use a different tool!
problem is solved. however one of my friend has shown me a interesting way. if i press F9 key, the group is dissociated with the mail addresses. i am going to open a new question how to do that programmetrically, anyways thanx to all of you. for your reference i am using the following code snippets to solve my problem. major portion has already given by th EE.

a blend of follwing code and the code given by "mbonaci" solved my problem.

thanx again to all of you
Function GetUserDocumentFromNAB( strServer As String, strUser As String ) As NotesDocument 	
        'The function returns the user document from server's NAB 
	Dim s As New NotesSession         
	Dim nab As NotesDatabase 
	Dim v As NotesView 
	
	Set nab = s.GetDatabase( strServer, "NAMES.NSF", False) 
	If Nab Is Nothing Then Exit Function 
	
	Set v = nab.GetView("($Users)") 
	If v Is Nothing Then Exit Function 
	
	Set GetUserDocumentFromNAB = v.GetDocumentByKey( strUser, True) 	
End Function

Open in new window

thanx