asked on

Searching and accessing Outlook inbox from Excel

Hi,

I woud like to create a macro in excel visual basic that does the following:
- check to see if an email has hit the user's outlook inbox yet (criteria for the email being date, sender and subject line)
- if yes, open the worksheet attached in the email
- copy from the attached worksheet to the activeworksheet (I know this part)
- close the attached worksheet (and message if necessary)

Or if someone knows if this is not possible I would appreciate if you could let me know.

Thanks very much in advance,

Mark

William Elliott

use rules wizard
http://support.microsoft.com/kb/306108

To implement the custom code to process the message, create a subroutine in Visual Basic for Applications. The name of the subroutine does not matter, but it must accept one argument because the Rules Wizard will pass a mail message (MailItem) or meeting request (MeetingItem) to the subroutine. The argument must by of type MailItem or MeetingItem, otherwise the subroutine will not be available in the Rules Wizard. You cannot create one subroutine to handle both types of items by defining the argument to be of type Object. The following Outlook Visual Basic for Applications code illustrates how to create the subroutines:

Sub CustomMailMessageRule(Item As Outlook.MailItem)
MsgBox "Mail message arrived: " & Item.Subject
End Sub

Sub CustomMeetingRequestRule(Item As Outlook.MeetingItem)
MsgBox "Meeting request arrived: " & Item.Subject
End Sub

You can put the subroutine in any module, including ThisOutlookSession, but if you move the subroutine to another module or change the subroutine's name, you must modify the rule to point to the updated subroutine.

Rory Archibald

Is this on an Exchange Server or using Personal folders? I suspect you will run into the Outlook Security Guard which will stop you in your tracks unless the user allows access.
Can I ask why you want to do this - it seems very convoluted.
Regards,
Rory

Meldrum_Mark

ASKER

Thanks weelio,

That all works great, however the issue still remains of accessing these messages from Excel and manipulating the attachment. Any ideas?

thanks,

MM

ps: rorya - I'm doing this because I receive multiple emails daily from which I open the attachment, find desired data and copy it to where I need it in my own spreadsheet. If I could automate this it would save me copious amounts of copying and pasting.

Meldrum_Mark

ASKER

pps: yes it is on an Exchange Server. Is there any way to get around the Oulook Security Guard?

Rory Archibald

I haven't yet checked to see if anything required would trigger it, though I suspect so, but you would need either to use Extended MAPI calls (very tricky), or use the Redemption library, or use a third party program like Click-Yes. If you have a friendly network admin, they may be able to trust your dll for you.

Rory Archibald

Sorry - bit of shorthand there - you would have to compile your code into a COM add-in for that last part to work.

ASKER CERTIFIED SOLUTION

William Elliott

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

William Elliott

i ran this from within outlook, not excel.. but you shoudl be ale to manipulate excel from outlook, utilizing vba

William Elliott

also if you want to specify the attachment information
If Atmt.FileName = "myspreadsheet.xls" Then
or
If Right(Atmt.FileName, 3) = "xls" Then

Meldrum_Mark

ASKER

Thanks weellio, worked beautifully.

rorya - thanks for the assistance but weellio's tips ended up working out, although I agree with you that the Exchange Server can cause some problems and am quite surprised that it didn't here.

Take care guys