Emails INTO Access

[Guest]

Dear All
I have developed a contact management process within my .mdb which writes
letters (through MS Word) and sends emails (through Outlook or Outlook
Express). However, users are unhappy because I can't get emails received in
Outlook into the appropriate database records without either 'file and save
as' in Outlook and then attach as a file in the db record or good old copy
and paste.

Is there a method to collect emails received in Outlook within the .mdb and
be able to recognise the sender(s), the subject, the content and so on? I.e.
can Access (2003) get hold of emails received in Outlook?

Many thanks
PJB

 

[Granny Spitz via AccessMonster.com]

Is there a method to collect emails received in Outlook within the .mdb and
be able to recognise the sender(s), the subject, the content and so on? I.e.
can Access (2003) get hold of emails received in Outlook?

Access 2003 can link to the Exchange server tables or Outlook pst files.
When linking choose Exchange() or Outlook() in the "Files of Type" combo box
in the "Link" window.

 

[Guest]

PJB

Try the code below. You will also need the Microsoft Outlook 11.0 Object
Library selected in the References list (or whatever version of Outlook you
are using).

Dim fdrInBox As Outlook.MAPIFolder
Dim MailItem As Outlook.MailItem
Dim vSubject As String, vBody As String, vSender As String, vSenderName As
String, vBCC As String
Dim vSentDate as Date

Set fdrInBox = GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
For Each MailItem In fdrInBox.Items
vSubject = MailItem.Subject
vSender = MailItem.SenderEMailAddress
vBody = MailItem.Body
vBCC = MailItem.BCC
vSenderName = MailItem.SenderName
vSentDate = MailItem.SentOn
Next
Set fdrInBox = Nothing
Set MailItem = Nothing

This code cycles through all the e-mails in your In Box and copies the
various fields to variables. In practice, of course, you would copy them to
an array or a table within the For-Next loop. You will probably also need to
filter out the particular e-mails that you are interested in based on the
Subject field (or whatever). Anyway this will give you a starting point if
this is what you are after.

Hope this helps.

 

[Guest]

Thank you

PJB

Try the code below. You will also need the Microsoft Outlook 11.0 Object
Library selected in the References list (or whatever version of Outlook you
are using).

Dim fdrInBox As Outlook.MAPIFolder
Dim MailItem As Outlook.MailItem
Dim vSubject As String, vBody As String, vSender As String, vSenderName As
String, vBCC As String
Dim vSentDate as Date

Set fdrInBox = GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
For Each MailItem In fdrInBox.Items
vSubject = MailItem.Subject
vSender = MailItem.SenderEMailAddress
vBody = MailItem.Body
vBCC = MailItem.BCC
vSenderName = MailItem.SenderName
vSentDate = MailItem.SentOn
Next
Set fdrInBox = Nothing
Set MailItem = Nothing

This code cycles through all the e-mails in your In Box and copies the
various fields to variables. In practice, of course, you would copy them to
an array or a table within the For-Next loop. You will probably also need to
filter out the particular e-mails that you are interested in based on the
Subject field (or whatever). Anyway this will give you a starting point if
this is what you are after.

Hope this helps.

 

[Guest]

Thank you

Access 2003 can link to the Exchange server tables or Outlook pst files.
When linking choose Exchange() or Outlook() in the "Files of Type" combo box
in the "Link" window.

 

[Granny Spitz via AccessMonster.com]

Thank you

You're welcome, PJ. Good luck on your project.

 

[wheather girl]

I need to attach emails from outlook into my access database...I read your
answer on it, but I am sorry I am afraid that I am not as advanced in this
area, and need a more step by step approach of how to do it. I am running
microsoft office 2007.

Could you please describe how to do this in a much simpler manner

thanks

 

[Peter Hibbs]

Hi Wheather Girl,

Possibly, but what exactly are you trying to do. What do you mean by
"attach emails from outlook into my access database", are you trying
to import some email information into a table in your database or
what? More details of what you want to do would help.

