Emails INTO Access

[wheather girl]

Peter please read line below...sorry haven't gotten back to you, life has
just been mad lately.

Wheather Girl,

I have now got the code to save the emails to disk from Outlook, it is
only a couple of lines of code. However, there are a few other things
to consider so I need to ask a few more questions.

Just to recap, you will receive (or send) an email where the Subject
line holds the Project code, plus a -R or -S and then the email
reference number. For example :- 08194-R1234 or 09003-R0002 (I'm
assuming the + sign that crept in on one of the numbers in your last
post was a typo).

Yes a typo
The 08194 or 09003 is the project code and will also

be the name of the folder into which all emails for that project will
be saved. yes

Q1. The two examples you gave have five digit numbers for the project
code followed by a dash, is this ALWAYS the case or is it possible
that, in the foreseeable future, you could have six digit project
codes (or even four digit codes)? I need to extract this value to
create the folder on disk and it is easier if they are always a fixed
length, a bit more code if they can be a variable length.

at this stage they are always a 5 digit code..not sure if it will change, to
6 will definately not change to 4, but 6 is possible in the forseeable future.

Q2. Are you likely to receive replies from your customers for an email
you sent which you would also want to save to disk. When a reply is
received from a previous email, Outlook adds RE: to the start of the
Subject line. Is this likely to happen?

Yes
If so then we would need to

strip of the prefix before saving the email.

NO, dont strip off that prefix, as I will add my project code ie 08194-R001
and that willl then be he prefix...but i do have to save these emails not
only in the project folder, but they have to go into a folder under the
project folder of who(the company) the email came from, so for example the
project is 08194, the email gets saved with a number like 08194-R001 and then
it needs to be delegated to the another folder for that company that the
email came from or was sent to...like Onesteel for example, so that email
will go into the project folder of 08194 then to the Onesteel folder as is
came from Onesteel or was sent from us to Onesteel. And so on and so on for
each company that dealings with that project, so Onesteel is to name a few,
we can have about 20 different companies having dealings with that one
project, so need to be able to put them into corresponding folder as
well...is that possible? one thing to get them into the project folder, but
might be another thing to get them into corresponding folders, as need to
assign each email at the time, from outlook regardless wheather it is
incoming of outgoing.

Q3. This then raises another question. What happens if you try to save
an email that has already been saved, either by trying to save an
already saved email again accidentally or by saving an email that has
been returned to you as a reply (and had the RE: bit removed). You
have several options here :-

NO wouldn't happen, cause I have to save it with a new number regardless if
it is the same email or someone has deleted the RE: from the emial....every
email gets assigned a new number.

(a) Overwrite the existing email with the new email (a dangerous
option unless you are sure it is the same email).

NO for the same reasons above..wouldn't happen.

(b) Save the email with a different filename, perhaps append an
incrementing number to the end of the filename automatically so, for
example, 08194-R1234.msg would become 08194-R1234+001.msg (or
whatever).

NO for same reasons as above...even when we get a reply from the email we
sent, we then reassign it a new number, the old number still remains in the
subject line for tracking, but it will be given a new number again, and again
and so on, untill that emial stops.

(c) Allow the user to change the filename and then save it to disk
under that new name. An iffy option unless the user knows what they
are doing, they could change the project code part of the filename or
the extension code which would mess the whole thing up somewhat.

NO our system does it automatically, so no it wont be messed up, the project
code will always remain the same, just the end bit ie, R or S then the number
0001, or what ever.

(d) Just not allow the email to be saved at all if it has the same
name as an already saved email. once again, same as above.
Let me know what you want the code to do under these circumstances.

same as above, it would n't happen

Q4. Where abouts on your hard disk are you storing these folders (I
assume that you have some already saved to disk). In other words, what
is the root directory of the project folders, this would be something
like :- C:\Projects\08194 or C:\Projects\09003 I will need that to
locate the project folders. Normally I would not 'hard code' this
pathname (that is enter a pathname into the code) because if you ever
changed it, you would need to rewrite the VBA code to get the project
working again. However, for the moment that will be the easiest
option, maybe when it is working we can add a .ini file on your hard
disk to store it (or perhaps store it in the Registry).

ok will have to write it down, cant remember off the top of my head..it
would be something like T:09003/documentcontroll/transmittals will have to
check the rest of that. but would also include /Onesteel for the
corresponding folder or /Tonkin just for example sake. and there would be
about 20 of these seperate folders that they would have to go into. not sure
if that is possible...your the expert

Q5. Is the project code ALWAYS a numerical value. I think it would be
advisable to include some checks in the code to ensure that the email
that the user is trying to save is a valid project email and not some
other email that is not related to any project. If the Subject line of
a valid project email always starts with a five digit number, I could
use that information to validate the email before saving it. I would
think that that would be good enough, it is probably rare to get
normal emails with that sort of Subject line. Yes always numerical.

To answer your other question, yes you can save ANY email in Outlook,
that is received, sent, deleted, etc. You can also highlight a group
of emails together and save them all in one operation.

