Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Automating extract of Outlook attachenments and saving as new filename

Posted on 2006-05-08
14
Medium Priority
?
510 Views
Last Modified: 2012-05-07
I need to extract .wav file attachments from incoming email messages and save them as specific filenames based on the subject and the TIME the email was either sent or received (whichever is easiest, prefer sent time) in military format.  The format of the subject is as follows:

[0:33] Message for MB 2102 from x 2296 (FIRSTINITIAL LASTNAME)

The only information needed fromo the subject line is FIRSTINITIAL LASTNAME.  The other information is from the email timestamp.

I need the output filename to follow this format:

hhmmss_FIRSTINITIAL_LASTNAME.wav
(using military time format)


Any ideas?

Tim Mueller
0
Comment
Question by:jpegvarn
  • 7
  • 6
14 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 16631277
Hi Tim,

Does this need to be automatic or manual?

Cheers!
0
 

Author Comment

by:jpegvarn
ID: 16631514
I am pretty open.  Ideally it would be triggered manually so we can keep an eye on it.

Tim
0
 
LVL 18

Expert Comment

by:Karen Falandays
ID: 16632788
I like the Outlook attachment sniffer, found here:
http://www.rsbr.de/Software/OASniffer/index_eng.htm
kfalandays
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 76

Expert Comment

by:David Lee
ID: 16635028
If you're going to run the code manually do you want to select the message it'll process or should it look through all the messages in the folder, presumeably the Inbox, and find the ones matching a condition?
0
 

Author Comment

by:jpegvarn
ID: 16638228
I have tried the Attachment Sniffer.  It works great, but won't let me extract information from the subject line or the message delivery time to create the new filename.  It extracts the attachments perfectly though.

LIke the Sniffer, I'd like the code to search a specific mailbox, find attachments of a specific type (.wav) and save those as the file name structure explained above.

Tim
0
 
LVL 76

Accepted Solution

by:
David Lee earned 2000 total points
ID: 16644563
Here's the code for doing this.  Follow these instructions to use it.

1.  Start Outlook
2.  Click Tools->Macro->Visual Basic Editor
3.  If not already expanded, expand Modules and click on Module1
4.  Copy the code below and paste it into the right-hand pane of the VB editor window
5.  Edit the code as needed.  I placed comment lines where things need to be changed
6.  Click the diskette icon on the toolbar to save changes
7.  Close the VB Editor
8.  Click Tools->Macro->Security
9.  Set the Security Level to Medium
10.  Test the macro.  To do that select a folder and then run the macro.  The code will look at every message in the folder.  It will examine every attachment.  If the attachment ends with .wav, then it will calculate the new file name and save it to disk.

Sub SaveWavAttachments()
    Dim olkFolder As Outlook.MAPIFolder, _
        olkMessage As Object, _
        olkAttachment As Outlook.Attachment, _
        objFSO As Object, _
        strRoot As String, _
        strInitial As String, _
        strLast As String, _
        strTime As String, _
        strTemp As String, _
        arrName As Variant
    'Change the root path on the following line
    strRoot = "C:\eeTesting\Attachments\"
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set olkFolder = Application.ActiveExplorer.CurrentFolder
    For Each olkMessage In olkFolder.Items
        For Each olkAttachment In olkMessage.Attachments
            If LCase(objFSO.GetExtensionName(olkAttachment.FileName)) = "wav" Then
                strTime = Format(olkMessage.SentOn, "hhmmss")
                strTemp = Mid(olkMessage.Subject, InStr(1, olkMessage.Subject, "(") + 1)
                strTemp = Replace(strTemp, ")", "")
                arrName = Split(strTemp, " ")
                strInitial = arrName(0)
                strLast = arrName(1)
                olkAttachment.SaveAsFile strRoot & strTime & "_" & strInitial & "_" & strLast & ".wav"
            End If
        Next
    Next
    Set olkAttachment = Nothing
    Set olkMessage = Nothing
    Set olkFolder = Nothing
    Set objFSO = Nothing
End Sub
0
 

Author Comment

by:jpegvarn
ID: 16647614
Blue Devil Fan;

Thanks for your work.  I can't seem to get the macro to run, though I am betting it's my inexperience with VBA rather than the code itself.  I made the one change in the code I found (strRoot...), saved the file and closed VBA.  I then set the macro security as required, and ran the macro with the Outlook folder I need the macro run against highlighted.  

The PC went to hourglass for a minute, but didn't seem to do anything, nor did the attachments appear in the folder I set "strRoot" to.

Is there something I might have miseed?  Also, am curious as to how you are parsing the subject line to grab the text within the parens (FIRSTINIT LASTNAME).  Keep in mind that this information is in the subject as text;  it is NOT the sender or recipient.  It is placed in the subject by the telephone switch that generates the email attachements and send the email.

Tim
0
 

Author Comment

by:jpegvarn
ID: 16647643
BlueDevilFan,

I think I found the problem!  I cahnged the Root save-to folder to my desktop without realilzing it. I forgot the last \ in the line!  I have 300+ .wav files on my desktop!

I'll make the changes and see what happens.  Thanks again!

Tim
0
 

Author Comment

by:jpegvarn
ID: 16647683
BlueDevilFan;

I was right, my error.  Once I put the ending slash in, the macro worked great.  

A little finetuning and one problem.  I changed the time to hhmm as I don't need the ss after all.

For some reason, the times are off by one hour.  The file name is one hour ahead of the timestamp; ie. the create time is 9:30PM, the file name reflects 2230 (10:30 PM).

Any thoughts?

Tim
0
 
LVL 76

Expert Comment

by:David Lee
ID: 16647876
> For some reason, the times are off by one hour
Did they come from a different time zone?
0
 

Author Comment

by:jpegvarn
ID: 16647970
Nope, they are local to this network.  The mail server and telephone switch are all right in the building.  I'll have to check the times on the server to be sure, but they "should" be correct.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 16649653
jpegvarn,

This is the line of code where I get the date

    strTime = Format(olkMessage.SentOn, "hhmmss")

As you can see I'm not changing it, just formatting it to remove the colons.  If the sent date shows right in the message and wrong here, then I don't have an explanation.
0
 

Author Comment

by:jpegvarn
ID: 16650501
I will check on the time issues later today/tomorrow.

Just ran into a new wrinkle.  It appears that if the subject line doesn't fiollow the (firstinitial lastname) setup, I get a subscript out of range error.  IS there a trap that can be placed in the code to allow (anything) to work?

Tim
0
 
LVL 76

Expert Comment

by:David Lee
ID: 16652366
I can certainly alter the code to not give an error, but if the (Firstinitial Lastname) isn't in the subject line and in some consitent format, then I won't be able to contruct the file name.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The core idea of this article is to make you acquainted with the best way in which you can export Exchange mailbox to PST format.
There can be many situations demanding the conversion of Outlook OST files to PST format and as such, there is no shortage of automated tools to perform this conversion. However, what makes Stellar OST to PST converter stand above the rest? Let us e…
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question