Peter Hibbs.

 

[z\\]

Hi Wheather Girl,

Possibly, but what exactly are you trying to do. What do you mean by
"attach emails from outlook into my access database", are you trying
to import some email information into a table in your database or
what? More details of what you want to do would help.

Peter Hibbs.

 

[wheather girl]

Peter ,

Yes I am trying to attach emails with or without attachments into my table
in my database, in the "attachment field" without having to save the emails
in a temp folder then attach in the table to the "attachment" field. Is
there a direct path that I can just go into outlook select the email that I
want and attach it? OR/AND if the emails that are sent and received always
stored in outllook (like they are for work)what is the path to outlook to
create a hyperlink for the email/s in the hyperlink field of the table in my
database.

Either way, I need to be able to either attach the email to my table or
hyperlink it to my table or both... I need a path to hyperlink, and a path to
attach the email/s. There must be a way of doing this.

Hope this is more clear of my intentions.

thanks

thanks

 

[Peter Hibbs]

Wheather Girl,

Well, not really.

Are you saying that you will receive some emails into Outlook, which
may or may not have attached files, and you want to copy those emails
into a table in Access. It will be easy enough to copy the Subject
line and the Body of the email into an Access field (although images
and HTML code would be a problem) using the code I provided but I
don't think you could store any email attachment files in a table
because these could be any type of file (and they could potentially be
very large). You could save any attached files to disk and then set up
a path to the file in a field in an Access table which would allow you
to view or open that file, if that is what you are trying to do.

Also, I'm not aware of any way to access an email directly where it is
stored on disk (you may need to post a question to the Outlook
Newsgroup for info on this).

What is the purpose of this exercise, what are you going to do with
the emails when you have them in an Access table? Are you trying to
store incoming emails in an Access table? Do you want to delete the
email once you have it stored in Access? Do you want to view the
contents of an email from Access? Do you want to create a list of
emails in the InBox in Access.

Sorry, but I'm still unclear about what exactly you are trying to
achieve. Perhaps you could give us some more information on this.

Peter.

 

[wheather girl]

Peter thanks for your input,

To answer your questions, Yes - I guess I was trying to store the emails in
an Access table in the "attachment" field, but you have advised that it may
not be so easy to do so, and then the storage space would be a problem. NO
- dont want to delete the email once I have stored it, you see this is for
work, I work in an engineering company and all the emails are permanently
stored in outlook, the incoming and outgoing emails are given a project
number specific to the project it relates to and then is moved into what we
call the public folders, and under the public folders the email is stored in
the corresponding project folder. Yes - I want to be able to view the
contents of an email from the access table, so maybe if it is possible, can
we use a hyperlink to veiw the email, then we also wouldn't have the problem
of storage space.

I am trying to set up a register of all the incoming and outgoing emails. We
do this at work as we deal with alot of contracts etc, and all the emials
have a legal entity, so we need to be able to trace the correct email at the
right time, quickly and effeciently...the table is set with fields such as
who sent/recieved it, the date is was sent/recieved on, what company it was,
what the subject content was about, what attachments were there, etc, so my
thought s were that it would be great if we could now maybe have a hyperlink
field, that would take us directly to the corresponding email. this would be
quicker and more effecient and save us time searching through outlook to find
the right email.

I hope this gives you more insight as to what I am trying to do.
As for creating a list of emails in the InBox in Access, well I didn't even
know that access had an inbox....is this may be an option? maybe not, dont
think it quite what i had in mind.

thanks peter

 

[Peter Hibbs]

Wheather Girl,

See comments in line below.

Peter Hibbs.

Peter thanks for your input,

To answer your questions, Yes - I guess I was trying to store the emails in
an Access table in the "attachment" field, but you have advised that it may
not be so easy to do so, and then the storage space would be a problem.

