Saving attachment location to Access table - filepath code?

[Rebecca McCallan]

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.

[NeoPa]

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?

[Rebecca McCallan]

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.

For Each MailObject In InboxItems
Set rstSupportRequest = db.OpenRecordset("tbl002_SupportRequest")
 
                rstSupportRequest.AddNew
                strRequestID = rstSupportRequest!RequestID
                rstSupportRequest!emailaddress = MailObject.SenderEmailAddress
                rstSupportRequest!Subject = MailObject.Subject
                rstSupportRequest!Request = MailObject.Body
                Debug.Print MailObject.Body
                rstSupportRequest!RequestDate = MailObject.ReceivedTime
                Debug.Print strRequestID 

Thank you.

[NeoPa]

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.

[Rebecca McCallan]

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.

[NeoPa]

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?

[Rebecca McCallan]

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

Dim outNamespace 'As Outlook.NameSpace
Dim outOutlook 'As Outlook.Application
Dim MailObject 'As Outlook.MailItem
Dim outItem 'As Outlook.MailItem
Dim Atmt As Attachment
Dim outFolder As MAPIFolder
Dim outFoldersUnauthorised As MAPIFolder
Dim outFoldersActioned As MAPIFolder
Dim outFoldersNewSender As MAPIFolder
Dim outFoldersClient As MAPIFolder
Dim InboxItems As Outlook.Items
Dim intCount As Integer
Dim intMove As Integer
Dim strAnswer As String
Dim intAnswer As Integer
Dim strField As String
Dim rstFolders As dao.Recordset
Dim rstAdmin As dao.Recordset
Dim intID As Integer
Dim strRequestID As String
Dim intContactID As Integer
Dim rstData As dao.Recordset
Dim rstUserData As dao.Recordset
Dim rstPathData As dao.Recordset
Dim db As Database
Dim strEmailAddress As String
Dim rstContactCheck As dao.Recordset
Dim rstSupportRequest As dao.Recordset
Dim intunauthorised As Integer
Dim intnewsender As Integer
Dim intuncount As Integer
Dim intnewcount As Integer
Dim strClientEmail As String
Dim strProBaseEmail As String
Dim rstClient As dao.Recordset
Dim rstClientEmail As dao.Recordset
Dim strHostName As String
Dim strClientHost As String
Dim strFolderName As String
Dim rstAssignee As dao.Recordset
Dim intposition As Integer
Dim strContactName As String
Dim rstAuthorised As String
Dim strsubjectyes As String
Dim strsubjectno As String
Dim myInbox As Outlook.MAPIFolder
Dim myDestFolder As Outlook.MAPIFolder
Dim outItems As Outlook.Items
Dim rstAttachments As dao.Recordset
Dim intAttachmentID As Integer
 
 
Set db = CurrentDb
    Set outOutlook = CreateObject("Outlook.Application")
    Set outNamespace = Outlook.GetNamespace("MAPI")
    Set outFolder = Outlook.GetNamespace("Mapi").GetDefaultFolder(olFolderInbox)
    Set InboxItems = outFolder.Items
 

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!

[NeoPa]

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 :

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.

[Rebecca McCallan]

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.

[NeoPa]

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.

[NeoPa]

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.

[NeoPa]

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).

[Rebecca McCallan]

I have actually looked in the object explorer myself and tried to use the PathName property but it just doesn't seem to work...

[NeoPa]

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.

[Rebecca McCallan]

In hampshire so not very far.
Basically, when I try and use Attachment.PathName it returns a blank string...=[

[NeoPa]

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.

[Rebecca McCallan]

For Each Atmt In MailObject
Set rstAttachments = db.OpenRecordset("tbl003_Attachments")
rstAttachments.AddNew
intAttachmentID = rstAttachments!AttachmentID
rstAttachments!RequestID = strRequestID
rstAttachments!AttachmentLocation = Atmt.PathName
rstAttachments!createduserstamp = gstrFullName
rstAttachments!createddatestamp = Now
Debug.Print strRequestID
Next

Cheers.

[NeoPa]

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.

[NeoPa]

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 :

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.

[Rebecca McCallan]

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.

[NeoPa]

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.

[Rebecca McCallan]

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 =/

[NeoPa]

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?

[Rebecca McCallan]

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 =[

[NeoPa]

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?

[Rebecca McCallan]

Oh right sorry, yeah I've just tested that .FileName and .DisplayName both return the same value (the filename).

[NeoPa]

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.

[Rebecca McCallan]

Ohh, ok well thanks very much for all your time and effort anyway!

[NeoPa]

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.

[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:

Set myfolder = _
myNameSpace.GetDefaultFolder(olFolderInbox)
For Each myItem In myfolder.Items
If myItem.Subject = strSubject And myItem.UnRead Then
With myItem
Set objAttachments = myItem.Attachments
For Each Attachment In objAttachments
strFile = "C:\Saved Files\" & Attachment.DisplayName
Attachment.SaveAsFile (strFile)
strSaved = "Saved to " & strFile & " on " & Now()
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

[NeoPa]

Thank you Jim. Both for the helpful post and the kind remarks.

[Rebecca McCallan]

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:

 For Each Atmt In MailObject.Attachments
    'If myItem.Subject = strSubject And myItem.UnRead Then
    strFile = "C:\Users\mccallanr\Documents\Client Support Database\Test\NEW EMAIL MOD\Email Attachments\" & Atmt.DisplayName
    Atmt.SaveAsFile (strFile)
    strSaved = "Saved to " & strFile & " on " & Now()
Next
 
Set rstAttachments = db.OpenRecordset("tbl003_Attachments")
    rstAttachments.AddNew
    intAttachmentID = rstAttachments!AttachmentID
    rstAttachments!RequestID = strRequestID
    rstAttachments!AttachmentLocation = strSaved
    rstAttachments!createduserstamp = gstrFullName
    rstAttachments!createddatestamp = Now
Debug.Print strRequestID 

[Rebecca McCallan]

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!

[Rebecca McCallan]

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!

[NeoPa]

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.

[Rebecca McCallan]

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.

[NeoPa]

Does that mean you're happy? Or do you still need further assistance?

[Rebecca McCallan]

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!

[NeoPa]

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.

[Rebecca McCallan]

Ok well thank you for that, it has helped me to make a start on finding the answer.

Thanks for all your help

[NeoPa]

You're very welcome Rebecca :-)

I hope it all goes well for you.