Solved How to add an attachment in Gmail managed in MS Access VBA

[nector]

After working almost the whole Xmas day 25th December 2024 to make sure that access is able to send an email from its own form. This process is now working okay though very length .

Last issue requiring your help is on attachment for an external document, here I'm using the actual path of the required document which is very cumbersome, is there a way where by VBA will allow a pop when clicking on attachment so that I can browse through the folder the same way we do it in outlook to select the required document?

The which require that is below , see attachment field:

With Newmail
        .Sender = "nectorzambia@gmail.com"
        .From = "Nector Prime Accounting Solutions"
        .To = Me.SendTo
        .Subject = Me.Subject
        .TextBody = Me.Message
        .AddAttachment Me.Attachment 'This is the field I need help to make it allow browsing the folders to select the required attachments
       
        End With

See also form for reference

 

[June7]

Use FileSystemObject dialog to navigate to a file. This is a common topic. Example:

Function GetFile()
' Requires reference to Microsoft Office xx.0 Object Library.
 
   Dim fDialog As Office.FileDialog
 
   ' Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
 
   With fDialog
      .InitialView = msoFileDialogViewDetails
      ' Allow user to make multiple or only one selection in dialog box
      .AllowMultiSelect = False
      ' Set the title of the dialog box.
      .title = "Please select one file."
      .InitialFileName = "C:\"
      ' Clear out the current filters, and add our own.
      .Filters.Clear
      .Filters.Add "Access file", "*.accdb"
      ' Show the dialog box. If the .Show method returns True, the
      ' user picked at least one file. If the .Show method returns
      ' False, the user clicked Cancel.
      If .Show = True Then
         GetFile = .SelectedItems(1)
      Else
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   End With
End Function

 

[nector]

I'm getting user un defined Function on fDialog As Office.FileDialog

Function GetFile()
' Requires reference to Microsoft Office xx.0 Object Library.
 
   Dim fDialog As Office.FileDialog
 
   ' Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
 
   With fDialog
      .InitialView = msoFileDialogViewDetails
      ' Allow user to make multiple or only one selection in dialog box
      .AllowMultiSelect = False
      ' Set the title of the dialog box.
      .title = "Please select one file."
      .InitialFileName = "C:\"
      ' Clear out the current filters, and add our own.
      .Filters.Clear
      .Filters.Add "Access file", "*.accdb"
      ' Show the dialog box. If the .Show method returns True, the
      ' user picked at least one file. If the .Show method returns
      ' False, the user clicked Cancel.
      If .Show = True Then
         GetFile = .SelectedItems(1)
      Else
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   End With
End Function

 

[isladogs]

As stated in the code sample, set the reference to the Microsoft Office 16.0 library

 

[June7]

Right.
You have Microsoft Access 16.0 Object and Microsoft Office 16.0 Access database engine Object but not Microsoft Office 16.0 Object

 

[nector]

Thanks isladogs

I'm calling the above function as below but it does allow only MS Access Database instead of any document, could it be I'm again making a mistake somewhere:

Me.Attachment = GetFile()

 

[June7]

Read the code, understand the code, modify as appropriate. This line .Filters.Add "Access file", "*.accdb" restricts files to view. What file types do you want? If you want to allow all then: .Filters.Add "All Files", "*.*"

 

[nector]

Many thanks June7 it has work very well

I have also managed to make CC: and Attachment Optional, I need now to add some tables for host credentials and customer's table.