A real challenge!

[ceyhorn]

I'm writing a program that will open email files in a folder. And then will check to see if it has any attachments. If it does it will open the attachments(.xls and .doc) it will pull information from specific cells or fields and then rename the email file the name pulled and then move that file to a particular folder based on another value that was pulled from the attachment.

I hope that explanation was not too convoluted.
Thanks for any help,
Chris

 

[SmallCraig]

Chris,

In fact what you want to do is not that complicated. It's long, and you need to keep track of where you are, but VBA is very friendly when it comes to its own objects.

I've developed a similar system for automating the processing of incoming mail. You can do it so that Outlook automatically checks the Inbox, but that's a bit harder, and is not very reliable. The current method initiates a macro by way of a toolbar button, and isn't bad, if a bit flaky at times (but that's Outlook - we can only work with the tools we have).

Stage 1 is to add the references you'll need to be able to access the Excel/Word object models. In the "Visual Basic Editor" (Alt+F11, or Tools/Macros/Visual Basic Editor), select Tools/References, and find the "Microsoft Excel x.x Object Library", and/or the "Microsoft Word x.x Object Library". Select the models you need, and apply these preferences.

Stage 2 is to establish a "path" to the Inbox. This code has to be placed in "This Outlook Session" in "Microsoft Outlook Objects".

Dim intLoop as Integer

Dim oNameSpace As NameSpace
Dim oInboxItems As Items
Dim oMailItem As MailItem
Dim oAttachments as Attachments
Dim oAttachment as Attachment
Dim oDestinationFolder as MAPIFolder

Dim oExcel as Excel.Application
Dim oWord as Word.Application

Set oNameSpace = Application.GetNamespace("MAPI")
' Instantiate the Items collection for the folder to monitor ...
Set oInboxItems = _
    oNameSpace.GetDefaultFolder(olFolderInbox).Items
    
For Each oMailItem In oInboxItems
        
    With oMailItem
        
        Set oAttachments = .Attachments
        If oAttachments.Count <> 0 Then
            for intLoop = 1 to oAttachments.Count

                set oAttachment = oAttachments(intloop)
                

' Find out here if the extension of &quot;.FileName&quot; = &quot;xls&quot; or &quot;doc&quot; 
' You can code this bit ...

                If &quot;extension = xls&quot; Then
                    set oExcel = New Excel.Application
                    oExcel.Workbooks.open .Filename
                  
                    if oExcel.range(&quot;A1&quot;) = &quot;???&quot; then
                        Set oProcessed = _
                                objNameSpace.GetDefaultFolder(olFolderInbox). _
                            Folders.Item(&quot;xls&quot;). _
                            Folders.Item(&quot;???&quot;)

                        .UnRead = False    ' Looks neater
                        .MoveItem oProcessed

                    End If
                End If  
            Next
        End If
    End With
Next

You'll of course want to tidy this up a bit - I've kind of been making it up as I go along, so it's a bit messy. And of course, set all your objects to "Nothing" when you've finshed with them - not only stops memory leaks, but results can be a bit unpredicatble with objects left lying around in memory.

Lastly, you may want to shift some of the processing off into functions or subroutines to make your "mainline" a bit easier to follow. I've in fact cobbled this single piece of code together from a couple of different subroutines.

This won't be the final solution, by any means, but I hope it gives you somewhere to start.

Best regards,
SmallCraig

 

[ceyhorn]

SmallCraig,

Thanks for the reply, it has definately gotten me on the right track. I do have one question, once I have gotten the name out of the particular file, can I rename the email that it is attached to using that retrieved information? Thanks again for the help

Chris

 

[SmallCraig]

Chris,

I don't know if "renaming" an e-mail is possible (does it have a name?), but you certainly can move it to a particular folder based on that name. That's what this bit of code does:

                        Set oProcessed = _
                                oNameSpace.GetDefaultFolder(olFolderInbox). _
                            Folders.Item("xls"). _
                            Folders.Item("???")

                        .UnRead = False    ' Looks neater
                        .MoveItem oProcessed

where "???" is the name that you've read out of the Excel file.

You can even create the folder if it's not there:

Dim oNewFolder As MAPIFolder ' extra. all other Dims are as before

On Error Resume Next
Set oNewFolder = _
    oNameSpace.GetDefaultFolder(olFolderInbox). _
                Folders.Item("???")
    
If Err.Number <> 0 Then
    
    On Error GoTo 0
        
' Set up the inbox ...
    Set oInbox = _
        oNameSpace.GetDefaultFolder(olFolderInbox)
        
' ... and add the new folder.
    Set oNewFolder = oInbox.Folders.Add(&quot;???&quot;)
                
    Set oNewFolder = Nothing
    Set oInbox = Nothing
        
End If
    
On Error GoTo 0

This is the only way I can think of to "rename" an e-mail. Someone else may have a better idea, though.

Hope this completes the picture,

SmallCraig