It would not be too difficult to store the email contents in an Access
table if they consist of ONLY text but the problem would be if there
are images included (or HTML code) in the body of the email. Do you
know if the emails will be text only and could you get any SPAM emails
which might complicate things?

NO
- dont want to delete the email once I have stored it, you see this is for
work, I work in an engineering company and all the emails are permanently
stored in outlook, the incoming and outgoing emails are given a project
number specific to the project it relates to and then is moved into what we
call the public folders, and under the public folders the email is stored in
the corresponding project folder.

The problem here is that if the emails are not deleted after you have
copied them into an Access table, how will the code know which ones
have been copied to Access and which ones are new emails the next time
you do the copying. It may be possible to compare the date and times
of each email in the Outlook InBox with all the email records in the
database and just copy the ones that don't match but I suspect this
would be tricky and slow. If you copy the emails to a different
Outlook folder after you have checked them you could then copy them to
Access after you have looked at them and then move them to a different
Outlook folder which could work but would be a bit fraught, it would
rely on people doing things in the right order every time.

Yes - I want to be able to view the
contents of an email from the access table, so maybe if it is possible, can
we use a hyperlink to veiw the email, then we also wouldn't have the problem
of storage space.

This is the very tricky one, I am not aware of any method of
displaying an email using code in an Access database. To check this I
have posted a question to the Outlook NG to see if anyone there knows
of a method. Will keep you posted. If this is not possible then I
don't see any alternatives other than just showing the text only part
of the email (which would be relatively easy).

I am trying to set up a register of all the incoming and outgoing emails. We
do this at work as we deal with alot of contracts etc, and all the emials
have a legal entity, so we need to be able to trace the correct email at the
right time, quickly and effeciently...the table is set with fields such as
who sent/recieved it, the date is was sent/recieved on, what company it was,
what the subject content was about, what attachments were there, etc, so my
thought s were that it would be great if we could now maybe have a hyperlink
field, that would take us directly to the corresponding email. this would be
quicker and more effecient and save us time searching through outlook to find
the right email.

I am not sure at the moment whether it is possible to capture all that
data but it probably is. As I see it you want to create a record in a
table for each email you receive with a field for the date received,
sender name, subject line, etc. That should be doable, will look into
that further. Perhaps you could confirm that that is what you want.

Another problem would also be - how do you select an email to view
(assuming it is possible). Say you create a table with 500 email
records, how do you know which is which, does the subject line have
some sort of unique value, would you know the date and time it was
received, how would you display a list of emails in your database - as
a list continuous list of records, in a List box, or what.

I hope this gives you more insight as to what I am trying to do.

It does (up to a point).

As for creating a list of emails in the InBox in Access, well I didn't even
know that access had an inbox....is this may be an option? maybe not, dont
think it quite what i had in mind.

Sorry, bad choice of words, I meant to say "Do you want to create a
list of emails that appear in the InBox in Outlook which can be viewed
from a form in your Access database".

 

[Peter Hibbs]

Hi Wheather Girl,

Just had a reply from Ken Slovak, Outlook MVP which is as follows :-

"To view an Outlook item you need to open Outlook.
If you store all the data from an Outlook item in a database table
then you can use an Access form you design to display the stored data,
otherwise no matter what you try, you need to use Outlook to display
Outlook items."

As I suspected, you can only view Outlook emails properly by using
Outlook. As I mentioned previously, you could still show pure text
emails in an Access form (Memo field), the question is - does that
change your plans or is that acceptable?

Perhaps you can let me know how you want to proceed, do you still need
code to do that or do you want to re-think your design?

Peter Hibbs.

 

[wheather girl]

Peter

thankyou for your running around that you have done on this... Regarding
advice from Ken Slovak - If I can only view text from the email in access,
then there is no point, I need to be able veiw everything and with
attachments. He mentiions that i can use an access form to display the
stored data, how do i go about doing that? but first I still need to store
the data from outlook to my access table, so still need to do that.

