469,353 Members | 2,282 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,353 developers. It's quick & easy.

Saving attachment location to Access table - filepath code?

I have some code which saves emails into a database, creates new folders for new senders etc etc but what I now want to do is when a new email comes in, i want any attachments to be saved into a table (by saving the location and then having the ability to go back in and open it from that location)... All I really need is the code which will show the filepath... does anybody know how to do this? Any replies appreciated.
Dec 6 '10 #1

✓ answered by jimatqsi

Rebecca,
NeoPa is usually so much on the mark that none of us spent any time on this thread once we saw he was on the case. We thought he'd wrap it up in a jiffy. But, even Superman has to look out for kryptonite once in a while. :)

I found a thread that might be helpful. It offers another way to get what you want, I think ... it demonstrates how to read through your emails and save the attachments where you want to put them. So, your code could do that and then it would know what to save in your attachments table.

This is a reminder that even if you have opened the email and saved the attachment somewhere, that's not where the email's attachment is. That's where the copy of the attached file is. The email attachment itself is still (I guess) in the Outlook.pst file with the rest of the email.

Here's some of the code from the thread:
Expand|Select|Wrap|Line Numbers
  1. Set myfolder = _
  2. myNameSpace.GetDefaultFolder(olFolderInbox)
  3. For Each myItem In myfolder.Items
  4. If myItem.Subject = strSubject And myItem.UnRead Then
  5. With myItem
  6. Set objAttachments = myItem.Attachments
  7. For Each Attachment In objAttachments
  8. strFile = "C:\Saved Files\" & Attachment.DisplayName
  9. Attachment.SaveAsFile (strFile)
  10. strSaved = "Saved to " & strFile & " on " & Now()
  11. Next
The whole thread can be found at http://bytes.com/topic/access/answer...attached-email

What might also be helpful to you is to know you can use the ENVIRON function to get the user's working path for documents. This thread helps with that:
http://bytes.com/topic/access/answer...ws-id-username

Hope this helps.

Jim

40 7195
NeoPa
32,184 Expert Mod 16PB
Rebecca, we don't provide code. We answer questions, which in this case is next to impossible as you include so little information in your question. What are you working with? What do you have already that we can even start with?
Dec 6 '10 #2
Well people often help me with code when I can't find it/work it out myself. The reason why I didn't put any code was because I thought it was quite straight forwards. I did say I have already written some code for outlook automation. Here is an extract from my code as the entire module is quite long. As I said, I want to also append the table with the attachment path.
Expand|Select|Wrap|Line Numbers
  1. For Each MailObject In InboxItems
  2. Set rstSupportRequest = db.OpenRecordset("tbl002_SupportRequest")
  3.  
  4.                 rstSupportRequest.AddNew
  5.                 strRequestID = rstSupportRequest!RequestID
  6.                 rstSupportRequest!emailaddress = MailObject.SenderEmailAddress
  7.                 rstSupportRequest!Subject = MailObject.Subject
  8.                 rstSupportRequest!Request = MailObject.Body
  9.                 Debug.Print MailObject.Body
  10.                 rstSupportRequest!RequestDate = MailObject.ReceivedTime
  11.                 Debug.Print strRequestID 
Thank you.
Dec 6 '10 #3
NeoPa
32,184 Expert Mod 16PB
Rebecca McCallan:
Well people often help me with code when I can't find it/work it out myself.
An expert, at their discretion, can supply code where they feel it is helpful to illustrate a point they are trying to make. They can also work with an OP to produce code when they are comfortable that the OP is not simply asking for it to be done for them. We don't encourage our experts to give code solutions without any such indication, as it leaves members with the mistaken understanding that we are here simply to provide solutions for them - which is not our interest. We aim to help people understand and learn.

Clearly, from your last post, you have some code already and are well into this project, so I'm happy to treat you as someone who I will be working with in this case, but please bear this in mind in future questions. Just something in the question to make it obvious you're not simply asking for your work to be done for you, will help things go more smoothly.

As I say, your post satisfies me on that point. What is still missing, and absolutely necessary for me to have a good enough understanding of what the working environment is, is the answer to my second question - What do you have already that we can even start with?

