Solved: Lotus Notes to Excel (If, your looking for a Challenge)

[valendj]

This is probably very difficult. I would like to have excel auto populate a field in excel from lotus notes. I would like the field to populate with the user name and the column to be activated by click. I know not a lot of people have Lotus Notes so, I am sure I narrowed the list of experts down greatly. The sheet has many users and instead of them typing their name in the “Requested by” column they type their department I have explained this many times and its hard to track down a person who put in the request for accounting if I have to search the whole Accounting division for that person! I have no idea where to start. Thanks in Advance!

[Kenneth Hobs]

I am not sure what you mean. Are you wanting to find all the names in a Lotus Notes Addressbook from Excel? You would probably need to know the NSF name to start with.

I guess that your "Requested by" means a column in Excel. If so, if a user typed "Kenneth Hobson" and the Lotus Notes Addressbook had it as "Ken Hobson", it would obviously not be sent properly. Ergo your request I guess.

I have sent emails from Excel to Lotus Notes with attachments.

[valendj]

Kenneth,
What I mean is for excel to pull up the user name of the lotus notes account opened. So, If double clicked or triggered the cell to run the macro would pull up my email name minus the @company name and just return the being portion of the email name which is the users name. What I am looking for is when a user inputs data into a shared file their name auto fills. This is similar to using excel to pull up the users lan name or windows account login name.

I am not sure what you mean. Are you wanting to find all the names in a Lotus Notes Addressbook from Excel? You would probably need to know the NSF name to start with.

I guess that your "Requested by" means a column in Excel. If so, if a user typed "Kenneth Hobson" and the Lotus Notes Addressbook had it as "Ken Hobson", it would obviously not be sent properly. Ergo your request I guess.

I have sent emails from Excel to Lotus Notes with attachments.

[Kenneth Hobs]

Look at the Session.Username part.

[VBA]'Brian Walters, ozgrid.com, #67089
Sub SendLotusMail()

Dim Maildb As Object 'The mail database
Dim UserName As String 'The current users notes name
Dim MailDbName As String 'The current users notes mail database name
Dim MailDoc As Object 'The mail document itself
Dim Session As Object 'The notes session
Dim Subject As String 'The subject string
Dim ReturnReceipt As String 'The ReturnReceipt string
Dim Recipient As String 'The Recipient string (or you could use the list)
Dim Recip(10) As Variant 'The Recipient list
Dim BodyText As String 'The body text
Dim SaveIt As Boolean 'Save to sent mail
Dim WasOpen As Integer 'Checking to see if the Mail DB was already

Subject = "Test Auto-generate"

Recipient = "anyone@yahoo.com"

SaveIt = True
Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
Set Maildb = Session.GETDATABASE("", MailDbName)
If Maildb.IsOpen = True Then
WasOpen = 1 'Already open for mail
Else
WasOpen = 0
Maildb.OPENMAIL 'This will prompt you for password
End If
Set MailDoc = Maildb.CREATEDOCUMENT
MailDoc.Form = "Memo"
MailDoc.sendto = Recipient
MailDoc.Subject = Subject
MailDoc.Body = "Did it work?"
MailDoc.ReturnReceipt = "1"

MailDoc.SAVEMESSAGEONSEND = SaveIt
MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
MailDoc.Send 0, Recipient

Set Maildb = Nothing
Set MailDoc = Nothing
'Set AttachME = Nothing
'Set EmbedObj = Nothing
If WasOpen = 1 Then
Set Session = Nothing
ElseIf WasOpen = 0 Then
Set Session = Nothing
End If

Dim Msg, Style, Title, Help, Ctxt
Msg = "E-mail has been sent to " & Recipient & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Press OK to continue."
Style = vbOKOnly + vbInformation
Title = "Open Issues List"
MsgBox Msg, Style, Title, Help, Ctxt
End Sub



[/VBA]

[valendj]

Unfortunately,
I am not a programmer and I don’t know how to create the code to return the username of the lotus notes session to a excel field by click.

Look at the Session.Username part.

[vba]'Brian Walters, ozgrid.com, #67089
Sub SendLotusMail()