Yes - I want to create a record in a table for email I receive or send with
all the appropiate fields, we have a system that allows us to give each email
a designated number to that email we receive or send, for example if I send
an email I will give it first the project number of 08194 followed by S for
Send and then the number, so it would look like this 08194-S023 this number
is saved in the subject line, so we always know which email is which, and we
do the same when we have received the email except with a R for Reveived
inplace of the S.

Out of all of this it is starting to seem harder than what I first thought,
my orginal way of doing it was just to drag and drop the email to a folder on
my hard drive, and then attach the email to my table in the attachment field
for each record and/or hyperlink from the hyperlink feild to that folder
where i have saved them to there. This works perfectly and I am having no
problems with opening the emails in these manners as attachment or hyperlink.
I just thought that there must be a better way of doing it, without the
cumbersome dragging and dropping of each and every single email.

I just dont understand why we just cant hyperlink from the table and that
would open outlook if not already open and take us straight to that email, it
all just seems too hard. I guess what your saying is that the code doesn't
recognise it.


So not too sure how to proceed from here, I guess I just go back to doing it
the old way.

thankyou for all your trouble, I really appreciated it.

 

[Peter Hibbs]

Wheather Girl,

OK, I understand a bit more now. Have I got this right - you receive
an email in Outlook and, at the moment, you drag that email into a
specific folder on your hard drive which creates a copy of the email
which has a file name (which is the same as the Subject field) with a
..msg extension code. So I guess that would be something like
08194-R023.msg using your example. Then you could just double-click
that file to display the contents of that email. Is this correct?

You also have a table which holds the information for all these
emails, you seem to have fields for the sender name, date received,
subject line and so on. Is this correct?