I'm looking for something that indicates what the filepath is, or how it's worked out from your program's perspective. Maybe related to MailObject. I assume you also have a field name for where the path is stored within rstSupportRequest?

I suspect the solution will be more of an Outlook related one than an Access one, so I may have to do some digging for you as I'm no Outlook expert, but we'll give it a try when you provide the requested info.
Dec 6 '10 #4
I want to store the filepath in tbl003_Attachments in the field 'AttachmentLocation' which is linked to rstSupportRequest (tbl002_SupportRequest) by the RequestID stored in the attachments table. I have found that all email attachements are kept in the file location 'C:\Users\mccallanr\AppData\Roaming\Microsoft\Word \' So basically I need to refer to this location (ofcourse pulling in the file name aswell to complete the path) Cheers for the reply.
Dec 6 '10 #5
NeoPa
32,184 Expert Mod 16PB
I presume you can handle the adding of data to the relevant table then Rebecca (If not, let me know - but I may want more detail from you). As for the path of the attachment, you may know what the folder is, but unless we can get the project to determine this for itself, you won't have a workable solution. You and I may well know it's always stored in 'C:\Users\mccallanr\AppData\Roaming\Microsoft\Word \', but judging by the fact that your ID is embedded within that, I doubt this will be true for other users even on the same PC let alone others.

What I need from you is your code reference to the object itself, and the name of the class of that object. With that I can look at finding code which will return the actual path used without hard-coding in any assumptions about where it should be stored. Without this we don't even have the name of the file - even if we may know the folder that may be used for it. Does that make sense?
Dec 6 '10 #6
I think so, when you say my code reference to the object do you mean for the MailObject? There are quite a lot of objects defined but I will copy them all in to ensure you don't miss anything. Below the dim's I set the database and set up outlook

Expand|Select|Wrap|Line Numbers
  1. Dim outNamespace 'As Outlook.NameSpace
  2. Dim outOutlook 'As Outlook.Application
  3. Dim MailObject 'As Outlook.MailItem
  4. Dim outItem 'As Outlook.MailItem
  5. Dim Atmt As Attachment
  6. Dim outFolder As MAPIFolder
  7. Dim outFoldersUnauthorised As MAPIFolder
  8. Dim outFoldersActioned As MAPIFolder
  9. Dim outFoldersNewSender As MAPIFolder
  10. Dim outFoldersClient As MAPIFolder
  11. Dim InboxItems As Outlook.Items
  12. Dim intCount As Integer
  13. Dim intMove As Integer
  14. Dim strAnswer As String
  15. Dim intAnswer As Integer
  16. Dim strField As String
  17. Dim rstFolders As dao.Recordset
  18. Dim rstAdmin As dao.Recordset
  19. Dim intID As Integer
  20. Dim strRequestID As String
  21. Dim intContactID As Integer
  22. Dim rstData As dao.Recordset
  23. Dim rstUserData As dao.Recordset
  24. Dim rstPathData As dao.Recordset
  25. Dim db As Database
  26. Dim strEmailAddress As String
  27. Dim rstContactCheck As dao.Recordset
  28. Dim rstSupportRequest As dao.Recordset
  29. Dim intunauthorised As Integer
  30. Dim intnewsender As Integer
  31. Dim intuncount As Integer
  32. Dim intnewcount As Integer
  33. Dim strClientEmail As String
  34. Dim strProBaseEmail As String
  35. Dim rstClient As dao.Recordset
  36. Dim rstClientEmail As dao.Recordset
  37. Dim strHostName As String
  38. Dim strClientHost As String
  39. Dim strFolderName As String
  40. Dim rstAssignee As dao.Recordset
  41. Dim intposition As Integer
  42. Dim strContactName As String
  43. Dim rstAuthorised As String
  44. Dim strsubjectyes As String
  45. Dim strsubjectno As String
  46. Dim myInbox As Outlook.MAPIFolder
  47. Dim myDestFolder As Outlook.MAPIFolder
  48. Dim outItems As Outlook.Items
  49. Dim rstAttachments As dao.Recordset
  50. Dim intAttachmentID As Integer
  51.  
  52.  
  53. Set db = CurrentDb
  54.     Set outOutlook = CreateObject("Outlook.Application")
  55.     Set outNamespace = Outlook.GetNamespace("MAPI")
  56.     Set outFolder = Outlook.GetNamespace("Mapi").GetDefaultFolder(olFolderInbox)
  57.     Set InboxItems = outFolder.Items
  58.  