Dim Maildb As Object 'The mail database
Dim UserName As String 'The current users notes name
Dim MailDbName As String 'The current users notes mail database name
Dim MailDoc As Object 'The mail document itself
Dim Session As Object 'The notes session
Dim Subject As String 'The subject string
Dim ReturnReceipt As String 'The ReturnReceipt string
Dim Recipient As String 'The Recipient string (or you could use the list)
Dim Recip(10) As Variant 'The Recipient list
Dim BodyText As String 'The body text
Dim SaveIt As Boolean 'Save to sent mail
Dim WasOpen As Integer 'Checking to see if the Mail DB was already

Subject = "Test Auto-generate"

Recipient = "anyone@yahoo.com"

SaveIt = True
Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
Set Maildb = Session.GETDATABASE("", MailDbName)
If Maildb.IsOpen = True Then
WasOpen = 1 'Already open for mail
Else
WasOpen = 0
Maildb.OPENMAIL 'This will prompt you for password
End If
Set MailDoc = Maildb.CREATEDOCUMENT
MailDoc.Form = "Memo"
MailDoc.sendto = Recipient
MailDoc.Subject = Subject
MailDoc.Body = "Did it work?"
MailDoc.ReturnReceipt = "1"

MailDoc.SAVEMESSAGEONSEND = SaveIt
MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
MailDoc.Send 0, Recipient

Set Maildb = Nothing
Set MailDoc = Nothing
'Set AttachME = Nothing
'Set EmbedObj = Nothing
If WasOpen = 1 Then
Set Session = Nothing
ElseIf WasOpen = 0 Then
Set Session = Nothing
End If

Dim Msg, Style, Title, Help, Ctxt
Msg = "E-mail has been sent to " & Recipient & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Press OK to continue."
Style = vbOKOnly + vbInformation
Title = "Open Issues List"
MsgBox Msg, Style, Title, Help, Ctxt
End Sub



[/vba]

[Kenneth Hobs]

Please try not to copy all of another's post in your reply. In particular, the code part needs not be repeated.

Copy the code that follows and paste as detailed next.
[vba]Function LotusNotesUserName() As String
Dim Session As Object
Dim s As String, sLen As Long
Set Session = CreateObject("Notes.NotesSession")
s = Session.UserName
Set Session = Nothing
'LotusNotesUserName = Right(s, (Len(s) - 3))
s = Right$(s, (Len(s) - 3))
LotusNotesUserName = Left$(s, (InStr(1, s, "/") - 1))
End Function
[/vba]
To paste worksheet code:

1. Right click the worksheet tab.
2. Select View Code.
3. Paste the code into the right-hand code window.
4. Close the VBE, save the file if desired.

[vba]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Range
Set r = Intersect(Target, Range("B:B"))
If Target.Column <> 2 Then Exit Sub
r.Value2 = LotusNotesUserName
r.Columns.AutoFit
Set r = Nothing
End Sub
[/vba] To paste module code to a new module:

1. Press Alt + F11 to open the Visual Basic Editor (VBE).
2. From the Menu, choose Insert-Module.
3. Paste the code into the right-hand code window.
4. Close the VBE, save the file if desired.

[valendj]

Kenneth,
I tried the code and it does not work.


[quote=Kenneth Hobs]Please try not to copy all of another's post in your reply. In particular, the code part needs not be repeated.

Copy the code that follows and paste as detailed next.
[vba]Function LotusNotesUserName() As String
Dim Session As Object
Dim s As String, sLen As Long
Set Session = CreateObject("Notes.NotesSession")
s = Session.UserName
Set Session = Nothing
]

[valendj]

That code works great!!! Thank you so much! I put the code in the wrong spot! Woops! Kenneth I would be lost without your help! This is the best site!



[quote=valendj]Kenneth,
I tried the code and it does not work.

Please try not to copy all of another's post in your reply. In particular, the code part needs not be repeated.

Copy the code that follows and paste as detailed next.
[vba]Function LotusNotesUserName() As String
Dim Session As Object
Dim s As String, sLen As Long
Set Session = CreateObject("Notes.NotesSession")
s = Session.UserName
Set Session = Nothing
]