just started using access, I am hoping to get much better at it. I have tried searching the forum for an answer but either because I am a noob or it may not be there, I cannot seem to find an answer, just alot of dead links.
I have been able to extract data from outlook using new data source > outlook folder >...
One of the fields generated is "Has Attachments" but it does not list the attachment name.
Is there anyway to get Access to display the names of the various attachments, and if possible provide a hyperlink to the attachment? I have been able to download all the attachments into a local drive. If I could connect the attachment file( in the local drive) to the database that would be sweet.
Here is something I knocked up in Outlook to save the attachments and replace with links. Garnered from the internet.
Perhaps you can adapt.?
Code:
Public Sub ReplaceAttachmentsToLink()
Dim objApp As Outlook.Application
Dim aMail As Outlook.MailItem 'Object
Dim oAttachments As Outlook.Attachments
Dim oSelection As Outlook.Selection
Dim oPA As PropertyAccessor
Dim i As Long
Dim iCount As Long
Dim sFile As String
Dim sFolderPath As String
Dim sDeletedFiles As String
Dim sDate As String, sTime As String
Const PR_ATTACHMENT_HIDDEN = "http://schemas.microsoft.com/mapi/proptag/0x7FFE000B"
' Get the path to your My Documents folder
sFolderPath = CreateObject("WScript.Shell").SpecialFolders(16)
On Error Resume Next
' Instantiate an Outlook Application object.
Set objApp = CreateObject("Outlook.Application")
' Get the collection of selected objects.
Set oSelection = objApp.ActiveExplorer.Selection
' Set the Attachment folder.
sFolderPath = sFolderPath & "\OLAttachments"
'If folder does not exist create it
If Dir(sFolderPath, vbDirectory) = "" Then
MkDir sFolderPath
End If
' Check each selected item for attachments. If attachments exist,
' save them to the Temp folder and strip them from the item.
For Each aMail In oSelection
' This code only strips attachments from mail items.
' If aMail.class=olMail Then
' Get the Attachments collection of the item.
Set oAttachments = aMail.Attachments
iCount = oAttachments.Count
If iCount > 0 Then
' We need to use a count down loop for removing items
' from a collection. Otherwise, the loop counter gets
' confused and only every other item is removed.
For i = iCount To 1 Step -1
Set oPA = oAttachments.Item(i).PropertyAccessor
If oPA.GetProperty(PR_ATTACHMENT_HIDDEN) = False Then
' Save attachment before deleting from item.
' Get the file name.
sFile = oAttachments.Item(i).fileName
'Now get Date & Time as strings to use in filename, but use received date & time
sDate = Format(aMail.ReceivedTime, "yyyymmdd")
sTime = Format(aMail.ReceivedTime, "hhmmss")
' Combine with the path to the Temp folder.
sFile = sFolderPath & "\" & sDate & "_" & sTime & "_" & sFile
' Save the attachment as a file.
oAttachments.Item(i).SaveAsFile sFile
' Delete the attachment.
oAttachments.Item(i).Delete
'write the save as path to a string to add to the message
'check for html and use html tags in link
If aMail.BodyFormat <> olFormatHTML Then
sDeletedFiles = sDeletedFiles & vbCrLf & "<file://" & sFile & ">"
Else
sDeletedFiles = sDeletedFiles & "<br>" & "<a href='file://" & _
sFile & "'>" & sFile & "</a>"
End If
End If
Next i
' Adds the filename string to the message body and save it
' Check for HTML body
If aMail.BodyFormat <> olFormatHTML Then
aMail.Body = aMail.Body & vbCrLf & _
"The file(s) were saved to " & sDeletedFiles
Else
aMail.HTMLBody = aMail.HTMLBody & "<p>" & _
"The file(s) were saved to " & sDeletedFiles & "</p>"
End If
aMail.Save
'sets the attachment path to nothing before it moves on to the next message.
sDeletedFiles = ""
End If
Next 'end aMail
ExitSub:
Set oAttachments = Nothing
Set aMail = Nothing
Set oSelection = Nothing
Set objApp = Nothing
End Sub
I have been able to extract data from outlook using new data source > outlook folder >...
One of the fields generated is "Has Attachments" but it does not list the attachment name.
Is there anyway to get Access to display the names of the various attachments, and if possible provide a hyperlink to the attachment? I have been able to download all the attachments into a local drive. If I could connect the attachment file( in the local drive) to the database that would be sweet.
Based on the data that you get from the "source > outlook folder" extraction process, I don't think u can adopt Gasman's code to do it, cuz his stuff is copying the attachment files from the email messages to a new directory. My guess is that you would have to write VBA code in an Access module to open an instance of Outlook, loop thru all the messages in the inbox comparing the subject lines and sender name for each message to what you have extracted, and get the attachment path property from the email object (I'm not even sure that "path" is a property of an email object in outlook's VBA object model). R u wanting someone to provide u a sample VBA script to do this?
Thank you for helping. Its a real lifesaver and its super cool of you to help a stranger.
Basically the project given to me was to extract an outlook folder (30 000+) emails and put them all onto an excel. however exporting a CSV from outlook doesnt give me the exact time, just the date.
So now i am using Access, which is great because it gives me the exact time. Not sure about the difference btween date recieved, date created and date modified, but i guess i'll find out later.
Now this is the problem, placing 30 000+ hyperlinks individually assuming 2mins per link, should take me 125days. which is no bueno. So I was hoping for an automated way to extract the file name and create a hyperlink to a local drive, that way my boss can click on the link and it opens up the attachment. I am not very technically savvy so I am unsure if i would recognize the answer even if it was placed in front of me. I guess what I am saying is that what would you recommend I do.
Once again thank you for your help. It means alot.
Well if you used my code, you'd get somewhere near.?
My code will take the attachments in selected emails and replace with links and save the attachments in the folder.
If after running this, you import the emails into Access, I would hope the links would still work?
Send a few emails with attachments to yourself and try it out on those.
I ran the script and it removed the attachments from the inbox. But when i extracted the inbox onto access, it does not have the link.
I extracted the file by using access>external data> new data source> from other sources > outlook folder. and followed the wizard to extract the inbox. Did i do it right?
I extracted the file by using access>external data> new data source> from other sources > outlook folder. and followed the wizard to extract the inbox. Did i do it right?
of course you did! but that's just the same thing you described doing in ur original post! would u like someone to provide a vba module that tries to capture these links ur wanting?
the reason no links came with your outlook data when you imported it is cuz the images are objects attached to the email messages. they have no links associated with them, at least as far as i know. it would be different if they were stored in the cloud, like on google drive or something.
I think it may be time to call it, Thank you in advance vba_PHP, would you be able to provide me a VBA module? I am so sorry i wasnt able to get the other thing to work.
I think it may be time to call it, Thank you in advance vba_PHP, would you be able to provide me a VBA module? I am so sorry i wasnt able to get the other thing to work.
I know it can be written, especially when using gasman's code, cuz getting local dir file addresses is a cinch in vba code. so worst case scenario, code would have to be written to throw the attachments into a local dir (gasman's code) + code to capture the extracted attachments' addresses (which is not tough). so give me a bit and I'll be back with you, that is of course unless someone else provides you a solution first!
this is really nothing different than gasman's code. all it does is save the "spath" to a hyperlink field in the table "test_table" after the code saves the actual attachment as a file in ur local directory. keep in mind that his code (which is what this code is based off of) is looping through the "personal folders" inbox in the outlook application. if you want to look at an inbox of a specific IMAP account that you synced, for instance a gmail account, the code has to point to that folder instead.
so the code doesn't error out on you, put a table in the access db, call it "test_table", then put a field in it, call it "path" and set the type to "hyperlink". then of course you can modify this to suit the table you already have setup. those coding additions shouldn't take any skill cuz ur just copying code lines that are already in what is below. note however that if ur inbox has many messages in it, this code is gonna take a while to run cuz it's got to perform 2 processes that aren't very fast: 1) save an attachment as a file, and 2) add a record to a table.
you said you already have all the attachments from ur inbox saved to ur local drive? if you do and you don't want to bother saving the attachments again using gasman's portion of this, just copy and paste Allen Browne's code from here to list the files in a directory. his code should be not be difficult for you to add a couple of lines that will copy the file paths returned by his functions and update a hyperlink field in your table with those file paths. forgoing gasman's portion and just performing allen's will speed up the process greatly:
Code:
Private Sub ReadInbox()
Dim TempRst As DAO.Recordset
Dim OlApp As Outlook.Application
Dim Inbox As Outlook.MAPIFolder
Dim InboxItems As Outlook.Items
Dim Mailobject As Object
Dim dealer As Integer
Dim oAttachments As Outlook.Attachments
Dim iCount As Long
Dim sFile As String
Dim sDate As String, sTime As String
Dim sFolderPath As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("test_table", dbOpenDynaset)
sFolderPath = CreateObject("WScript.Shell").SpecialFolders(16)
' Set the Attachment folder.
sFolderPath = sFolderPath & "\OLAttachments"
'If folder does not exist create it
If Dir(sFolderPath, vbDirectory) = "" Then
MkDir sFolderPath
End If
Set OlApp = CreateObject("Outlook.Application")
Set Inbox = OlApp.GetNamespace("Mapi").GetDefaultFolder(olFolderInbox)
'
Set InboxItems = Inbox.Items
'
For Each Mailobject In InboxItems
Set oAttachments = Mailobject.Attachments
iCount = oAttachments.Count
If iCount > 0 Then
For i = iCount To 1 Step -1
sFile = oAttachments.item(i).FileName
' Combine with the path to the Temp folder.
sFile = sFolderPath & "\" & sFile
' Save the attachment as a file.
oAttachments.item(i).SaveAsFile sFile
'save local link to the table
rs.AddNew
rs!Path = sFile
rs.Update
Next i
End If
Next
rs.Close
db.Close
Set db = Nothing
Set rs = Nothing
Set OlApp = Nothing
Set Inbox = Nothing
Set InboxItems = Nothing
Set Mailobject = Nothing
End Sub
if those hyperlinks don't actually open the file when you click on them, the alternative would be to store the "sPath" variable as text in a text field, then create a form with the file's path fieldname on it, and then write code to open the file behind the double click event of the field's textbox control or something like that.
I sometimes do it as well Micron, but rarely. The only time I do it is when I've gotten plenty of responses in the 1st thread but haven't gotten a solution.
Cross posting is allowed - probably just about everywhere. What ticks people off is when someone does that and doesn't post a link to the other thread - or at least have the courtesy to say what forum it's in. There's really not much excuse for neglecting that when it's in the rules of any forum that I've participated in. I seldom post the customary link about cross posting anymore because it's getting tiresome.
When it comes to declaring it, which camp are you in?
and get the attachment path property from the email object (I'm not even sure that "path" is a property of an email object in outlook's VBA object model).
the code I provided to the OP saves the attachment to the hard drive then writes that local file address to the access table field that is hyperlink data type. it makes no sense that an attachment in an outlook email message would have a path before the file is saved to the local hard drive.
the code I provided to the OP saves the attachment to the hard drive then writes that local file address to the access table field that is hyperlink data type. it makes no sense that an attachment in an outlook email message would have a path.