I did understand why I can't use that path but because you said 'something that indicates what the filepath is' I assumed you may be able to work with this information.

Cheers!
Dec 6 '10 #7
NeoPa
32,184 Expert Mod 16PB
Rebecca McCallan:
when you say my code reference to the object do you mean for the MailObject?
Not exactly, no. I mean the object that is the attachment itself. I imagine it is an Attachment object of some sort, and somewhere in its lineage (Parent; Parent of Parent; etc) is almost certainly MailObject. How one would refer to an attachment item from there though, is not something I know.

However, we are not without any progress. i was able to discover that an Outlook.Attachment object has properties that include .FileName as well as .PathName. I suspect the latter is what you're after. I can't put any code around it yet as I don't have the link from your code yet, but it seems this is the crux of the matter.

On a separate point, just some general advice, it is always a good idea to declare things as directly as possible. Line #5 for instance, should be :
Expand|Select|Wrap|Line Numbers
  1. Dim Atmt As Outlook.Attachment
There is useful information in the extra qualification and it avoids ambiguity should you ever add another reference with similarly named object classes.
Dec 6 '10 #8
I did actually find the properties .FileName and .PathName but they didn't seem to work no matter what I did with them... I'm not sure how to help define the attachment object... I think that's part of the problem I am having myself!

Cheers.
Dec 7 '10 #9
NeoPa
32,184 Expert Mod 16PB
Rebecca McCallan:
I'm not sure how to help define the attachment object... I think that's part of the problem I am having myself!
In that case you've caught me on a weak spot. Our network security was such that sending emails by code was always blocked, so I never really got to play much with Outlook. A shame really as it sounds quite interesting. I only have myself to blame though, as I set up the network security :-(

Anyway, I may not know much about it but I'll have a dig and see what I can find within the Outlook.MailItem object that can throw some light onto how attachments are linked.
Dec 7 '10 #10
NeoPa
32,184 Expert Mod 16PB
By the way, if you can post a copy of the database in 2003 or earlier version that may help me. Below are some general instructions. Ignore any that are obviously unrelated to this situation.

When attaching your work please follow the following steps first :
  1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
  2. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
  3. If the process depends on any linked tables then make local copies in your database to replace the linked tables.
  4. If the database includes any code, ensure that all modules are set to Option Explicit (See Require Variable Declaration).
  5. If you've done anything in steps 1 to 4 then make sure that the problem you're experiencing is still evident in the updated version.
  6. Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
  7. Compact the database (Tools / Database Utilities / Compact and Repair Database...).
  8. Compress the database into a ZIP file.
  9. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.
