File Browser snag

[Dave Eyley]

Hi all,

I'm using a piece of code to open a dialog window to find a file and then add it to field on a form where the file will be attached to an email and sent out automatically.

The code -

If Not IsNothing(MySet![email]) Then
        Mailcount = Mailcount + 1
        Set appOutLook = CreateObject("Outlook.Application")
        Set MailOutLook = appOutLook.CreateItem(olMailItem)
        With MailOutLook
            'set the recipient list
            .To = MySet![email]
        
            'set the subject
            .Subject = Me.BulkSubject
        
            'set the body text
            .Body = Me!BulkBody
            
            'add the attachment
            If Me!AttachmentFlag = True Then
               
                [COLOR="Red"].attachments.Add Me!BulkAttachment[/COLOR]
                
            End If
            'send or display the email
            If Me!OutlookOpt = 2 Then
                .Send
            Else
                .Display
            End If
        
        End With
        'get rid of our object references
        Set appOutLook = Nothing
        Set MailOutLook = Nothing
    End If

The problem is the code stops with an error at the point in red. If, however I type the path as say, "C:\users\George\documents\file.doc", it works OK.

It doesn't seem to like the reference to Me!BulkAttachment

Can anyone suggest a solution?

Dave

 

[mdlueck]

It doesn't seem to like the reference to Me!BulkAttachment

So put a watch on that and see what is its value.

Or do a Debug.Print of it.

 

[Dave Eyley]

Hi Michael,

Thanks for the response. The error message is - This object does not support this property or method.

The string is valid - "C:\User\George\Documents\File.doc"

If I replace the reference to the control BulkAttachment with -

.Attachments.Add "C:\User\George\Documents\File.doc" it works OK

but then I'm stuck with hardcoding the path and file name.

There must be a way to refer to the string received by the 'find file' function and apply it to the form control, but I can't work out how.

 

[mdlueck]

There must be a way to refer to the string received by the 'find file' function and apply it to the form control, but I can't work out how.

What "find file" function might you be talking about? I do not see such in your example code.

 

[Dave Eyley]

The 'find file' function is a standard function used in the form -
Me!BulkAttachment = tsGetFileFromUser()
The function opens a dialog window where the user selects a file and then it assigns the filepath and name to a control on the form.
Another button on the form collects the email address, subject, bodytext and attachment and creates an Outlook email, either in .send or .Display mode.

The 'find file' works fine and returns the full path in the expected format. It's the way the code above can't seem to use the control to refer to the path that's the problem.

Thanks for your patience...

 

[NigelShaw]

Hi,

have you tried

Me!BulkAttachment.Value
Me!BulkAttachment.Text

you could also use-

Dim sAttachment As String

sAttachment = Nz(Me!BulkAttachment.Value, "")

Cheers

Nidge

 

[Dave Eyley]

Thanks Nidge

Me!BulkAttachment.Value - worked a treat...

Cheers

Dave

 

[mdlueck]

I am glad to see your problem is resolved already, Dave.

The function opens a dialog window where the user selects a file and then it assigns the filepath and name to a control on the form.

I have only coded with the MSOffice dialog box in Access / VBA, as follows:

  Dim fDialog As Office.FileDialog
  Dim strFilename As String

  'Set up the File Dialog
  Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
  With fDialog
    .AllowMultiSelect = False
    .title = "Import Parts List - Please select one file to import into this product."
    .Filters.Clear
    .Filters.Add "Text Files", "*.csv"
    If .Show = True Then
      'Receive back which file was selected
      strFilename = .SelectedItems.Item(1)
    Else
      'Dialog cancled, so exit this method
      modparts_importwizard_ReadImportFile = False
      GoTo Exit_modparts_importwizard_ReadImportFile
    End If
  End With