Help with AfterUpdate event to add Outlook Attachment to Hyperlink field (1 Viewer)

gojets1721

Registered User.
Local time
Yesterday, 20:55
Joined
Jun 11, 2019
Messages
430
Hello. Bit of a unique/challenging question. I have looked for some answers online but can't find anything. This might be a bit too specific of an issue.

I have a Hyperlink control in my form to allow users to drag a file from a File Explorer onto the control and it saves that file in a designated shared folder. It works great.

The issue is that it does not work if you drag a file attachment from Outlook. Nothing is added to the control. I use the below AfterUpdate code in the hyperlink control to achieve the above.

Any suggestions on if this can be tweaked to allow files to be dragged from Outlook?

Code:
Private Sub txtHyperLinkIN_AfterUpdate()

    Dim strInPath As String
    Dim strFileName As String
    Dim strFileFolder As String
    Dim strOutPath As String
    Dim strReportNumber As String
    Dim strTempFileName As String
    Dim strFileExt As String
    
    DoCmd.SetWarnings False
    
    strFileFolder = "\Assets\Attachments\"  'specify the output folder
    
    strInPath = Me!txtHyperLinkIN.Hyperlink.Address
    
    strReportNumber = Me.ID
    strTempFileName = strReportNumber & " - " & Format(Now, "yyyymmddhhmmss")
    
    If Len(strInPath) > 0 Then
        strFileName = Right(strInPath, Len(strInPath) - InStrRev(strInPath, "\"))  'get the file name
        strFileExt = Right(strFileName, Len(strFileName) - InStrRev(strFileName, ".") + 1) ' get the file extension with dot
    
    'build the new path with output folder path and record number and date and extension
            strFileFolder = strFileFolder & strTempFileName & strFileExt
    
        FileCopy strInPath, strFileFolder
    
        Me!HyperLinkOUT = "#" & strFileFolder & "#"
        
End Sub
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 04:55
Joined
Jul 9, 2003
Messages
16,418
I couldn't answer your question! So I thought I'll asked chat GTP! Here is a copy of the exchange.

Uncle Gizmo
I have a Hyperlink control in my form to allow users to drag a file from a File Explorer onto the control and it saves that file in a designated shared folder with this code:-

Code:
Private Sub txtHyperLinkIN_AfterUpdate()

    Dim strInPath As String
    Dim strFileName As String
    Dim strFileFolder As String
    Dim strOutPath As String
    Dim strReportNumber As String
    Dim strTempFileName As String
    Dim strFileExt As String
   
    DoCmd.SetWarnings False
   
    strFileFolder = "\Assets\Attachments\"  'specify the output folder
   
    strInPath = Me!txtHyperLinkIN.Hyperlink.Address
   
    strReportNumber = Me.ID
    strTempFileName = strReportNumber & " - " & Format(Now, "yyyymmddhhmmss")
   
    If Len(strInPath) > 0 Then
        strFileName = Right(strInPath, Len(strInPath) - InStrRev(strInPath, "\"))  'get the file name
        strFileExt = Right(strFileName, Len(strFileName) - InStrRev(strFileName, ".") + 1) ' get the file extension with dot
   
    'build the new path with output folder path and record number and date and extension
            strFileFolder = strFileFolder & strTempFileName & strFileExt
   
        FileCopy strInPath, strFileFolder
   
        Me!HyperLinkOUT = "#" & strFileFolder & "#"
       
End Sub
It does not work if you drag a file attachment from Outlook. Nothing is added to the control.

Can you modify the code to allow files to be dragged from Outlook?

ChatGPT
When a file is dragged and dropped from Microsoft Outlook into an application, the file is not being referenced directly from the filesystem (as it would be from File Explorer). Instead, the file is an attachment within the Outlook application and it needs to be extracted and saved into the filesystem before you can work with it.

Unfortunately, Microsoft Access (VBA) doesn't have built-in features to handle such scenarios directly. You would need to use an Outlook add-in or another programming language that can interact with Outlook's COM objects, such as C#, VB.NET or Python.

Here is a very basic example of how you might do this in VB.NET:

Code:
Imports Microsoft.Office.Interop

Public Sub SaveAttachmentsFromSelection()
    Dim outlookApp = New Outlook.Application
    Dim selection = outlookApp.ActiveExplorer().Selection

    For Each mailItem In selection
        Dim attachments = mailItem.Attachments

        For i = 1 To attachments.Count
            Dim attachment = attachments.Item(i)
            attachment.SaveAsFile("PathToYourFolder\" & attachment.FileName)
        Next
    Next
End Sub


In this code, we're accessing the currently selected items in the active Outlook window and looping through each item. For each item, we get its attachments and save them to a specified directory.

To integrate this into your Access application, you'd likely need to create a COM-visible DLL using VB.NET or C#, then reference that DLL in your Access application. You'd call the relevant function from the DLL when a user drags an Outlook file into your Access application.

Please note that creating COM-visible DLLs and interacting with them from Access is a non-trivial task that requires understanding of .NET framework, COM interoperability and the security settings of the machine where the Access application will be running.

Also remember that you should handle errors and edge cases, such as when no items are selected in Outlook, when the selected item is not a MailItem (it could be a meeting, for instance), when the MailItem has no attachments, when the attachment cannot be saved (because of permissions, lack of disk space, etc), and so forth.

Before implementing this, you should evaluate if this is the best approach for your needs and consider alternatives, such as instructing users to first save the file to their local drive before dragging it into your Access application.

Uncle Gizmo
Can I use Microsoft.Office.Interop in MS Access VBA?

ChatGPT
Yes, you can use Microsoft.Office.Interop objects within VBA. However, they are typically more difficult to work with in VBA as compared to .NET languages such as VB.NET or C#. Here's how you can use Interop to access Outlook from Access VBA:

In VBA, you need to set a reference to the Outlook Object Library. Go to Tools -> References... in the VBA editor and then tick the Microsoft Outlook XX.0 Object Library.

Here's a sample of how you might use it:

Code:
Dim objOutlook As Outlook.Application
Dim objNamespace As Outlook.Namespace
Dim objMail As Outlook.MailItem
Dim objAttachments As Outlook.Attachments
Dim strPath As String

strPath = "C:\YourPath\" 'Modify this to your path

'Get a reference to Outlook
Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")

'Select a mail item
On Error Resume Next
Set objMail = objOutlook.ActiveExplorer.Selection.Item(1)
On Error GoTo 0

'If a mail item is selected
If Not objMail Is Nothing Then
    Set objAttachments = objMail.Attachments

    'Loop over the attachments and save them
    For i = 1 To objAttachments.Count
        objAttachments.Item(i).SaveAsFile strPath & objAttachments.Item(i).FileName
    Next i
End If

'Clean up
Set objAttachments = Nothing
Set objMail = Nothing
Set objNamespace = Nothing
Set objOutlook = Nothing

This code works similarly to the VB.NET version, but within Access VBA instead. When you run this, it will save all attachments from the currently selected email in Outlook to the specified path.

Remember to replace "C:\YourPath\" with your target path.

This code assumes that an Outlook window is open and an email is selected when it's run. You would need to add error handling code for situations where this is not the case, or where other errors might occur.

For instance, the Outlook application may not be running, the active window in Outlook might not be a mail window, or the selected item might not be a mail item. Also, saving the attachment might fail for a variety of reasons, such as the file already existing in the target location, the path being invalid, insufficient permissions, and so on.
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 04:55
Joined
Jul 9, 2003
Messages
16,418
I Asked Chat GPT:-

Uncle Gizmo
could you reformat both code blocks in to something i can cut and paste in to my form module?

ChatGPT
Sure, here is how you can incorporate the sample code into your existing Access VBA code. I'm including code to get the file name from Outlook, build a new path, and copy the file to a new location, similar to what your original code does:


Code:
'VBA
Private Sub txtHyperLinkIN_AfterUpdate()

    Dim objOutlook As Outlook.Application
    Dim objNamespace As Outlook.Namespace
    Dim objMail As Outlook.MailItem
    Dim objAttachments As Outlook.Attachments

    Dim strFileFolder As String
    Dim strOutPath As String
    Dim strReportNumber As String
    Dim strTempFileName As String
    Dim strFileExt As String
    
    strFileFolder = "\Assets\Attachments\"  'specify the output folder
    strReportNumber = Me.ID
    strTempFileName = strReportNumber & " - " & Format(Now, "yyyymmddhhmmss")
    
    ' Get a reference to Outlook
    Set objOutlook = CreateObject("Outlook.Application")
    Set objNamespace = objOutlook.GetNamespace("MAPI")

    ' Select a mail item
    On Error Resume Next
    Set objMail = objOutlook.ActiveExplorer.Selection.Item(1)
    On Error GoTo 0

    ' If a mail item is selected
    If Not objMail Is Nothing Then
        Set objAttachments = objMail.Attachments

        ' Loop over the attachments and save them
        For i = 1 To objAttachments.Count
            Dim strFileName As String
            strFileName = objAttachments.Item(i).FileName
            
            strFileExt = Right(strFileName, Len(strFileName) - InStrRev(strFileName, ".") + 1) ' get the file extension with dot
            'build the new path with output folder path and record number and date and extension
            strFileFolder = strFileFolder & strTempFileName & strFileExt
            
            objAttachments.Item(i).SaveAsFile strFileFolder
            
            Me!HyperLinkOUT = "#" & strFileFolder & "#"
        Next i
    End If

    'Clean up
    Set objAttachments = Nothing
    Set objMail = Nothing
    Set objNamespace = Nothing
    Set objOutlook = Nothing
End Sub

This code will now attempt to handle files dropped from Outlook in addition to files from File Explorer. When a file is dropped from File Explorer, your existing code will run. When a file is dropped from Outlook, the newly added code will attempt to save the file to disk.

Remember to add a reference to the Microsoft Outlook Object Library by going to Tools -> References in the VBA editor.

Please note, this is a basic solution and may not cover all edge cases. You should further modify and expand this to suit your exact needs. Don't forget to handle errors and edge cases such as when Outlook isn't open, the selected item isn't a mail item, there are no attachments, or if the file can't be saved for some reason.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 04:55
Joined
Jul 9, 2003
Messages
16,418
This is a link to the chat GPT which has produced the above code:-


I think you should be able to ask more questions yourself. Let me know how you get on with it. I'd love to know if you can pick up a chat GPT that I have created and continue with working with it...
 

gojets1721

Registered User.
Local time
Yesterday, 20:55
Joined
Jun 11, 2019
Messages
430
I Asked Chat GPT:-

Uncle Gizmo
could you reformat both code blocks in to something i can cut and paste in to my form module?

ChatGPT
Sure, here is how you can incorporate the sample code into your existing Access VBA code. I'm including code to get the file name from Outlook, build a new path, and copy the file to a new location, similar to what your original code does:


Code:
'VBA
Private Sub txtHyperLinkIN_AfterUpdate()

    Dim objOutlook As Outlook.Application
    Dim objNamespace As Outlook.Namespace
    Dim objMail As Outlook.MailItem
    Dim objAttachments As Outlook.Attachments

    Dim strFileFolder As String
    Dim strOutPath As String
    Dim strReportNumber As String
    Dim strTempFileName As String
    Dim strFileExt As String
   
    strFileFolder = "\Assets\Attachments\"  'specify the output folder
    strReportNumber = Me.ID
    strTempFileName = strReportNumber & " - " & Format(Now, "yyyymmddhhmmss")
   
    ' Get a reference to Outlook
    Set objOutlook = CreateObject("Outlook.Application")
    Set objNamespace = objOutlook.GetNamespace("MAPI")

    ' Select a mail item
    On Error Resume Next
    Set objMail = objOutlook.ActiveExplorer.Selection.Item(1)
    On Error GoTo 0

    ' If a mail item is selected
    If Not objMail Is Nothing Then
        Set objAttachments = objMail.Attachments

        ' Loop over the attachments and save them
        For i = 1 To objAttachments.Count
            Dim strFileName As String
            strFileName = objAttachments.Item(i).FileName
           
            strFileExt = Right(strFileName, Len(strFileName) - InStrRev(strFileName, ".") + 1) ' get the file extension with dot
            'build the new path with output folder path and record number and date and extension
            strFileFolder = strFileFolder & strTempFileName & strFileExt
           
            objAttachments.Item(i).SaveAsFile strFileFolder
           
            Me!HyperLinkOUT = "#" & strFileFolder & "#"
        Next i
    End If

    'Clean up
    Set objAttachments = Nothing
    Set objMail = Nothing
    Set objNamespace = Nothing
    Set objOutlook = Nothing
End Sub

This code will now attempt to handle files dropped from Outlook in addition to files from File Explorer. When a file is dropped from File Explorer, your existing code will run. When a file is dropped from Outlook, the newly added code will attempt to save the file to disk.

Remember to add a reference to the Microsoft Outlook Object Library by going to Tools -> References in the VBA editor.

Please note, this is a basic solution and may not cover all edge cases. You should further modify and expand this to suit your exact needs. Don't forget to handle errors and edge cases such as when Outlook isn't open, the selected item isn't a mail item, there are no attachments, or if the file can't be saved for some reason.
THank you! So I tried this combined suggestion. The 'drag and drop' from file explorer still works but nothing happens when dragging over from Outlook. I can connect with ChatGPT to dive more into it unless you have any suggestions
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 04:55
Joined
Jul 9, 2003
Messages
16,418
unless you have any suggestions
None I'm afraid, it was all from Chat GPT.... If the link works for you, then I strongly recommend you ask questions within that chat GPT thread.

You need to give chatGPT a clear indication of what the fault is and I'm sure it will provide you with some new code . I also be interested to know if you can actually carry on a thread I started and have it work okay for you. So if it does work or even if it doesn't work, I'd be grateful if you would let me know ....

BTW, did you set the references as instructed by chat GTP?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 04:55
Joined
Jul 9, 2003
Messages
16,418
Maybe try the separate code segments individually and see if they work ok. It might give you an idea where the issue lies...
 

gojets1721

Registered User.
Local time
Yesterday, 20:55
Joined
Jun 11, 2019
Messages
430
Maybe try the separate code segments individually and see if they work ok. It might give you an idea where the issue lies...
I was able to figure it out. The code is written as if it were a command, rather than a drag and drop situation, which honestly works better. Thanks so much. This has been eye opening to me in terms of how useful ChatGPT can be! Thanks again!
 

Users who are viewing this thread

Top Bottom