Use box to change part of VBA code?

[ExcelRookie01]

Hi there.

I need to make a function for exporting a file as an PDF and attach it to an e-mail. I have the vba working for me, by inserting my username (U18695) in the FilePath.

However I need the function to work for multiple users, so I was wondering if it is possible to have an inputbox show up when running the macro, where the user can type in their username and then have the variable username inserted into the filepath in the code???

I have inserted the first part of the code below. Please let me know if you would need the whole code to help me out
Thanks!!


CODE:

Sub SendBelæg_PO()

Dim OutApp As Object

Dim OutMail As Object

Dim Filepath As String

Dim Filename As String


'Set the file path and name for the PDF file

Filepath = "C:\Users\U18695\Documents\"

Filename = ActiveWorkbook.Name & ".pdf"

 

[Georgiboy]

How about capturing the username rather than typing it?

Sub SendBelæg_PO()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim Filepath As String
    Dim Filename As String
    Dim objNetwork As Object
    Dim uName As String
   
    'Set the file path and name for the PDF file
    Set objNetwork = CreateObject("WScript.Network")
    uName = objNetwork.UserName
    Filepath = "C:\Users\" & uName & "\Documents\"
    Filename = ActiveWorkbook.Name & ".pdf"

    Set objNetwork = Nothing
End Sub

 

[MARK858]

Why do you need an input box? can you not just use the code below?

Filepath = "C:\Users\" & Application.UserName & "\Documents\"

or

Filepath = "C:\Users\" & Environ("USERNAME") & "\Documents\"

depending on what username you want

 

[ExcelRookie01]

Why do you need an input box? can you not just use the code below?

Filepath = "C:\Users\" & Application.UserName & "\Documents\"

or

Filepath = "C:\Users\" & Environ("USERNAME") & "\Documents\"

depending on what username you want

Just tried this out.

The first one didn't work for some reason

The second one worked for me, but not when my co-worker tried it out


I have attached some more of the code, as it was in the line beginning with "ActiveSheet" the macro reported a bug



Sub SendBelæg_PO()

Dim OutApp As Object

Dim OutMail As Object

Dim Filepath As String

Dim Filename As String


'Set the file path and name for the PDF file

Filepath = "C:\Users\" & Environ("USERNAME") & "\Documents\"

Filename = ActiveWorkbook.Name & ".pdf"


'Export the active sheet as a PDF file

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Filepath & Filename, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False


'Create a new Outlook email

Set OutApp = CreateObject("Outlook.Application")

Set OutMail = OutApp.CreateItem(0)

 

[MARK858]

What about the code in post 2?

 

[ExcelRookie01]

That is the code that only worked from my user, but didn't work for my co-worker with a different username

 

[MARK858]

I have attached some more of the code, as it was in the line beginning with "ActiveSheet" the macro reported a bug

What does the error state?

Also does the filepath look correct in the Immediate Window if you run the code below?

Dim Filename As String


'Set the file path and name for the PDF file

Filepath = "C:\Users\" & Environ("USERNAME") & "\Documents\"

Filename = ActiveWorkbook.Name & ".pdf"

Debug.Print Filename

 

[ExcelRookie01]

Just testet it out with another co-worker and now it works!

Thanks a lot for the much simpler solution, than I thought I needed!

 

[MARK858]

You're welcome