Posts Tagged With: @DBLookup

As we work to get our Notes applications functioning smoothly with our Outlook mail, I’m finding ways to keep the close binding between Notes applications and the user’s mail. It’s so much easier to click a button to generate an email associated to a particular Notes document than to copy-paste a document link.

We’ve got a Notes form for the Quarterly Project Report. Each quarter, various key members on the project are supposed to have a call to review the project. The form itself is ponderous, having something like 700 fields, but (using hide-whens) can be distilled down to a manageable number for the meeting’s agenda. The Notes names of the expected participants are computed from other documents within the database, though the fields are editable. In switching from using Notes mail to generate the meeting notice to Outlook, I ended up switching to using iCal.

It turns out that iCal is a far simpler way to initiate the meeting notice in the UI. All I need is something like this in an ICS file to have it open in my Outlook client as a meeting notice for me to send (as an update, but more on that in another post)

BEGIN:VCALENDAR
BEGIN:VEVENT
DTSTART:20170622T211500
DTEND:20170622T221500
ATTENDEE;ROLE=REQ-PARTICIPANT;PARTSTAT=NEEDS-ACTION;CN="David Navarre/Company";RSVP=TRUE:mailto:David_Navarre@company.com
ATTENDEE;ROLE=REQ-PARTICIPANT;PARTSTAT=NEEDS-ACTION;CN="Another Person/Company";RSVP=TRUE:mailto:Another_Person@company.com
ATTENDEE;ROLE=OPT-PARTICIPANT;PARTSTAT=NEEDS-ACTION;RSVP=TRUE:mailto:external_person@SecondCompany.com
DESCRIPTION: 
SUMMARY:2017 Q2 QPR: Agribusiness Competitiveness Enhancement via file
UID:AC1804D765C782CD8525814500073F37
END:VEVENT
END:VCALENDAR

One of the hurdles is that we need to have email addresses as well as names for all of the attendees. If you leave the mailto blank or put the Notes name there, it ignores it. Since our address book is still available in Notes and contains everyone’s email address, I thought I’d just google how to do it. Sadly, it wasn’t out there, so I took a few minutes and modified some script in the help documentation (Examples: AddressBooks property) to create a function to do the lookup.

%REM
	Function getEmailAddress
	Description: This Function returns a string that is the email address from the address books

	IMPORTANT: all address books are stored in the NotesDatabase list, addressBookList, which must be called BEFORE calling the getEmailAddress function

%END REM
Function getEmailAddress ( recipientName As String ) As String
	Dim view As NotesView
	Dim doc As NotesDocument
	Dim internetAddress As Variant
	Dim found As Boolean
	Dim reason As String	

	On Error Goto errorhandler

	' if already an internet address, just return that value '
	If ( InStr ( recipientName, "@" ) ) Then
		getEmailAddress = recipientName
		Exit function
	End If

	getEmailAddress = ""
	found = False
	
	ForAll addressBook In addressBookList
		' all address books are stored in the NotesDatabase list, addressBookList, which must be called BEFORE calling the getEmailAddress function '
		' check every Domino Directory, until found '
		If ( addressBook.IsPublicAddressBook ) And ( Not found ) Then
			' look up name in the VIMPeople view of address book '
			Set view = addressBook.GetView( "($VIMPeople)" )
			If not ( view Is Nothing ) Then
				Set doc = view.GetDocumentByKey( recipientName )
				' if person is found, get their internet addrress and stop '
				If Not ( doc Is Nothing ) Then
					internetAddress = doc.Getitemvalue("InternetAddress")
					If ( internetAddress (0) <> "" ) Then
						getEmailAddress = internetAddress (0)
						found = True
						Exit ForAll
					End If
				End If
			End If 
		End If
	End ForAll
	' if found is still False, the person was not found '
	If Not found Then
		MessageBox ( "Unable to locate " & recipientName & " in the address book, using " & recipientName & " as their email addresss" )
		getEmailAddress = recipientName
	End If

exiting:
	Exit Function
errorhandler:' report all errors in a messagebox '
	reason = "Function getEmailAddress: "
	reason = reason & "Error #" & Cstr (Err) & " (" & Error & ") on line " & Cstr (Erl)
	Messagebox reason, 16, "Error"
	Resume exiting
End Function

Hope someone else finds this useful….

Addendum

Dim addressBookList List As NotesDatabase

As Ben pointed out, re-opening every address book every time you want the email address is incredibly inefficient. So, declare a global variable for the address books and use the following function to open them. The code in getEmailAddress now reflects this….

%REM
	Function openAddressBooks
	Description: This Function assigns all address books to a NotesDatabase list and opens them
%END REM
Function openAddressBooks ( ) As Boolean
	Dim reason As String	

	On Error Goto errorhandler

	openAddressBooks = False
	
	ForAll addressBook In session.Addressbooks
		' open every Domino Directory '
		If ( addressBook.IsPublicAddressBook ) Then
			Set addressBookList (addressBook.FileName) = addressBook
			Call addressBookList (addressBook.FileName).Open( "", "" )
		End If
	End ForAll
	
	openAddressBooks = True

exiting:
	Call agentLog.LogAction ( "-------" ) 
	Call agentLog.LogAction ( "-------" ) 
	Exit Function
errorhandler:' report all errors in a messagebox '
	reason = "Error #" & Cstr (Err) & " (" & Error & ") on line " & Cstr (Erl)
	Messagebox reason, 16, "Error"
	Call agentLog.LogAction ( reason )
	Resume exiting
End Function