Text file & Script (Update Forwarding Emails)

HCL Domino Domino Forum
1

We have a text file with “old email address,new email address” on each line. See example below:

tom.jones@test.com,tom.jones@newtest.com

mike.smith@test.com,mike.smith@newtest.com

etc…

I need to use this information to find the person document (1st email address is current email) then change forwarding email field with 2nd value. Another thought… paste contents of text file into a Notes document then click button that fetches values from this field to update forwarding email addresses. Any thoughts on how to accomplish this? …thanks in advance.

2

Subject: Text file & Script (Update Forwarding Emails)

The easiest way to do this is create a LotusScript agent.

Set the view to the $VIMPeople in the NAB (Server Address Book). Process each person document in a while loop.

Set db = New NotesDatabase( “Server”, “names.nsf” )

Set view = db.GetView( “$VIMPeople” )

Set doc = view.getFirstDocument

Set count = 0

'use the Open Statement to read in your file. Put the values into two variant arrays, one with the original value, the new value in the other.

While not(doc is nothing)

forall v in 1stVariantArray

'check each value in the array for a match.

if doc.EmailAddress(0) = v Then

'replace it with the new e-mail value.

doc.EMailAddress = 2ndVariantArray( count )

Call doc.Save( True, False )

end if

count = count + 1

end forall

count = 0

Set doc = view.GetNextdocument( doc )

Refresh the Server cache at the end to take effect.

Hope this helps.

Tim Williams

3

Subject: RE: Text file & Script (Update Forwarding Emails)

I have been working on this over the weekend… with limited success. What am I missing here? FYI… my original posting was a bit incorrect. It is not old email address to new email address (as in same field… internetaddress). The old email would be used for the lookup <per previous posting - is in the internetaddress field> and the 2nd or new email address would be added to the mailaddress or forwarding address field.

Here is my script so far:

note: the script debugger is complaining about a couple things. #1 is my “Set counter = 0”… complains about using set. #2 is the “doc.MailAddress = newmail( count )”… complains about an illegal parenthesized reference. If I remove the set and the ( count ) , the script will run to a point. I’m getting a “input past end of file” dialog right after entering in file location. My print statement says it stops at the “Input #fileNum%, arrayOfRecs(countrec%).oldmail, _arrayOfRecs(countrec%).newmail” line. Am I close? Honestly, I am confused about the array (is is correct?). I tried to test this same method with a simple search for oldmail value… used the print statement to let me know if no such value or value found. This worked… reason for my feeling somewhat confident re: arrays.

Declaration:

Type MailType

oldmail As Variant

newmail As Variant

End Type

Sub Click(Source As Button)

'PURPOSE: set the names of the admin server and directory



Dim server As String

Dim database As String

server="testserver/test"

database="testdir.nsf"



'PURPOSE: set the correct admin server and directory	



Set db = New NotesDatabase( server, database )

Set view = db.GetView( "$VIMPeople" )



'PURPOSE: start at first document in the $VIMPeople view



Dim count As Integer	

Set doc = view.getFirstDocument

Set count = 0



'PURPOSE: open comma-delimited text file and create variant arrays



Dim arrayOfRecs() As MailType 

	

Dim txt As String

Dim fileNum As Integer

Dim counter As Integer

Dim countRec As Integer

Dim found As Boolean



fileNum% = Freefile        ' Get a file number to open a file.

counter% = 0

file = Inputbox$("Where is your import file located?")	

Open file For Input As fileNum%



Do While Not Eof(fileNum%)



	Line Input #fileNum%, txt$  ' Read each line of the file.



	counter% = counter% + 1     ' Increment the line count.



Loop



Seek fileNum%, 1                ' Pointer to beginning of file

’ Since file has counter% number of lines, define arrayOfRecs ’ to have that number of elements.

Redim arrayOfRecs(1 To counter%)

’ Read the file contents into arrayOfRecs.

For countRec% = 1 To counter%    



	Input #fileNum%, arrayOfRecs(countrec%).oldmail, _

	arrayOfRecs(countrec%).newmail( count )

	

Next

Close fileNum%



'PURPOSE: process person documents using oldmail array to locate 

'person document and newmail array to populate forwarding address



While Not(doc Is Nothing)

	Forall v In oldmail

'PURPOSE: check each value in the array for a match.

		If doc.InternetAddress(0) = v Then

'PURPOSE: access each person document and update the email forwarding address

			doc.MailAddress = newmail

			Call doc.Save( True, False )

		End If

		count = count + 1

	End Forall

	count = 0

	Set doc = view.GetNextdocument( doc )

Wend

End Sub

