Automate Outlook

Posted on 2003-03-16
Medium Priority
Last Modified: 2010-04-07
Hi all,

I would like to create a script (vb6) which opens outlook 2002 (exchange) and finds unread emails with a certain subject line and saves the attachment. I need it to either check periodically (preferable) or continuously repeat.

If someone could put me on the correct path...

Much appreciated,
kind regards,
Question by:katiew
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 23

Expert Comment

ID: 8149444
You would need to be familiar with the object model of Outlook 2002 - http://www.microeye.com/resources/ObjectModel2002.htm
Next you may need some code samples

Dim oItemContact As Outlook.ContactItem
Set oItemContact = oOutlookApp.CreateItem(olContactItem)
   With oItemContact
       .FirstName = "Some"
       .LastName = "One"
       .FullName = "Some One"
       .CompanyName = "Top Company"
       .Email1Address = "someone@topcompany.com"
       .Email2Address = "someone2@topco.com
    .HomeAddressStreet = "1 Very Quiet Street"
       .HomeAddressCity = "Quiet City"
       .HomeAddressPostalCode = "1234"
       .HomeAddressCountry = "South Africa"
       .BusinessTelephoneNumber = "(021) 5555555"
       .MobileTelephoneNumber = "(082) 5555555"
   End With

That is for contacts, but with that methodology and the OL2002 object model you should be able to get a good start.

Accepted Solution

Todd Wedlake earned 1000 total points
ID: 8158232
How? To begin with, go to Tools > Macro > Macros, type a new name for your macro in the Macro Name field, and click Create.

Outlook will load the Visual Basic editor for you and start your new macro with the proper name, like so:

Sub ReportSave()
End Sub
Now just set up the objects you'll need to make this work:
Dim oApp As Application
Dim oNS As NameSpace
Dim oMsg As Object
Dim oAttachments As Outlook.Attachments

Set oApp = New Outlook.Application
Set oNS = oApp.GetNamespace("MAPI")
This gets your macro read to work with items.
Be sure you're working with the Inbox, so set the current folder to the Inbox like so:

Set oFolder = oNS.GetDefaultFolder(olFolderInbox)
The table is set; let's eat.
How the Program Works
Outlook looks at each item in the Inbox and determines which of them to auto-process. In most cases, the FROM address is the same for your automated report messages, so you can use that address as the criterion to check:

For Each oMsg In oFolder.Items
This tells Outlook to cycle through each item...
With oMsg
and with each item it is to...
If .SenderName = "Report Generator" Then
Check to see if the SenderName is "Report Generator." SenderName is the display name of the person who sent you the message. If it is from the specified sender, then it is to...
oMsg.Attachments.Item(1).SaveAsFile "C:\reports\in\" & 
Use the SaveAsFile method to save the file to the C:\Reports\in\ directory using the filename that the sender gave it. If you'd rather assign your own filename to the item, you can do so by substituting your own filename, in quotes, for the "myAttachments.Item(1).DisplayName" part, like this:
oMsg.Attachments.Item(1).SaveAsFile "C:\reports\in\" & "report.txt"
After you're done saving the file, you probably want to delete the message it came in because you don't need it once the file is saved. You can use this...
to do that. Remember that you started with a "With Each oMsg in oFolder.Items" so you can simply use the .Delete method to delete the current message.
Knowing Whether You Got a Report
By having the program automatically save the file and delete the message, you may not know (without looking) whether you received your automatic report or not. To solve this, you can give yourself a notification with a message box by adding this line after the .Delete:

MsgBox "The report has been processed"
At this point, if you receive a report that is processed, the message box will pop up with that message and you'll acknowledge it by clicking OK.
That's the basics of the application. You'll need an "End If" statement to close your IF procedure that checks for SenderName, an "End With" statement to finish the "With Each oMsg", and a "Next" statement, like this:

                   End If
           End With
End Sub
Now you're done!
Keeping Old Reports
One flaw in this program is that if you have more than one report waiting from the same auto-report generator (say, if you've been away for a day or two and haven't logged in), it's going to cycle through and save each one with the same filenameoverwriting the first ones saved with the later ones, so that you end up with just one report.

To prevent this auto-deletion, have the program assign a unique number to the beginning of each filename. Go back to where you defined your variables and define a string like so:

DIM StrControl
Assign it a value by adding this line beneath where you set your oFolder to the Inbox:
StrControl = 0
Then add this:
StrControl = StrControl + 1
Beneath the "If" statement that checks for SenderName to increment the value by one each time a message from the report generator is discovered.
Finally, change the line that saves the file by adding that StrControl value to the beginning of the filename, like this:

oMsg.Attachments.Item(1).SaveAsFile "C:\reports\in\" & strControl
& "report.txt"
The Final Script
When you're all done, the completed macro should look something like this:

Sub Reportave()
    Dim oApp As Application
    Dim oNS As NameSpace
    Dim oMsg As Object
    Dim oAttachments As Outlook.Attachments
    Dim strControl
    Set oApp = New Outlook.Application
    Set oNS = oApp.GetNamespace("MAPI")
    Set oFolder = oNS.GetDefaultFolder(olFolderInbox)
    strControl = 0
    For Each oMsg In oFolder.Items
        With oMsg
            If .SenderName = "Report Generator" Then
                strControl = strControl + 1
                oMsg.Attachments.Item(1).SaveAsFile "C:\reports\in\"
                        & strControl & "report.txt"
            End If
        End With
End Sub

Author Comment

ID: 8199600

It works perfectly. Sorry to take so long to reply :)

Just wondering though is there any way I can have this either:

repeat until user intervention; or

set to run at specific times?

Kind regards,

Author Comment

ID: 8199684
Also, is it possible to have multiple criteria for the If statement:

eg If .SenderName = "Report Generator" and .Subject = "Prado" Then


Thanks Cathryn

Featured Post

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!

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how to import Lotus Notes Contacts into Outlook 2016, 2013, 2010 and 2007 etc. with a few manual steps. You can easily export and migrate Lotus Notes contacts into Microsoft Outlook without having to use any third party tools.
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…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Suggested Courses

765 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