Invoke Attachments dialog with preset folder path

[ldoodle]

Hey,

I have a user form that basically presets some common data (from SQL).

Once the form is completed and the user presses OK, it passes the data to an Outlook template and opens a draft for them to choose recipients and send.

This works great. However I would like to invoke the attachments dialog, once the template is opened, that defaults to a predetermined folder (from one of the form fields). I do not want to programmatically attach stuff. Just open the dialog.

Is this possible?

Thanks

[gmayor]

The short answer is that you can't, but if you are running the code from Outlook (from which the FileDialog(msoFileDialogFilePicker) is not available in VBA, you could use one of the Office apps that does to provide it. This means a small delay while the app opens, but it works well enough. Call the following function with the filepath you wish to start from e.g.

Sub test()
    MsgBox BrowseforFile("C:\Path\")
lbl_Exit:
    Exit Sub
End Sub

If you are running the code from some other app, such as Excel (used here to provide the dialog) you don't need to Create or Get the object as you already have it.

Public Function BrowseforFile(strPath As String) As String
Dim xlApp As Object
Dim fdFolder As Object
Dim bStarted As Boolean
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err <> 0 Then
        Set xlApp = CreateObject("Excel.Application")
        bStarted = True
    End If
    On Error GoTo 0
    xlApp.Visible = False
    Set fdFolder = xlApp.FileDialog(msoFileDialogFilePicker)
    With fdFolder
        .Title = "Select file and click OK"
        .AllowMultiSelect = False
        .InitialFileName = strPath
        .InitialView = msoFileDialogViewList
        If .Show <> -1 Then GoTo err_Handler:
        BrowseforFile = .SelectedItems.Item(1)
    End With
lbl_Exit:
    If bStarted Then xlApp.Quit
    Set xlApp = Nothing
    Exit Function
err_Handler:
    BrowseforFile = ""
    GoTo lbl_Exit
End Function

[ldoodle]

Thanks gmayor. I thought that would be the case.

I have used FileDialog for a previous project so I am aware of it's existence.

Thanks

[ldoodle]

I have a weird issue.

On my machine, FileDialog works fine. If I then copy my VbaProject.OTM file to another computer (and different user), FileDialog does open, but it seems hidden behind all other windows - you have to use Switch To in Task Manager.

However, on the problem machine, if I simply open VBA Editor and view code for any module/form, without changing a single thing, and close down VBA Editor, that fixes the problem and FileDialog now opens in the foreground.

Does some 'thing' unique to a machine get stored in the OTM file, that stops it being used elsewhere, and simply viewing the code removes that uniqueness, or changes it to the local machine.

This leads to another question - I want to deploy this code to all users in my company (60+). What is the method to use for doing so, that doesn't involve visiting each machine importing it, and/or telling users how to do it themselves. That's simply not workable for something new as there is bound to be teething issues/user change requests and you can't repeat the manual process 100 times per machine/user.

Word has a Startup folder for things like this - Outlook has no such equivalent.