What you are trying to do now is add a new field to this table (is it
called Attachments? I'm not sure) in which you save the pathname to
the email .msg file that you saved earlier and your idea is that if
this was a Hyperlink field you could just click on this field in your
form which would then open the associated email for display. Is this
correct?

Some more questions. How are you populating the table with information
about the emails, are you entering this information manually for each
one or are you using some sort of code to do it automatically? Is this
part of your project working as you want it or are you looking to
change that part as well?

You copy each email from Outlook to your hard disk manually at the
moment, are you trying to automate this part of the operation or are
you happy to keep doing that manually?

Here are my thoughts on what you have told me so far. When you create
a copy of the email on your hard disk it takes the contents of the
Subject line as its filename and appends the .msg code as the
extension. You also, it seems, have the same Subject line stored in a
table for each email record (which should be identical to the filename
of the saved file). What I would do is forget about using a Hyperlink
field (which can be difficult to use programmatically) and use the
standard Shell function to open the email from within the database.

Let's say you have all your email files saved in a folder called
'EMails' on your C: drive. In your database you would open your emails
display form (you are using a form I hope to display your email table
data), select the relevant email record and click a button to display
the associated email. In code, you would then create the full pathname
to the email like this :-

strPathname = "C:\Emails\" & Subject & ".msg"

where Subject holds the contents of the Subject field from your table.
Then you would pass that string to the Shell code which would open the
email for viewing in the same way as if you had double-clicked on the
file itself. If you want to go down this route just post back and I
can give you more details on the Shell code.

If you are also trying to automate the copying of the email from
Outlook to the hard disk I guess you could add a new button and some
VB code to your copy of Outlook which would do that automatically. The
problem with that is that you would need to add the code to your
Outlook program (unless Outlook 2007 has that option built in). If you
want to do that you will have to consult the experts in the Outlook
newsgroup because that would be quite complicated to do (assuming it's
even possible). I don't have Outlook 2007 installed so I can't help
you there.

I think you can do what you want (maybe with some compromises), let me
know if you want to continue this thread and what you want to do next.

Peter Hibbs.

 

[wheather girl]

Peter
Please read line below

Wheather Girl,

OK, I understand a bit more now. Have I got this right - you receive
an email in Outlook and, at the moment, you drag that email into a
specific folder on your hard drive which creates a copy of the email
which has a file name (which is the same as the Subject field) with a
..msg extension code. So I guess that would be something like
08194-R023.msg using your example. Then you could just double-click
that file to display the contents of that email. Is this correct? Yes that is correct.

You also have a table which holds the information for all these
emails, you seem to have fields for the sender name, date received,
subject line and so on. Is this correct? Yes that is correct.

What you are trying to do now is add a new field to this table (is it
called Attachments? I'm not sure) in which you save the pathname to
the email .msg file that you saved earlier and your idea is that if
this was a Hyperlink field you could just click on this field in your
form which would then open the associated email for display. Is this
correct?

Yes that is correct, except I haven't got form running for this table yet,
but will get around to it. I was hoping that the hyperlink could just go
staight to outlook and open up the email from there as our emails are stored
permanently in outlook, and that would save me having to save the emails to
the hardcopy folder first and then link it from there as this can become
cumbersome.

Some more questions. How are you populating the table with information
about the emails, are you entering this information manually for each
one or are you using some sort of code to do it automatically?

Doing it manually.

Is this

part of your project working as you want it or are you looking to
change that part as well?

No this seems to be working as I want it, no change needed here, unless you
have something that is more efficient and quicker. But all is generally good
at this stage.

You copy each email from Outlook to your hard disk manually at the
moment, are you trying to automate this part of the operation or are
you happy to keep doing that manually?

Well that was the part that I was trying to cut out and stop all together,
by using a hyperlink straigtht to outlook to open the email. So no i am not
happy with having to keep doing this manually, and as for automating it, I
dont really know what that means? will or is that a better way or option of
doing it?

Here are my thoughts on what you have told me so far. When you create
a copy of the email on your hard disk it takes the contents of the
Subject line as its filename and appends the .msg code as the
extension. You also, it seems, have the same Subject line stored in a
table for each email record (which should be identical to the filename
of the saved file). What I would do is forget about using a Hyperlink
field (which can be difficult to use programmatically) and use the
standard Shell function to open the email from within the database.

If the Shell function can do the job and open the email fromm within the
database, then that would be the way to go, but I dont know what the shell
function is or how to do it.

Let's say you have all your email files saved in a folder called
'EMails' on your C: drive. In your database you would open your emails
display form (you are using a form I hope to display your email table
data), select the relevant email record and click a button to display
the associated email. In code, you would then create the full pathname
to the email like this :-

strPathname = "C:\Emails\" & Subject & ".msg"

where Subject holds the contents of the Subject field from your table.
Then you would pass that string to the Shell code which would open the
email for viewing in the same way as if you had double-clicked on the
file itself. If you want to go down this route just post back and I
can give you more details on the Shell code.

This sounds like the way to go, only you say I have to still save the emails
from outlook to my folder on the hard drive, which is what I wanted to
bypass, as it is too time consuming. Can the Shell code go straight to
outlook and open the email up there instead of the folder on the hard drive,
cause if it cant then I am no better of than doing what I am currently doing.
If I still have to copy and save the emails from outlook to my hard drive
folder then I dont see how I have just improved my time management and
effeciency - I haven't changed anything then, just the code or the link where
it is coming from.

If you are also trying to automate the copying of the email from
Outlook to the hard disk I guess you could add a new button and some
VB code to your copy of Outlook which would do that automatically. The
problem with that is that you would need to add the code to your
Outlook program (unless Outlook 2007 has that option built in). If you
want to do that you will have to consult the experts in the Outlook
newsgroup because that would be quite complicated to do (assuming it's
even possible). I don't have Outlook 2007 installed so I can't help
you there.

This one sounds all too hard, as I said earlier dont know what automating
really is, and dont know how automating would improve on what I am trying to
accomplish.

I think you can do what you want (maybe with some compromises), let me
know if you want to continue this thread and what you want to do next.

Peter, what I want to do, is if the Shell code can open up my email from my
table in access under a new field, and open up the emial in outlook, without
me having to save the emial in my folder on the hard drive. I need to be able
to skip that bit, other wise I am no better off.


thanks Peter.

 

[Peter Hibbs]

Wheather Girl,

As I see it you have two areas of your system that you want to improve
on, that is speeding up the process of saving the emails from Outlook
to your hard disk and, once saved, having a simple and easy method to
display any of those emails from within the database when you are
displaying the associated record. Does that sound right?

I am not an expert on Outlook (or Access for that matter) but as I
understand it all emails, contacts, calendar info, etc are saved by
Outlook as .pst files somewhere on your hard drive. I don't believe
there is any way of displaying specific emails directly from those
files which means that your only option is to save them as .msg files
(as you do now) and then use Access to display those files.

What I am suggesting is that you speed that process up by adding a new
button to your Outlook tool-bar (ribbon in OL2007) along with some VB
code which will allow you to do that much quicker than you do at the
moment (that is what I meant by 'automating the process'). I assume
that you open Outlook to look at each email as it is received anyway
so with my suggestion, instead of then opening another folder and
dragging and dropping the email file to the folder you would just
select the email to copy, click the button on the tool-bar and it
would be done (automatically as it were). The problem with this option
at the moment is that I don't know how to write code to do that but I
am sure it can be done. If you want to try this method I will look
into it further.

The second problem is opening an email from within your database.
Using the Shell code I mentioned will make that bit easy but you will
need to add some VBA code to your database. How much do you know about
writing (entering) VBA code. If you know nothing at all then you may
need to seek some help from someone local who has that expertise, it's
not complicated code but it has to been done correctly. It involves
creating a Code Module and copying some code into it from this Web
site : :- http://www.mvps.org/access/api/api0018.htm
and also adding a few lines of code to your form. If you are prepared
to try this I will try and talk you through it, if you feel that you
would rather just stick with what you are doing now then, fair enough.

In either case I would suggest that you create a form with which to
display the contents of the table that holds your email records
because, if you go for my suggestions, you will need a Bound form as a
container for those records (as you cannot create an 'event' in a
table). I don't know how many fields you have in the table but a
Continuous form may do the job. The way this part would work is that
you open this form, display the email record that you are interested
in and then click a button (or possibly a field on the form) which
would open the email for you (just the same as if you had opened the
folder it was stored in and double clicked on it). As you can see,
this is a whole lot easier than what you are doing at present.

If you are still prepared to try this out, post back when you have
created the form and got that working properly. Also you should
provide some details about the table and the field names you used as I
will need that info. The full pathname to the folder that you
currently store the email message files would be useful as well.

There is one other issue that occurs to me, you said earlier that the
first part of the filenames represents the Project number. Does this
number remain the same all the time or do you have a number of other
project numbers? If so, do you save ALL the emails in the same folder
or do you have a different folder for the emails for each project? If
so, how do you name the folders? If you do have a lot of different
folders for different projects, the Outlook save code would need to
allow for that.

Anyway, let me know how you want to continue (or if you want to
continue).

Peter Hibbs.

 

[wheather girl]

Peter - -Please read line below.

Wheather Girl,

As I see it you have two areas of your system that you want to improve
on, that is speeding up the process of saving the emails from Outlook
to your hard disk and, once saved, having a simple and easy method to
display any of those emails from within the database when you are
displaying the associated record. Does that sound right? Yes.

I am not an expert on Outlook (or Access for that matter) but as I
understand it all emails, contacts, calendar info, etc are saved by
Outlook as .pst files somewhere on your hard drive. I don't believe
there is any way of displaying specific emails directly from those
files which means that your only option is to save them as .msg files
(as you do now) and then use Access to display those files.

What I am suggesting is that you speed that process up by adding a new
button to your Outlook tool-bar (ribbon in OL2007) along with some VB
code which will allow you to do that much quicker than you do at the
moment (that is what I meant by 'automating the process'). I assume
that you open Outlook to look at each email as it is received anyway
so with my suggestion, instead of then opening another folder and
dragging and dropping the email file to the folder you would just
select the email to copy, click the button on the tool-bar and it
would be done (automatically as it were). The problem with this option
at the moment is that I don't know how to write code to do that but I
am sure it can be done. If you want to try this method I will look
into it further. Oh yeah that would be great.

The second problem is opening an email from within your database.
Using the Shell code I mentioned will make that bit easy but you will
need to add some VBA code to your database. How much do you know about
writing (entering) VBA code. If you know nothing at all then you may
need to seek some help from someone local who has that expertise, it's
not complicated code but it has to been done correctly. It involves
creating a Code Module and copying some code into it from this Web
site : :- http://www.mvps.org/access/api/api0018.htm
and also adding a few lines of code to your form. If you are prepared
to try this I will try and talk you through it, if you feel that you
would rather just stick with what you are doing now then, fair enough.

well I think I might as well just stick to what i am doing, as this is
working fine, I am able to open the email from the attachement field or
hyperlink field, and if I still have to save the copy of the email to the
hard drive then it doesn't really matter then does it.... but to automate the
emails to the hard drive folder would be good, and that would save me time...
tell me is it possible to do that with the outgoing emails as well?

In either case I would suggest that you create a form with which to
display the contents of the table that holds your email records
because, if you go for my suggestions, you will need a Bound form as a
container for those records (as you cannot create an 'event' in a
table). I don't know how many fields you have in the table but a
Continuous form may do the job. The way this part would work is that
you open this form, display the email record that you are interested
in and then click a button (or possibly a field on the form) which
would open the email for you (just the same as if you had opened the
folder it was stored in and double clicked on it). As you can see,
this is a whole lot easier than what you are doing at present.

ok yes I will get that up and running

If you are still prepared to try this out, post back when you have
created the form and got that working properly. Also you should
provide some details about the table and the field names you used as I
will need that info. The full pathname to the folder that you
currently store the email message files would be useful as well. ok yes I will do that.

There is one other issue that occurs to me, you said earlier that the
first part of the filenames represents the Project number. Does this
number remain the same all the time or do you have a number of other
project numbers?

No this number for these emails stays the same, the only part that changes
is the end bit for example 08194-R1234, then the next one might be
081+94-R1235 and so on, but on another project the first number will change,
for example 09003-R0001 and then the next one might be 09003-R0002, but that
is for a new project for those emails to go into the 09003 folder and the
08194 will go into the 08194 folder,so for the whole project the first
numbers stay the same until the next project.
If so, do you save ALL the emails in the same folder
No they are saved in there own folder as mentioned above ie 08194 emails in
the 08194 folder and the 09003 emails in the 09003 folder.

or do you have a different folder for the emails for each project?

Yes
If so, how do you name the folders?
Name them as above.
If you do have a lot of different

folders for different projects, the Outlook save code would need to
allow for that. ok

Anyway, let me know how you want to continue (or if you want to
continue).

Yes I would be interested in automating the emails and also if possible to
be able to automate the sent emails as well as I have to save both. to be
able to document what was sent also,not only was has been received.... I
think that doing the shell code would not be so beneficial as I can already
open the emails to veiw them anyhow and that is working just fine....so maybe
just working on the automating button thing would be good. And I will get
the form up and running as yes I know it will be easier etc, just trying to
find the time

Peter thankyou so much for help, you' ve been a gem.

 

[Peter Hibbs]

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

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.

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? If so then we would need to
strip of the prefix before saving the email.

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 :-
(a) Overwrite the existing email with the new email (a dangerous
option unless you are sure it is the same email).
(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).
(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.
(d) Just not allow the email to be saved at all if it has the same
name as an already saved email.
Let me know what you want the code to do under these circumstances.

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

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.

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

Peter Hibbs.