Prompt user to choose files from specific folder to attach to email???

[jenmar10]

Below I have the VBA to automatically open an email after update on a field. Instead of attaching a specific attachment (in this case "C:\Folder1\Folder2\File.xlsx") I would like to automatically open the insert file browser in Outlook for that specific email so that the user can select one or multiple files for attachment from the folder it opens up to (in this case "C:\Folder1\Folder2\") and simply click insert.

Any ideas?

Private Sub Completion_Dt_AfterUpdate()
Dim strEmail As String
Dim strMsg As String
Dim oLook As Object
Dim oMail As Object

Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.createitem(0)
With oMail
.to = "Maria Maria"
.cc = "Some Person"
.body = "Please see attachment."
.Subject = "File attached."

.Attachments.Add "C:\Folder1\Folder2\File.xlsx"

'instead of attaching a specific file I would like to automatically open the browser for a file attachment so the user can select one or multiple files on their own.

.display

End With

Set oMail = Nothing
Set oLook = Nothing


End Sub

 

[PHV]

You may try to use a FileDialog object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jenmar10]

I'm not sure how to use that. Where would I add the code?

 

[TheAceMan1]

How are ya jenmar10 . . .

Perhaps this:

[blue]Public Function cc()
   Dim oLook As Object, oMail As Object, strEmail As String
   Dim FD As FileDialog, vrtSelectedItem As Variant

   Set FD = Application.FileDialog(msoFileDialogFilePicker)
   Set oLook = CreateObject("Outlook.Application")
   Set oMail = oLook.createitem(0)
   
   With oMail
      .to = "Maria Maria"
      .cc = "Some Person"
      .body = "Please see attachment."
      .Subject = "File attached."
      
      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 Function[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[jenmar10]

Thanks for the help... but I've been getting an error when I Dim As FileDialog. The error highlights the 'FD As Dialogue' line and says "User-defined Type not defined."

My code below:

Private Sub Scanned_Dt_AfterUpdate()
Dim oLook As Object
Dim oMail As Object
Dim FD As FileDialog
vrtSelectedItem As Variant

Set FD = Application.FileDialog(msoFileDialogFilePicker)
Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.createitem(0)

With oMail
.to = "ToGroup"
.cc = "CcGroup"
.body = "See attached."
.Subject = "Attachment"

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

 

[PHV]

You should reference the Micrososoft Office x.y Object Library

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

OOps, sorry for the typo.
You should reference the Microsoft Office x.y Object Library

When in VBE, menu Tools -> References ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[LeonAtRC]

Might be easier to just:
Dim fd as object

It's the:
Set FD = Application.FileDialog(msoFileDialogFilePicker)
that makes the object a file dialog object.

 

[jenmar10]

Thanks everyone for all the help. I figured out the code and it looks like this:

Private Sub Completion_Dt_AfterUpdate()
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 = Application.FileDialog(3)

With oMail
.to = "Jane Smith"
.cc = "John Smith"
.body = "Please see attached."
.Subject = "Info Attached"

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

If BrowseJob.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


So basically, after updating or changing the Completion_Dt field, the user will be prompted to select attachments from a predetermined folder and the selected attachments will be added to the email.

Thanks All!!!

 

[jenmar10]

Oops... The If statement should read:

If FD.Show = True Then
For Each vrtSelectedItem In FD.SelectedItems
.Attachments.Add vrtSelectedItem
Next
End If