• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Get Excel File name and Incorporate code into a txt

Dokat

Member
Hi,

I have below code.

Code:
 mItem.Body = "This is an automated message to inform you that " & _
                 Environ("username") & " has downloaded and is using Sales Report"

I'd like message to say rather than Sales Report the Excel File name.

Can someone help me on what code should i use to to pull the file name and incorporate it to the body of the message.

Thanks
 
Yes i have however getting Compile error sub or function not defined error.

Code:
    mItem.Body = "This is an automated message to inform you that " & _
                 Environ("username") & " has downloaded and is using" & ("ThisWorkbook.Name")

Thanks
 
Yes, still getting the same Compile error sub or function not defined error.
message.

Code:
    mItem.Body = "This is an automated message to inform you that " & _
                 Environ("username") & " has downloaded and is using" & ThisWorkbook.Name
 
You'll need to give more context.

Where does your code reside? Is it in Outlook or Excel?

Is workbook defined elsewhere in your code? etc.
 
Hi,

It resides in Excel. Yes it it is defined somewhere else

Here is the code i use.

Code:
Private Sub Workbook_Open()
GetUserFullName
    Dim oApp As Object 'Outlook.Application 'Object
    Dim ns As Object 'Namespace
    Dim fldr As Object 'MAPIFolder
    Dim mItem As Object 'Outlook.MailItem
    Dim sendTo As Object 'Outlook.Recipient
    Dim bOutlookFound As Boolean
    Dim FileOnly As String
    FileOnly = ThisWorkbook.Name
   
    On Error Resume Next
    Set oApp = GetObject(, "Outlook.Application")
    bOutlookFound = Err.Number = 0
    On Error GoTo 0
    If Not bOutlookFound Then Set oApp = CreateObject("Outlook.Application") 'New Outlook.Application
    '# Set the namespace and folder so you can add recipients
    Set ns = oApp.GetNamespace("MAPI")
    Set fldr = ns.GetDefaultFolder(6) 'olFolderInbox
    '# create an outlook MailItem:
    Set mItem = oApp.CreateItem(0) 'olMailItem
    '# assign a recipient
    Set sendTo = mItem.Recipients.Add("larro@partg.Com")
        sendTo.Type = 1 'To olTo
        '# Validate the recipients (not necessary if you qualify valid email addresses:
    For Each sendTo In mItem.Recipients
        sendTo.Resolve
    Next
    mItem.Subject = "A user has opened the Excel file"
    mItem.Body = "This is an automated message to inform you that " & _
                 Environ("username") & " has downloaded and is using" & FileOnly
    mItem.Save
    mItem.Send
    'If outlook was not already open, then quit
    If Not bOutlookFound Then oApp.Quit
    Set oApp = Nothing
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strDate As String
Dim Wk As Workbook
Dim datNow As Date
datNow = Now()
strDate = Format(datNow, "mmddyyyy hhmmss")
Thanks
 
Worked fine on my end. If you need further help, upload sample workbook where it's giving you issue.
 
That has nothing to do with ThisWorkbook.Name.

But with "GetUserFullName"

There is no Sub or Function defined with that name.
Not sure what it's supposed to do. Just remove it and see if it works.
 
Back
Top