Outlook VBA : Loop through an outlook folder and Save attachments with file name as avalue from message body


I have an outlook folder where i keep unread messages from a sender, these mails have attachments which i would like to save to my desktop folder. But while saving i would like the file name to be a string from the message body.

For e.g.. the message body contains a string "ABC Corp", i would like to save the attachment using this string value as file name. each message body would have a different string.

Can someone guide me in the right direction to execute this using vba in outlook ?

V BSenior AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David Johnson, CD, MVPOwnerCommented:
Since the string is variable how is the computer supposed to know which string to use as the filename. Computers are the dumbest things on earth, they only do things quickly and will repeat an instruction until told to quit. Unlike animate objects that quickly tire. Humans unlike computers can try and guess .. computers only follow strict rules
V BSenior AnalystAuthor Commented:
Hi David,

thank you for taking time to respond to my question, the string i am looking for is available in the first few lines of the mail body and is standard across all emails, a sample is given below

Please find attached your xxxxxxx eBill for,

Account Number: 0123456789
Bill Period: 01/06/15 to 30/06/15
Due Date: 15/07/15

i want to save the attachment using the account number for each mail. I was wondering if a regular expression would do the trick, still trying it out, thought someone in EE might have already found a way to do it.

omgangIT ManagerCommented:
Paste the following into a new module in your Outlook VBE.
See comments in the code where you need to make changes for your environment.

Not a regular expression but a function to parse the message body and get the account #

OM Gang

Option Explicit

Public Sub GetAcctNumFromMsg()

On Error GoTo Err_GetAcctNumFromMsg

    Dim olNS As NameSpace
    Dim olMailBox As MAPIFolder, olFolder As MAPIFolder
    Dim olItems As Outlook.Items
    Dim olMsg As Outlook.MailItem
    'Dim olAttachment As Outlook.Attachment
    Dim strMailboxName As String, strFolderName As String
    Dim strMsg As String, strAcct As String
    strMailboxName = "Mailbox - Gang, OM"          '<--- change for your mailbox name
    strFolderName = "Test"                                         '<--- change for the folder you wish to process

    Set olNS = Outlook.GetNamespace("MAPI")
    Set olMailBox = olNS.Folders(strMailboxName)
    Set olFolder = olMailBox.Folders(strFolderName)
    Set olItems = olFolder.Items
        'sort the folder items
    olItems.Sort "[ReceivedTime]", False
    For Each olMsg In olItems
        strMsg = olMsg.Body
            'call parsing function to get account nuumber
        strAcct = ParseString(strMsg)
        Debug.Print strAcct

                    '    add you code for saving the attachment with the account # for filename


        'destroy object variables
    Set olMsg = Nothing
    Set olItems = Nothing
    Set olFolder = Nothing
    Set olMailBox = Nothing
    Set olNS = Nothing
   Exit Sub

   MsgBox Err.Number & " (" & Err.Description & ") in procedure GetAcctNumFromMsg of Module Module4"
   Resume Exit_GetAcctNumFromMsg
End Sub

Public Function ParseString(strIn As String) As String
On Error GoTo Err_ParseString

'parses message body passed as argument and returns array of values

    Dim lngPos1 As Long, lngPos2 As Long
    Dim intCount As Integer
    Dim strSearch1 As String, strSearch2 As String, strElement As String
    strSearch1 = "Account Number:"
    strSearch2 = vbCrLf
    lngPos1 = InStr(strIn, strSearch1) + Len(strSearch1)
    lngPos2 = InStr(lngPos1, strIn, strSearch2)
    strElement = Mid(strIn, lngPos1, lngPos2 - lngPos1)

        'function return
    ParseString = strElement
   Exit Function

   MsgBox Err.Number & " (" & Err.Description & ") in procedure ParseString of Module Module4"
   Resume Exit_ParseString
End Function

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

V BSenior AnalystAuthor Commented:
Thank you Omgang,

i will test this out and let you know. Once again thank you for your effort.

V BSenior AnalystAuthor Commented:
Hi Omgang
i have tested this with one mail in a folder and looks like its working ok for one mail, and i believe this should work fine with multiple mails in a folder as well, i am checking this in the next few days.

will update the results here, thanks for your outstanding solution

V BSenior AnalystAuthor Commented:
Hi Omgang,

sorry for the delay in responding back to you. I was actually testing this on a large scale project, and i have hit a road block. when i try to run this on a folder which has multiple emails, i get the error message

" -2147221233 (The attempted operation failed.  An object could not be found.) in procedure GetAcctNumFromMsg of Module Module4 "

I did a debug and looks like its failing at the "olFolder", as in, it cant find the folder, although i have defined the folder under the inbox, and i can see the folder structure within the inbox.

I am trying to do some research of my own on this, but if you could have a look at this when you get some time, that would be great.

V BSenior AnalystAuthor Commented:
Hi Omgang,

i think i got it working, a bit of tweak to your code, i will test this with our test unit and post the results.

omgangIT ManagerCommented:
Sorry I missed your posts over the weekend.  Let me know if you need anything.
If the folder where the messages are stored is a subfolder of another folder the code will need to be modified a bit.  Perhaps a folder picker instead of hard coding the folder name?

OM Gang
V BSenior AnalystAuthor Commented:
Hi OMGang,

I should get the results out tomorrow, will update it over here. The crux of the application is the ParseString function that you wrote, this works like magic.

V BSenior AnalystAuthor Commented:
Hi OMGang,

i would like to thank you for this amazing solution that you have provided, this works like magic :)

I am marking this as the accepted solution.

Once again, thank you for your efforts.

V BSenior AnalystAuthor Commented:
OMGang was fast in responding and coming up with a solution that required no change and worked like magic.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.