Automate Outlook email handling from an Excel workbook (VBA)

[Oliver Dewar]

Hi All!

For my business I need to automate the handling of the emails that hit our default inbox in Outlook. The 'handle emails' script will be run every few minutes and do one of several things depending on the email.

I thought this thread could end up being a nice - Outlook automation from excel 'toolbox' as all the scripts I need will be very generic.

These are what I need the scripts to do:

1. Check for the presence of emails in the default inbox.

2. If there are emails - cycle through them one after the other running 'if' statements that do several things.

(i) Test against the subject.

(ii) Test against the email sender's address.

(iii) Copy and paste of the email body to a single cell in the workbook to be processed.

(iv) Move the email to another folder under the Inbox hierarchy.

(v) Change the email status from read to unread or vice versa.

That's it.

Obviously why these actions will be taken depends on the info in the emails as compared to my data.

If anyone can help me with these scripts I'll be hugely grateful. I've had a look around the net for the answers but can only find scripts that do half of what I need and then half of something completely different and as my coding understanding is poor at best - I can't extract the good from the bad.

Cheers all!

Oliver

 

[JP2112]

I start with the code I wrote here:

http://www.jpsoftwaretech.com/outlook-vba/stock-event-code/

<CODE class="vb keyword">

Private</CODE> <CODE class="vb keyword">WithEvents</CODE> <CODE class="vb plain">Items </CODE><CODE class="vb keyword">As</CODE> <CODE class="vb plain">Outlook.Items </CODE>
<CODE class="vb spaces"></CODE>
<CODE class="vb keyword">Private</CODE> <CODE class="vb keyword">Sub</CODE> <CODE class="vb plain">Application_Startup() </CODE>
<CODE class="vb keyword">Dim</CODE> <CODE class="vb plain">olApp </CODE><CODE class="vb keyword">As</CODE> <CODE class="vb plain">Outlook.Application </CODE>
<CODE class="vb keyword">Dim</CODE> <CODE class="vb plain">objNS </CODE><CODE class="vb keyword">As</CODE> <CODE class="vb plain">Outlook.NameSpace </CODE>
<CODE class="vb keyword">Set</CODE> <CODE class="vb plain">olApp = Outlook.Application </CODE>
<CODE class="vb keyword">Set</CODE> <CODE class="vb plain">objNS = olApp.GetNamespace(</CODE><CODE class="vb string">"MAPI"</CODE><CODE class="vb plain">) </CODE>
<CODE class="vb keyword">Set</CODE> <CODE class="vb plain">Items = objNS.GetDefaultFolder(olFolderInbox).Items </CODE>
<CODE class="vb keyword">End</CODE> <CODE class="vb keyword">Sub</CODE>
<CODE class="vb spaces"></CODE>
<CODE class="vb keyword">Private</CODE> <CODE class="vb keyword">Sub</CODE> <CODE class="vb plain">Items_ItemAdd(</CODE><CODE class="vb keyword">ByVal</CODE> <CODE class="vb plain">item </CODE><CODE class="vb keyword">As</CODE> <CODE class="vb keyword">Object</CODE><CODE class="vb plain">) </CODE>
<CODE class="vb spaces"></CODE>
<CODE class="vb keyword">On</CODE> <CODE class="vb keyword">Error</CODE> <CODE class="vb plain">Goto ErrorHandler </CODE>
<CODE class="vb spaces"></CODE>
<CODE class="vb spaces"></CODE><CODE class="vb keyword">Dim</CODE> <CODE class="vb plain">Msg </CODE><CODE class="vb keyword">As</CODE> <CODE class="vb plain">Outlook.MailItem </CODE>
<CODE class="vb spaces"></CODE>
<CODE class="vb spaces"></CODE><CODE class="vb keyword">If</CODE> <CODE class="vb plain">TypeName(item) = </CODE><CODE class="vb string">"MailItem"</CODE> <CODE class="vb keyword">Then</CODE>
<CODE class="vb spaces">  </CODE><CODE class="vb keyword">Set</CODE> <CODE class="vb plain">Msg = item </CODE>

     ' ******************************
     ' what do you want to do?
     ' ******************************

<CODE class="vb spaces"></CODE><CODE class="vb keyword">End</CODE> <CODE class="vb keyword">If</CODE>
<CODE class="vb spaces"></CODE>
<CODE class="vb plain">ProgramExit: </CODE>
<CODE class="vb spaces"></CODE><CODE class="vb keyword">Exit</CODE> <CODE class="vb keyword">Sub</CODE>
<CODE class="vb plain">ErrorHandler: </CODE>
<CODE class="vb spaces"></CODE><CODE class="vb plain">MsgBox Err.Number & </CODE><CODE class="vb string">" - "</CODE> <CODE class="vb plain">& Err.Description </CODE>
<CODE class="vb spaces"></CODE><CODE class="vb keyword">Resume</CODE> <CODE class="vb plain">ProgramExit </CODE>
<CODE class="vb keyword">End</CODE> <CODE class="vb keyword">Sub

This code will automatically process incoming emails in Outlook. Paste into ThisOutlookSession, customize it and restart Outlook.</CODE>