VBA - Identify name of the file

eurano

New Member
Joined
Sep 13, 2015
Messages
25
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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:

Code:
[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:
Code:
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:

Code:
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!
 
Upvote 0
Dim the xxxx as a string then use
xxxx = Environ("username")
or
xxxx = Environ("computername")
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top