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
Amitava_MukherjeeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

madheeswarCommented:
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.
0
Amitava_MukherjeeAuthor Commented:
I am modifying my default template by writing code at the send button in lotusscript at the client end
0
madheeswarCommented:
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..
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Amitava_MukherjeeAuthor Commented:
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.
0
madheeswarCommented:
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.
0
mbonaciCommented:
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

0
Sjef BosmanGroupware ConsultantCommented:
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.
0
Amitava_MukherjeeAuthor Commented:
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.
0
mbonaciCommented:
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

0
Amitava_MukherjeeAuthor Commented:
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.
0
Sjef BosmanGroupware ConsultantCommented:
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).
0
mbonaciCommented:
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).
0
mbonaciCommented:
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
0
Amitava_MukherjeeAuthor Commented:
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?
0
mbonaciCommented:
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

0
larsberntropCommented:
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.
0
mbonaciCommented:
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

0
mbonaciCommented:
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).

0
mbonaciCommented:
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

0
Sjef BosmanGroupware ConsultantCommented:
IMHO you don't need a lookup in an address book, for the final mail sent contains full addresses. Assuming that all mails are sent through the Domino server, the journal will capture those mails. Plus, if iNotes is used, those mails will be captured in the journal as well.

I'd say, set up journalling, wait a month, and then export the TO-addresses.

Don't forget that you'd have to modify the code again when you upgrade your Domino environment. kSpam is for incoming mail only, I know, but why do you want to collect the addresses? Domino does not have a black/whitelist function for outgoing mail. All you can reasonably do is set "Deny messages to recipients in the following Internet domains or hostnames". The export only shows addresses that are used. Who's going to decide if the addresses are 'black' or 'white'?? What's your bigger plan?
0
Amitava_MukherjeeAuthor Commented:
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
0
mbonaciCommented:
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)
0
mbonaciCommented:
I also think you should consider Sjef's suggestions.
He has a good point with the fact that all outbound user names will be resolved to mail addresses automatically, by the router.

But you obviously have your agenda.
You know, it wouldn't harm to answer the questions, I'm also interested to find out the big picture.
Describe your server environment (which relational db, how many servers, users) and most importantly, explain what you want to accomplish with those e-mail addresses and how.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Amitava_MukherjeeAuthor Commented:
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
0
Amitava_MukherjeeAuthor Commented:
Yes the problem is solved from local address book. Now I am trying to test from my server address book.
0
Amitava_MukherjeeAuthor Commented:
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
0
larsberntropCommented:
You can keep trying to do the work of the router.  Or you can try the journaling solution.
0
larsberntropCommented:
In other words: if all you have is a hammer, every problem is a nail.

It's time to use a different tool!
0
Amitava_MukherjeeAuthor Commented:
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

0
Amitava_MukherjeeAuthor Commented:
thanx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.