VBA - Identify name of the file

[eurano]

Hi,
I have following problem. Im sharing some file wich contain macro to automaticly send e-mail from Lotus Notes and I want to make my coworkers to use it. Underlined value in the script is name of the nsf file wich is contained in the same directory in their computers (its the only file in this folder). I want to change macro to somehow identify name of the file and insert it in outlined command in the scirpt so whenever some1 opens it from other computer macro will still work. Or if its too hard to achieve i want to use the "if" command to the code and add 2 coworkers file name but im not good enough with VBA.

directory of the file:
C:\Users\xxxx\AppData\Local\IBM\Notes\Data\mail\gdansk




/code
Option Explicit

'Function for finding the first top level window in the windows list
'that meet the criteria.
Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long


Sub Send_Formatted_Range_Data()
Dim oWorkSpace As Object, oUIDoc As Object
Dim rnBody As Range
Dim lnRetVal As Long

Const stTo As String = "xxxxx@yyy.com"
Const stCC As String = ""
Const stBody As String = vbCrLf & " "
Const stSubject As String = "Report xxx"
Const stMsg As String = "An e-mail has been succesfully created and saved."

'Check if Lotus Notes is open or not.
lnRetVal = FindWindow("NOTES", vbNullString)

If lnRetVal = 0 Then
MsgBox "Please make sure that Lotus Notes is open!", vbExclamation
Exit Sub
End If

Application.ScreenUpdating = False

'A named range in the activesheet is in use.
Set rnBody = ActiveSheet.Range("Report")
rnBody.Copy

'Instantiate the Lotus Notes COM's objects.
Set oWorkSpace = CreateObject("Notes.NotesUIWorkspace")

On Error Resume Next
Set oUIDoc = oWorkSpace.ComposeDocument("", "mail\gdansk\xxx.nsf", "Memo")
On Error GoTo 0

Set oUIDoc = oWorkSpace.CurrentDocument
'Using LotusScript to create the e-mail.
Call oUIDoc.FieldSetText("EnterSendTo", stTo)
Call oUIDoc.FieldSetText("EnterCopyTo", stCC)
Call oUIDoc.FieldSetText("Subject", stSubject)

'If You experience any issues with the above three lines then replace it with:
'Call oUIDoc.FieldAppendText("EnterSendTo", stTo)
'Call oUIDoc.FieldAppendText("EnterCopyTo", stCC)

'Call oUIDoc.FieldAppendText("Subject", stSubject)

'The can be used if You want to add a message into the created document.
Call oUIDoc.FieldAppendText("Body", vbNewLine & stBody)

'Here the selected range is pasted into the body of the outgoing e-mail.
Call oUIDoc.GotoField("Body")
Call oUIDoc.Paste
Set oUIDoc = oWorkSpace.CurrentDocument
Call oUIDoc.GotoField("Body")
Call oUIDoc.InsertText("Proszę zorganizować wizytę serwisu w celu dokonania przeglądów wózków:") 'calls a function to return the body contents.
'Save the created document.
Call oUIDoc.Save(True, False, False)
Call oUIDoc.Send(True)

'Release objects from memory.
Set oWorkSpace = Nothing
Set oUIDoc = Nothing

With Application
.CutCopyMode = False
.ScreenUpdating = True
End With

MsgBox stMsg, vbInformation

'Activate Lotus Notes.
AppActivate ("Notes")

End Sub
\code

 

[Jeffrey Lopez]

So I am not sure I can help you per se, but I will do what I can.

First rather, than using something like this:

[COLOR=#574123]'Check if Lotus Notes is open or not.[/COLOR]
[COLOR=#574123]lnRetVal = FindWindow("NOTES", vbNullString)[/COLOR]

[COLOR=#574123]If lnRetVal = 0 Then[/COLOR]
[COLOR=#574123]MsgBox "Please make sure that Lotus Notes is open!", vbExclamation[/COLOR]
[COLOR=#574123]Exit Sub[/COLOR]
[COLOR=#574123]End If[/COLOR]

You can use something like this:

Set Session = CreateObject("Notes.NotesSession")

This will do 1 of 2 things. If notes is NOT open, it will open Lotus Notes, if it is open we can now call "Session" as our instances of Lotus Notes.
Once you have the session, you use something along these lines:

UserName = Session.UserName

Unless your IT group used some crazy naming convention for the .nsf file, this should work... Every company is different, so I am not sure where to go from here. Ours I have do some LEFT/RIGHT/LEN/INSTR to file the nsf file name, but yours may be a bit easier.

Anyways hope that helps!

 

[Roderick_E]

Dim the xxxx as a string then use
xxxx = Environ("username")
or
xxxx = Environ("computername")

 

[eurano]

I dimed xxxx as string and pasted xxxx = Environ("username") in the code but error telling me there is no file xxxx in the folder.

 

[Jeffrey Lopez]

I dimed xxxx as string and pasted xxxx = Environ("username") in the code but error telling me there is no file xxxx in the folder.

Did you try what I suggested?

Your environ user name, 9 times out 10 is not the same as your Notes.nsf file. Environ is the user name of the logged on user, where at your .nsf has a different file name.

When you get the user name of the "Lotus Notes" session you should see the file name of your .nsf file.

Either way let me know...

 

[eurano]

Unfortunently the file name is not same as user name.