It's also a good idea to include some instructions that enable us to find the issue you'd like help with. Maybe some instructions of what to select, click on, enter etc that ensures we'll see what you see and have the same problems.
Dec 7 '10 #11
NeoPa
32,184 Expert Mod 16PB
Actually, it seems simple enough (tell me if it's not). The MailItem class (of which your MailObject is an object/instance) has a collection, called Attachments, which maintains a collection of Attachment objects. As we already know, these objects contain PathName and FileName properties.

Unless I'm mistaken then, Bob is your proverbial uncle :-)

PS. Don't bother with attaching the database then, if this resolves your issues. I was intending to use it to drill down from the MailObject object and see what I could find. That's probably not necessary any more as I found it using Object Explorer instead (See Debugging in VBA).
Dec 7 '10 #12
I have actually looked in the object explorer myself and tried to use the PathName property but it just doesn't seem to work...
Dec 8 '10 #13
NeoPa
32,184 Expert Mod 16PB
Rebecca McCallan:
but it just doesn't seem to work...
Unfortunately, within that there are untold possibilities of what you could mean. Do you have anything more for me perhaps? Some details?

On a separate point, may I know which of the various Farnborough's you're from. That could be not a million miles away from me (Not back-yard or anything - but not a plane-ride either). Feel free to skip this if you prefer to keep things ambiguous. I respect that, but I'm just curious.
Dec 8 '10 #14
In hampshire so not very far.
Basically, when I try and use Attachment.PathName it returns a blank string...=[
Dec 9 '10 #15
NeoPa
32,184 Expert Mod 16PB
Could you post the code you're referring to Rebecca. You may mean the [Attachment Variable].PathName, but you posted Attachment.PathName. If you're actually tryng to use that as your code you'd have a problem as Attachment is a class and not an object. It's hard to say what might be wrong without seeing a bit of your code.
Dec 9 '10 #16
Expand|Select|Wrap|Line Numbers
  1. For Each Atmt In MailObject
  2. Set rstAttachments = db.OpenRecordset("tbl003_Attachments")
  3. rstAttachments.AddNew
  4. intAttachmentID = rstAttachments!AttachmentID
  5. rstAttachments!RequestID = strRequestID
  6. rstAttachments!AttachmentLocation = Atmt.PathName
  7. rstAttachments!createduserstamp = gstrFullName
  8. rstAttachments!createddatestamp = Now
  9. Debug.Print strRequestID
  10. Next
Cheers.
Dec 10 '10 #17
NeoPa
32,184 Expert Mod 16PB
This code seems to be about creating an attachment to add to an email item. I thought you wanted to determine the path of a pre-existing attachment item. As this code doesn't include any reference to .FullPath in it anywhere either, I'm struggling to see where it fits into the conversation.

I'm sure this is proving complicated for you, and I'd like to help, but I'm still struggling to understand where you're coming from, so it's hard for me to know how to. If you go back to your post #15 it says :
Rebecca McCallan:
Basically, when I try and use Attachment.PathName it returns a blank string...=[
Where is that? I need some context to work (or think) within. Without the context it's impossible for me to help you.

PS. Forget this post. I just reread back over the thread and realised I'd made the mistake that got me confused when I responded to your earlier post. Give me some time to reread things and see where I really should be at. At first glance, my property name understandings were all confused, but I think the fact that the code is creating a new attachment will prove important.
Dec 11 '10 #18
NeoPa
32,184 Expert Mod 16PB
Right. I think I'm back on track now (Ironic as I've just come in from an evening out, which included some alcoholic beverages, whereas when I posted my earlier rubbish I was stone cold sober).

The .AddNew is not a problem after all. That's another thing I completely misinterpreted (I'm really not happy with the number of errors I've made already on this thread). In fact, I see no reason why the code should behave as you've described.

Ah, I think I may be seeing some light at the end of the tunnel now. I think line #6 is fine, but line #1 is treating the MailItem object (MailObject) as an attachment collection, when really it is simply an object that contains such a collection called Attachments. Try the following for line #1 and see if that helps any :
Expand|Select|Wrap|Line Numbers
  1. For Each Atmt In MailObject.Attachments
I truly hope it does, as my embarrassment level needs reducing somewhat, and I'm relying on this to do that for me - at least a little.
Dec 11 '10 #19
Hmmm.... The code I posted to you appends rstAttachments with the details from the email and in these details I want to include the attachment path (Just wanted to clear that up but I think you've got it now!) Secondly, I'm not sure where .FullPath came into this, you did mention it earlier but I did think you were referring to PathName? So I'm not sure what I should do with this. Anyway, I changed line 1 to 'For Each Atmt In MailObject.Attachments' and though Atmt.PathName still returned an empty string, on hovering over 'Atmt' it returned the FileNAME... which is a start as it wasn't even doing that before! I can't work out why the PathName is returning an empty string because I have made sure the emails in my inbox have Attachments and surely if they didn't have attachments the 'For' would just be skipped anyway so for it to enter the loop there must be attachments present... Hmmmm.... =[ Cheers.
Dec 13 '10 #20
NeoPa
32,184 Expert Mod 16PB
Rebecca McCallan:
I'm not sure where .FullPath came into this,
That's easy! It came from in my head somewhare. I was getting things well and truly mixed up earlier. It's not an area I'm too familiar with I'm afraid.

I checked the help file for the PathName property, and it says :
Returns a String representing the full path to the linked attached file. This property is only valid for linked files. Read-only.
I assume from this that your attachment isn't what is termed a linked attached file, though I can't honestly say what that would be if different from a standard attachment (I would expect any attachment to be such, but who knows).

The help for the FileName property however, is :
Returns a String representing the file name of the attachment. Use this property in conjunction with the PathName property. Read-only.
This indicates perhaps that the PathName is required after all. Somewhat confusing after what you report. Is there any chance that the mail item you're dealing with is somehow still in the process of being created? I can only imagine these properties may not be set until after the MailItem has been saved away. I'm scrabbling in the dark somewhat, to be fair. I haven't anything I can reproduce this on so can't look at anything in detail or do any meaningful tests for you.

I suggest you look at some of the properties that are available when you have the attachment object available to you in the code, and maybe look into testing first with older items, if you're not already.
Dec 13 '10 #21
I also read something about a 'linked file' but I'm not sure what that means... I have been testing this on normal emails - my daily emails in my inbox, not 'test' emails so they are just normal emails with normal attachments so I can't work out why the pathname propery won't work =/
Dec 13 '10 #22
NeoPa
32,184 Expert Mod 16PB
I'm sorry I'm not being much help Rebecca. I assume you're working with Outlook items. Did you find all the other properties had the expected values in them?
Dec 13 '10 #23
Such as the message body and sender email etc? Yes, I have it all working and when I went on to add in the attachment location it didn't work as I thought it should =[
Dec 13 '10 #24
NeoPa
32,184 Expert Mod 16PB
That's good to know, but I should have made the question clearer. I was asking about the properties of the attachment object specifically. For instance, the .FileName and .DisplayName?
Dec 13 '10 #25
Oh right sorry, yeah I've just tested that .FileName and .DisplayName both return the same value (the filename).
Dec 14 '10 #26
NeoPa
32,184 Expert Mod 16PB
Frankly I'm out of ideas. This sort of thing works best when I'm working from experience, or I have something to work with (that I can run various tests on as ideas occur). Working remotely on a setup I'm unfamiliar with gives me none of those advantages, so I will continue to monitor the thread in case anything comes up (either to enlighten or that I can contribute to) but I can't offer any further ideas at this time. Sorry. I just don't know why that particular property is not being populated. There is nothing to indicate why in the Help.
Dec 15 '10 #27
Ohh, ok well thanks very much for all your time and effort anyway!
Dec 15 '10 #28
NeoPa
32,184 Expert Mod 16PB
If you ever wanted to drop your project in here for me to look at (See post #11) I may be able to make more progress. Otherwise, I'll try to remember this thread if ever I come across a solution.

Actually, I do have another option. I'll post a link to here in the experts' area and see if anyone else can throw any more light on the subject.
Dec 15 '10 #29
jimatqsi
1,260 Expert 1GB
Rebecca,
NeoPa is usually so much on the mark that none of us spent any time on this thread once we saw he was on the case. We thought he'd wrap it up in a jiffy. But, even Superman has to look out for kryptonite once in a while. :)

I found a thread that might be helpful. It offers another way to get what you want, I think ... it demonstrates how to read through your emails and save the attachments where you want to put them. So, your code could do that and then it would know what to save in your attachments table.

This is a reminder that even if you have opened the email and saved the attachment somewhere, that's not where the email's attachment is. That's where the copy of the attached file is. The email attachment itself is still (I guess) in the Outlook.pst file with the rest of the email.

Here's some of the code from the thread:
Expand|Select|Wrap|Line Numbers
  1. Set myfolder = _
  2. myNameSpace.GetDefaultFolder(olFolderInbox)
  3. For Each myItem In myfolder.Items
  4. If myItem.Subject = strSubject And myItem.UnRead Then
  5. With myItem
  6. Set objAttachments = myItem.Attachments
  7. For Each Attachment In objAttachments
  8. strFile = "C:\Saved Files\" & Attachment.DisplayName
  9. Attachment.SaveAsFile (strFile)
  10. strSaved = "Saved to " & strFile & " on " & Now()
  11. Next
The whole thread can be found at http://bytes.com/topic/access/answer...attached-email

What might also be helpful to you is to know you can use the ENVIRON function to get the user's working path for documents. This thread helps with that:
http://bytes.com/topic/access/answer...ws-id-username

Hope this helps.

Jim
Dec 16 '10 #30
NeoPa
32,184 Expert Mod 16PB
Thank you Jim. Both for the helpful post and the kind remarks.
Dec 16 '10 #31
Brilliant! Though I didn't actually use much of that code, the idea was great; rather than finding the path saving rhe attachment elsewhere. FYI this is the code I used:
Expand|Select|Wrap|Line Numbers
  1.  For Each Atmt In MailObject.Attachments
  2.     'If myItem.Subject = strSubject And myItem.UnRead Then
  3.     strFile = "C:\Users\mccallanr\Documents\Client Support Database\Test\NEW EMAIL MOD\Email Attachments\" & Atmt.DisplayName
  4.     Atmt.SaveAsFile (strFile)
  5.     strSaved = "Saved to " & strFile & " on " & Now()
  6. Next
  7.  
  8. Set rstAttachments = db.OpenRecordset("tbl003_Attachments")
  9.     rstAttachments.AddNew
  10.     intAttachmentID = rstAttachments!AttachmentID
  11.     rstAttachments!RequestID = strRequestID
  12.     rstAttachments!AttachmentLocation = strSaved
  13.     rstAttachments!createduserstamp = gstrFullName
  14.     rstAttachments!createddatestamp = Now
  15. Debug.Print strRequestID 
Dec 17 '10 #32
Ahh, there is just one problem with this... On testing it on an email with no attachment, it saves any pictures in the attachments folder too! This is quite annoying as some of our clients have pictures in their signatures etc so we would have the same picture over and over again in the attachments folder... I can't see any way of avoiding this... can either of you think of anything which could maybe only let it save when something is actually an attachment?

Thanks for all your help by the way!
Dec 17 '10 #33
Hmmm, should have tested it a bit further before asking for more help really! I thought it would save with copies as for example attachment.doc (1), (2) etc, but it doesn't so thats not too much of a problem! Thanks guys!
Dec 17 '10 #34
NeoPa
32,184 Expert Mod 16PB
Rebecca, if your code is doing the saving, then it should always be in complete control of where it saves to. If your code to save the pictures is using a folder address that you don't wish it to, then simply change the code to do it differently.
Dec 18 '10 #35
Hmm perhaps I didn't explain that too well... I don't want it to save the pictures, it seems to be thinking they are attachments...

Cheers.
Dec 20 '10 #36
NeoPa
32,184 Expert Mod 16PB
Does that mean you're happy? Or do you still need further assistance?
Dec 20 '10 #37
Sorry for the very delayed reply.
I am partially happy, let me try and explain again.
Some of my emails include pictures within the emails (Not attachments) these may be for example part of a client's signature, the code is saving these images as if they were attachments and I don't want it to do that...

Thank You & Happy New Year!
Jan 6 '11 #38
NeoPa
32,184 Expert Mod 16PB
Aaaah. This is veering into unknown territory for me again I'm afraid.

I do know that when sending and receiving such emails some systems can handle embedded pictures while others can't (don't). It may be due to the format (The three standard formats which are supported within Outlook are HTML, RTF & Plain Text). I would imagine an HTML format email may well contain embedded pictures (not as attachments but embedded), while Plain Text could not. In such a case (where one is sent from somewhere using HTML and to somewhere that uses Plain Text) I would expect the embedded picture to be converted to an attached picture instead. This may or may not be the root of your problem, but it's about all the help I can give on this. It may be an understanding that will at least enable you to look more profitably for the problem. Good luck.
Jan 6 '11 #39
Ok well thank you for that, it has helped me to make a start on finding the answer.

Thanks for all your help
Jan 10 '11 #40
NeoPa
32,184 Expert Mod 16PB
You're very welcome Rebecca :-)

I hope it all goes well for you.
Jan 10 '11 #41

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by =?Utf-8?B?Q2hyaXM=?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.