1

OK so I have working code, however the browser for adding files shows up in the background. This wouldn't be so bad but it freezes outlook when this happens making it difficult to get to quickly.

I'm trying to figure out how to make the popup browser show on top of everything so it's not buried. Also it is incredibly slow so if there is a more efficient way, that would be great as well. Here is what I have so far. 

Option Explicit
Private Sub AttachmentFile()
Dim oLook As Object
Dim oMail As Object
Dim FD As Object
Dim vrtSelectedItem As Variant

    Set oLook = CreateObject("Outlook.Application")
    Set oMail = oLook.CreateItem(0)
    Set FD = Excel.Application.FileDialog(3)

    With oMail

            FD.AllowMultiSelect = True
            FD.Filters.Clear
            FD.Filters.Add "All Files", "*.*"
            FD.InitialFileName = "\\ad\dfs\Shared Data\"

            If FD.Show = True Then
                  For Each vrtSelectedItem In FD.SelectedItems
                  .Attachments.Add vrtSelectedItem
                    Next
            End If
            .Display
    End With
    Set FD = Nothing
    Set oMail = Nothing
    Set oLook = Nothing

End Sub
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
HalfWit
  • 31
  • 6
  • Are you doing this from Excel? Or Outlook? The issue might be that your forcing it to open Excel to get the FIleDialog – dbmitch Jun 08 '18 at 16:48
  • I'm doing it from outlook but I couldn't find a way to do this without the excel reference. That makes sense on why it's slow, and there has to be a more efficent way. I can tolerate how slow it is but it keeps locking the outlook screen above the popup filedialog so it can't be seen. This is my primary concern. Speed by doing it through outlook is secondary. If you can solve both, you're my hero! – HalfWit Jun 08 '18 at 16:57
  • Actually thinking about it, it may solve both by doing it through outlook because by using excel, If I already have an excel window open underneath outlook, that could the why the filedialog is under outlook. But while researching how to do that many people said it can't be done in outlook. But I'm sure there is a work around. Here's that post... https://stackoverflow.com/questions/43919275/outlook-application-filedialog-not-found – HalfWit Jun 08 '18 at 17:01
  • I've used a VBA API and class to get around opening other applications. If you can't find an example of that I'm sure I can send some code – dbmitch Jun 08 '18 at 19:29
  • I don't need to open other applications, I just need to attach a file to an email I'm sending, but the files and locations vary based on the job so I need to be able to pick which file I'm uploading. I have further code that autofills the email based on the name of the attached file. So I just need an outlook only solution to attaching. Almost a macro that is clicking the paperclip button on the email. – HalfWit Jun 08 '18 at 22:03
  • There's one example of using a VBA CommonDIalog Class at http://visguy.com/vgforum/index.php?topic=738.0 - this works with selecting multiple files as well. Uses the `GetOpenFileName` call in `comdlg32.dll` – dbmitch Jun 08 '18 at 23:08
  • I tried. It's not working in 64 bit... I am getting errors when trying to call it. I may be implementing it wrong as I'm not great with VBA... – HalfWit Jun 08 '18 at 23:24
  • No. As long as you can see the DLL file? it might be a 64 bit thing and the way you have to declare variables. Is Outlook a 64 bit app? – dbmitch Jun 08 '18 at 23:59

1 Answers1

0

Although there is no FileDialog object in Outlook you may simulate pressing a button with ExecuteMso.

To open the Outlook Insert File dialog press the AttachFile button:

Private Sub ExecuteMso_AttachFile()

    Dim currItem As Object

    On Error Resume Next
    Set currItem = ActiveInspector.currentItem
    On Error GoTo 0

    If currItem Is Nothing Then Set currItem = CreateItem(olMailItem)

    currItem.Display

    ' Hover over the icon that you would add as a button
    ' The IdMso to use in ExecuteMso is the last part of the text displayed.
    ActiveInspector.CommandBars.ExecuteMso ("AttachFile")

End Sub
niton
  • 8,771
  • 21
  • 32
  • 52