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
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.
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.
A conversational AI system that listens, learns, and challenges
chat.openai.com
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...
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
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?
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!