There is just one other problem (at the moment). I only have Outlook
2003 so I can tell you how to do all this on that version. Outlook
2007, which I believe you are using, is very different. I have been
advised by the Outlook NG that this should work OK in Outlook 2007 but
I will not be able to give you a detailed procedure (which is fairly
complicated) on how to do it. If possible, I would suggest you
initially try this out on a PC that has Outlook 2003 installed.

Good Idea, will try to find it.
That

would give a chance to test it out and familiarise yourself with the
procedure. If not you will need to work out how to do on Outlook 2007
yourself. This Web site shows how to do it with Outlook 2003 :-
http://blogs.techrepublic.com.com/howdoi/?p=146
and this is how in Outlook 2007 :-
http://msdn.microsoft.com/en-us/library/dd229324.aspx
but don't try anything just yet.

Let me know the answers to these questions and I will post back the
procedure for Outlook 2003.

Thanks Peter, youv'e been very helpful

 

[Peter Hibbs]

Wheather Girl,

NO, dont strip off that prefix, as I will add my project code ie 08194-R001
and that willl then be he prefix...but i do have to save these emails not
only in the project folder, but they have to go into a folder under the
project folder of who(the company) the email came from, so for example the
project is 08194, the email gets saved with a number like 08194-R001 and then
it needs to be delegated to the another folder for that company that the
email came from or was sent to...like Onesteel for example, so that email
will go into the project folder of 08194 then to the Onesteel folder as is
came from Onesteel or was sent from us to Onesteel. And so on and so on for
each company that dealings with that project, so Onesteel is to name a few,
we can have about 20 different companies having dealings with that one
project, so need to be able to put them into corresponding folder as
well...is that possible? one thing to get them into the project folder, but
might be another thing to get them into corresponding folders, as need to
assign each email at the time, from outlook regardless wheather it is
incoming of outgoing.

Unfortunately, this does complicate things considerably. As I have
indicated before, it is relatively simple to create a folder based on
the Project Code numbers but I do not see any easy way we could then
create a sub-directory called 'Onesteel' or whatever, in that folder
since there is no way (that I can see based on your replies) of
determining the name of the company which is associated with each
email using VB code.

The simplest and logical method would have been to open a standard
File Selector dialog in which you could choose the company folder when
you save each email but after doing a bit more research, it appears
that this is not possible in Outlook (or not easily possible anyway).
It would probably be possible to design a new Outlook form which
showed a list of companies that you could choose and then use the
selected name to create the sub-folder but this one involve a lot more
programming on your part and I think that would be too complicated for
me to explain via this forum.

The only thing I can suggest (apart from hiring a local programmer to
do it for you) is to investigate an alternative method of saving the
email files. You mentioned earlier that to save an email you
drag-and-drop each email into a folder on your hard disk (which sounds
a bit messy to me). Have you considered using the File -> Save As
facility instead? You would need to change the Save as type: drop down
box to 'Outlook Message Format (*.msg)' before you save and you would
need to find the location on disk but I noticed that, once you have
done this, Outlook remembers the location so that you don't have to do
it every time. It may even be possible to set up these as default
options, I don't know. You might like to try the Outlook newsgroup to
see if they can make any suggestions.

Sorry to break the bad news but I can't think of anything else that
would make thos operation easier for you. If you have any further
suggestions then feel free to post back.

Peter Hibbs.

 

[wheather girl]

Peter

thanks for your help...yeah I thought it might be a long shot, but had to
check out this option....Once again thanks, I appreciate the effort you have
gone to.

tell me I do have query which is unrelated to this one...
In my database table, I have the Auto number field, and it displays as
1,2,3, and so on when you get to double digits it displays as 10,11,12 and so
on.. I want it to be an auto number so i dont have to keep filing it in, but
i want it to display 09003-T and then the auto number ex. 09003-T0001 and
then 09003-T0002 and so on.

NOw the auto number field does not allow me enter an input mask, but it does
allow you to enter a format for that field. So i typed in !"09003-T"00
which displays as 09003-T001 and for a double digit displays as 09003-T0012
which is good, which is what I want.. i want it to display a four digit
number after the T, but if I add in another 0 in the format box ie
!"09003-T"000 then it displays 09003-T0001 which is fine for this one, but
when i get to double digits or more i get a five digit number or more instead
of keeping to a four digit number ie 09003-T00012.

I cant put a field size of 4 in there, as an the auto number field wont let
me..is there a way manipulating this so it does display a four digit number
after the T in my project number, regardless of how big or small the number
gets ie 0001 or 0045 or 0356. I should n't need any more than four digits.

see how ya go with this one.!!!
thanks

 

[Dad]

Peter

thanks for your help...yeah I thought it might be a long shot, but had to
check out this option....Once again thanks, I appreciate the effort you
have
gone to.

tell me I do have query which is unrelated to this one...
In my database table, I have the Auto number field, and it displays as
1,2,3, and so on when you get to double digits it displays as 10,11,12 and
so
on.. I want it to be an auto number so i dont have to keep filing it in,
but
i want it to display 09003-T and then the auto number ex. 09003-T0001 and
then 09003-T0002 and so on.

