Timestamp from Outlook

[Steph]

Hi. Every week I run a procedure that saves the attachments from a bunch of
e-mails with a single folder to 1 large file. My question is this - can I
somehow also, for each file, pull in the timestamp from when the e-mail was
received??

 

[Dick Kusleika]

Steph

Use the ReceivedTime property of the MailItem object.

 

[Jack]

Thanks Dick. Do you know of any sample syntax?

Steph

Use the ReceivedTime property of the MailItem object.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

bunch

 

[Dick Kusleika]

As a matter of fact I do. I should have included that in my answer, sorry
about that.

http://www.dicks-clicks.com/excel/olRetrieving.htm

 

[Steph]

Thanks Dick. I checked it out, but admit I'm real good at this stuff.
Recall that my procedure saves all attachments to a specific folder (not an
outlook folder). I have a counter in place and am renaming the files
arbitrarily based on that. Would it be easy to save the filename as the
ReceivedTime? I guess the one pre-req I should have asked is Can 2 e-mails
arrive at the exact same time, and therefore have the exact same
ReceivedTime?

Thanks Dick!!!!!!!!!!!!!!

 

[Steph]

Sorry Dick, my previous post shoud have read But I admit I'm NOT real good
at this stuff!!!!!! Stressing the word NOT!!
Sorry....that typo made me sound like a know-it-all pompous ass!!

 

[Dick Kusleika]

Steph

Yes, you can have two emails with same received time. Of the 1,406 emails
in my Junk folder, two pair had the exact same received time. That's pretty
low odds, but I don't know if it's worth it. If you'd like to use the
ReceivedTime for other reasons, then you could pretty easily append a number
to the end of the file name if it already exists.

Each message has a unique ID, but it's like 27 characters long and pretty
unintelligible. Let me know if you need more help with any of these
options.

 

[Steph]

Hi Dick,

Wow, so it is possible that 2 emails arrive at the same time. The reason
I'd like to use it is this - we are having contractors send in their
timesheets via e-mail (roughly 300) each week. I'm sure the following will
happen - someone sends in their timesheet, then later realized they screwed
up, and therefore they'll resend. My current code grabs all attachments and
consolidates then onto one sheet for Supervisor approval. In the previous
example, someone will have 2 submittions. I wanted to use the timestamp to
help the Supervisors choose which one to reject and which one to approve.
(the assumption being the last one received is the one to use).

But I guess the possibility exists that of the 300 e-mails coming in each
week, 2 do show up at the exact same time.

So that being said, and my ignorance for this level of coding, can you help
me? The appendage to the end of the time would be nice if 2 e-mails arrive
at the same time. So is it easy to append my code to save the file name as
the ReceivedTime (plus possibly the end # appendage)??

Thanks Dick!!

 

[Dick Kusleika]

Steph

So that being said, and my ignorance for this level of coding, can you help
me? The appendage to the end of the time would be nice if 2 e-mails arrive
at the same time. So is it easy to append my code to save the file name as
the ReceivedTime (plus possibly the end # appendage)??

Once you've identified an email with an attachment to save, try something
like this

lUnique = 1
Do
sFname = Format(mi.ReceivedTime, "yyyymmddhhmmss") & lUnique &
".xls"
lUnique = lUnique + 1
Loop Until Len(Dir(sPath & sFname)) = 0

mi.Attachments(1).SaveAsFile sPath & sFname


where mi is MailItem variable. This loops adding a larger number to the end
of the proposed filename until it comes upon a file name that doesn't exist.