Home Ask Login Register

Developers Planet

Your answer is one click away!

Rgremer February 2016

Select a message based ont its subject from Outlook 2010, and run a macro that will copy data to Excel without manual intervention

Regretfully I have no formal background in VBA, but I have been able to learn quite a bit from sites like this.

Problem Statement:

I have a few emails with contain information that needs to be stored in excel. Fortunately I do have working script for that. Not provided to keep this somewhat shorter

The problem that I am facing is that capturing the right email from Microsoft Outlook 2010 and storing the data WITHOUT manual intervention.
The Email will contain a specific word/phrase, "EVEREST". Obviously it is not the only email received. It contains no attachments, and will come from various senders. I have tried various macros I have found on-line to pull the message from the inbox, but none of them have worked for me. So I have a macros that will pull messages from a personal folder, that macro then runs another macros that stores the contents of the email to excel, then it moves the message to its final resting place (another personal Folder) currently they all work fine together, but require manual intervention to complete the task. After the message is moved to the personal folder I simply click on a Quick Access Toolboar Icon mapped to a macro

To get the message moved over the personal folder i have a rule set up to move the message based on the word "EVEREST" and runs the initial script. The problem with all of this is that the message will get moved to the folder, but needs manual intervention to complete the task. I would like it to run automatically.

I have been fumbling around with this for the past 2 months and seem to be in a stalemate. I would greatly appreciate your feedback and assistance.

The following is what I have so far.

My outlook rule set is:

Apply this rule after the message arrives with "EVEREST" in the subject and on this computer only move it to the "EVEREST PRI" folder and run "Project1.ThisOutlookSession.Everest"

' I believe these were put here w        


niton February 2016

There is no need to select, you already have the required "email" passed as a parameter by the rule.

The run a script code will look something like this.

Sub Everest(email As MailItem)
    Dim Ns As NameSpace
    'Dim inboxFolder As Folder
    Dim olArchive As Folder

    Set Ns = GetNamespace("MAPI")

    CopyToExcelWithParameter email

    Set olArchive = Ns.Folders("Personal Folders")
    Set olArchive = olArchive.Folders("Archives")
    Set olArchive = olArchive.Folders("EVEREST Archive")
    email.Move olArchive

    ' Edit: Just realized this was due to
    ' unnecessary folder selecting that is now gone
    ' This is unnecessary now as well
    'Set inboxFolder = Ns.GetDefaultFolder(olFolderInbox)
    'Set ActiveExplorer.CurrentFolder = inboxFolder

    Set Ns = Nothing
    Set olArchive = Nothing
    'Set inboxFolder = Nothing

End Sub

You will have to rewrite CopyToExcel to take email as a parameter

Sub CopyToExcelWithParameter (email as mailitem)
    ' code that processes "email" directly, not a selection
    Debug.Print "Do something with " & email.subject
End Sub

Post Status

Asked in February 2016
Viewed 2,630 times
Voted 13
Answered 1 times


Leave an answer

Quote of the day: live life