NOw the auto number field does not allow me enter an input mask, but it
does
allow you to enter a format for that field. So i typed in !"09003-T"00
which displays as 09003-T001 and for a double digit displays as
09003-T0012
which is good, which is what I want.. i want it to display a four digit
number after the T, but if I add in another 0 in the format box ie
!"09003-T"000 then it displays 09003-T0001 which is fine for this one, but
when i get to double digits or more i get a five digit number or more
instead
of keeping to a four digit number ie 09003-T00012.

I cant put a field size of 4 in there, as an the auto number field wont
let
me..is there a way manipulating this so it does display a four digit
number
after the T in my project number, regardless of how big or small the
number
gets ie 0001 or 0045 or 0356. I should n't need any more than four
digits.

see how ya go with this one.!!!
thanks

 

[Peter Hibbs]

Hi Wheather Girl,

Firstly, as this is a new question you should really start a new
thread so that more people will see it. Anyway, since you ask :-

No, you can't use an AutoNumber field like this. AutoNumber fields
cannot be relied upon to generate sequential numbers and also, at some
time or other, you will get gaps in the numbers, for example, if a
record is abandoned after it has been created.

What you should do is create a new Number type field (Long Integer)
and enter the next number in sequence as you create each new record
(but keep the AutoNumber field for reference purposes). If the prefix
09003-T is ALWAYS the same then you don't need to do anything more.
If, on the other hand, you will have different prefixes (are these
still your project codes?) then you would probably want to store those
in a Text type field. Then, whenever you want to display the full
number to your users on a form or report or whatever, you would just
combine the two parts like this :-

"09003-T" & Format(YourNumberField,"0000")

I am assuming the prefix code is constant here. If the value of
YourNumberField is 46 for example, then the full code would look like
this :-

09003-T0046

To create the next number in the sequence for your new number field
you would use something like this :-

YourNumberField= Nz(DMax("YourNumberField", "tblYourTable"),0) + 1

This line of code just finds the highest value for your number field,
adds 1 to it and copies it back to the field. Where you put this code
will depend on how you are creating new records, possibly in the
Before_Update event of the form which is bound to the table.

If you need more detailed information I would suggest you start a new
thread which will then be seen by more experts.

Good luck.

Peter Hibbs.

 

[wheather girl]

Thanks Peter and all the best.

Hi Wheather Girl,

Firstly, as this is a new question you should really start a new
thread so that more people will see it. Anyway, since you ask :-

No, you can't use an AutoNumber field like this. AutoNumber fields
cannot be relied upon to generate sequential numbers and also, at some
time or other, you will get gaps in the numbers, for example, if a
record is abandoned after it has been created.

What you should do is create a new Number type field (Long Integer)
and enter the next number in sequence as you create each new record
(but keep the AutoNumber field for reference purposes). If the prefix
09003-T is ALWAYS the same then you don't need to do anything more.
If, on the other hand, you will have different prefixes (are these
still your project codes?) then you would probably want to store those
in a Text type field. Then, whenever you want to display the full
number to your users on a form or report or whatever, you would just
combine the two parts like this :-

"09003-T" & Format(YourNumberField,"0000")

I am assuming the prefix code is constant here. If the value of
YourNumberField is 46 for example, then the full code would look like
this :-

09003-T0046

To create the next number in the sequence for your new number field
you would use something like this :-

YourNumberField= Nz(DMax("YourNumberField", "tblYourTable"),0) + 1

This line of code just finds the highest value for your number field,
adds 1 to it and copies it back to the field. Where you put this code
will depend on how you are creating new records, possibly in the
Before_Update event of the form which is bound to the table.

If you need more detailed information I would suggest you start a new
thread which will then be seen by more experts.

Good luck.

Peter Hibbs.

 

[Agent 99]

Hello,

I'm using:
Access 2003 (11.8166.8221) SP3
Outlook 2003 (11.8217.8221) SP3

I found an odd behavior when using File | Get External Data | Link Tables...
| Outlook().

I selected my 'Sent Items' as the table input and it shows up nicely as a
table in my Access Database.

The Odd thing is this:
The "Received" field is the date that the email was sent (normal). Generally
the date is fine. What I noticed was that every time there is an email sent
on the last day of the month in the PM, the date is corrupted.

In one case, the "Received" date should have been 4/30/2009 7:19 PM (that's
what I see in the Outlook email message). When I look at the data in the
Access Table it shows up as 9/14/1618 1:25:36 AM. When I copy (Cntl-C) and
paste (Cntl-V) the data comes out as 1/1/1601.

All other dates are fine. There are 936 records in my file and 10 of them
are corrupted because they were received in the PM on the last day of the
month. It doesn't matter which month it's very consistently the last day of
the month in the PM. If the message is in the AM, the date is fine in both
Outlook and Access.

Has anyone else seen this? Is this a bug in Outlook or Access?

Any help or guidance would be appreciated.

Agent 99

 

[Agent 99]

Transferred to microsoft.public.access.externaldata as a question titled:
Corrupted Dates in Access Link Tables - Linking Access to Outlook(