4

Subject: RE: Text file & Script (Update Forwarding Emails)

When assigning a value to a scalar you do not use “set”. Simply say: count = 0.

Is the mail element of your MailType an array?

Are you mixing count and counter?

Why do you append the variables with %, $ etc? No need, since you declared them explicitly.

Why so sloppy with names: countrec versus countRec?

5

Subject: RE: Text file & Script (Update Forwarding Emails)

Rob… thanks for your responses. I’m not a full-time developer (really an admin in disguise… lol) so the naming issues should come as no surprise. The countrec piece came from the designer help! …see user-defined data types entry. Here is the snippet:

For countRec% = 1 to counter%

Input #fileNum%, arrayOfRecs(countrec%).empID, _

I will drop the set count = 0 and change to count = 0. I’m confused about the question “Is the mail element of your MailType an array?” …I’m not sure. Have I even created an array? I though the array was created with the following in the declarations:

Type MailType

oldmail As Variant

newmail As Variant

End Type

note: I changed the MailType back to RecType based on some example scripts. I thought this could be whatever as long as it was consistent throughout the script.

My goal was to create 2 variant arrays that contain the current email address (will be used as the unique key to lookup person document in directory) and the forwarding address. This was a recommendation from Tim since the comma-delimited text file being received is using the “user@test.com,user@newtest.com” (no quotes) formatting. 1 person per line.

I’ll cleanup the %, $, etc. since these were declared earlier.

6

Subject: RE: Text file & Script (Update Forwarding Emails)

Thanks Tim… let me “absorb” this great example. I’m not actually creating the text file… is being handed to me by someone else. On first look… where are you creating the arrays or using (open) file?

7

Subject: RE: Text file & Script (Update Forwarding Emails)

To create an array, I’ve put together a few tools to help.

First I dim a variant, then I add each entry to it using a delimeter not found in the data itself, like this:

“^myoldemail:mynewemail^hisoldemail:hisnewemail^etc:etc”

After I’m done I use a script library function to explode it:

Function ExplodeR5(mystr As String, comparechar As String, trimYesNo As String) As Variant

 '** ParseString

 '** This function works the same as @Explode -- WORKS FOR ANY STRING

'** 2004-11-01 -- Added feature to trim out blank values from list if requested as "Yes"



Dim Values List As String, returnval() As String

Dim tempstr As String, tempstrtemp As String

Dim x As Integer, y As Integer, pos As Integer



tempstr = mystr

’ Print “”

x = 0

While Not(tempstr Like "")

	pos = Instr(1, tempstr, comparechar)

	

	If (pos = 0) Then

		Values(x) = tempstr

		tempstr = ""

	Else

		REM 2004-11-01 -- Added Feature to trim out blank values if requested.

		If Ucase(trimYesNo) = "YES" Then

			tempstrtemp = Trim(Strleft(tempstr, comparechar))

			

			If Trim(tempstrtemp) = "" Then

				x = x - 1

			Elseif Trim(Rightbp(tempstrtemp, 1)) = comparechar Then

				Values(x) = Trim(Strleft(tempstr, comparechar))

				tempstr = ""

			Else

				Values(x) = Trim(Strleft(tempstr, comparechar))

			End If

			

			If tempstr <> "" Then				

				tempstr = Strright(tempstr, comparechar)

			End If

		Else

			Values(x) = Strleft(tempstr, comparechar)

			tempstr = Strright(tempstr, comparechar)

		End If

	End If

	x = x + 1

Wend



If (x >= 1) Then

	Redim returnval(x-1)

	For y = 0 To x-1

		returnval(y) = Values(y)

	Next

	ExplodeR5 = returnval

Else

	Redim returnval(0)

	'returnval(0) = mystr

	returnval(0) = ""

	ExplodeR5 = returnval

End If

End Function

Now you can explode the variant by doing this:

varaint = ExplodeR5(cstr(variant), “^”, “Yes”)

Now the variant is exploded into an array and you can put the values through a for loop check. If the two e-mails are put together with a colon, you can do a simple if instr(1, persondoc.Email(0), v, “5”) > 0 Then, if matched you can then process the string strright( v, “:” ) to get the new e-mail.

Well, the works half done now.

Good Luck.

Tim Williams

8

Subject: RE: Text file & Script (Update Forwarding Emails)

Thanks… thanks feels kinda “lightweight” after your excellent posting. Wow… might as well ask my next question (lol). What if we forced the person to paste the contents of the text file into a field? …and have the agent reference the field instead of text file? Sounds nicer to me… user creates new migration doc and pastes the contents then clicks the “Update…” button that references the field.