You are here: Home > Excel > Hands Off That Email Attachment!

Hands Off That Email Attachment!


April 14, 2008 – 5:49 pm by JP

About halfway down the Outlook page, there is some VBA code for opening an Excel workbook attached to an email, running a macro on it, then cleaning up and moving the email to another folder.

Here is some more sample code to make your macro even more intelligent. This code opens the attachment, then scans through the spreadsheet looking for an error condition. If the error condition is met, an email is generated to another mailbox, as an alert, and the email is left in the Inbox to be looked at by a human. If the error condition isn't met, there's no reason to look at the email, so it gets marked as read and moved to an archive folder.

If you are like me, then your time is valuable. You don't want to spend time looking at emails that you don't need to work on. This code lets me focus on high-value activities while using Outlook's built-in capabilities to ignore the spreadsheets that don't need personal attention.

First you want to open ThisOutlookSession and paste this event code:

plain text version
VBA:
  1. Private WithEvents QueueInbox As Outlook.Items
  2. Dim bWasPosted As Boolean

Really you can use anything in place of "QueueInbox", but I use something that will relate to the task so I can easily identify what the code is doing. The boolean variable is put in the global section because we are using it in one procedure (the mail sending sub) and checking the value in another sub.

Now you need to tell Outlook to monitor your chosen Inbox for new items. This code placed in the Application_Startup event will do that.

plain text version
VBA:
  1. Set QueueInbox = objNS.Folders("Mailbox - My Mailbox Name").Folders("Inbox").Items

Replace "My Mailbox Name" with the name of the mailbox you want to check. For example, if I were running this on my own Inbox, the code would be

plain text version
VBA:
  1. Set QueueInbox = objNS.Folders("Mailbox - Jimmy Pena").Folders("Inbox").Items

If you don't have an Application_Startup event, just put "Private Sub Application_Startup()" right above the Set statement, and "End Sub" right below it.

Now on to the real work. When a new item is added to the Inbox we selected, it saves and opens the attachment, checks to make sure everything is OK, and if so, moves the email to an archive folder. Don't forget to set a reference to the Excel object library (see binding page for assistance).

Of course I make some assumptions here, which you might want to change. For example, the emails we are checking have a known sender and subject, which was pre-arranged. Also, they always have one .xls attachment. If you can agree on a system like this, you'll be able to use Outlook code to automate some pretty boring processes.

plain text version
VBA:
  1. Private Sub QueueInbox_ItemAdd(ByVal Item As Object)
  2.  
  3. Dim objNS As Outlook.NameSpace
  4. Dim ArchiveFolder As Outlook.MAPIFolder
  5. Dim Msg As Outlook.MailItem
  6. Dim myAttachments As Outlook.Attachments
  7. Const attPath As String = “C:\”
  8. Dim colACount As Long
  9. Dim colGCount As Long
  10. Dim cell As Excel.Range
  11. Dim MyBook As Excel.Workbook
  12. Dim MySheet As Excel.Worksheet
  13. Dim colARange As Excel.Range
  14. Dim colGRange As Excel.Range
  15. Dim bBadCount As Boolean
  16.  
  17. bWasPosted = False
  18. bBadCount = False
  19.  
  20. Set objNS = GetNamespace(”MAPI”)
  21. ' the archive folder we are moving emails to
  22. Set ArchiveFolder = objNS.Folders(”Mailbox - My Mailbox Name”)._
  23. Folders(”Inbox”).Folders(”Archive”)
  24.  
  25. ' check to make sure we are looking at a mailitem
  26. If TypeOf Item Is Outlook.MailItem Then
  27.   If (Item.SenderName = “MySender@somewhere.com”) And _
  28.   (Item.Subject = “Attachment You Requested”) Then
  29.     ' obj ref to msg
  30.     Set Msg = Item
  31.     ' get attachment
  32.     Set myAttachments = Msg.Attachments
  33.     Att = myAttachments.Item(1).DisplayName
  34.     myAttachments.Item(1).SaveAsFile attPath & Att
  35.  
  36.     ' open workbook and check for errors
  37.     Set XLApp = New Excel.Application
  38.     Set MyBook = XLApp.Workbooks.Open(attPath & Att)
  39.     Set MySheet = MyBook.Sheets(1)
  40.  
  41.     ' check count of columns A & G to make sure they match
  42.     ' at this point you could do anything you want to the workbook, as if you
  43.     ' were in the Excel VBE
  44.     Set colARange = MySheet.Range(”A2:A5000″)
  45.     Set colGRange = MySheet.Range(”G2:G5000″)
  46.  
  47.     colACount = colARange.SpecialCells(xlCellTypeConstants).Count
  48.     colGCount = colGRange.SpecialCells(xlCellTypeConstants).Count
  49.  
  50.     If colACount <> colGCount Then
  51.     ' we found an error!
  52.       MyBook.Close False
  53.       ' call other macro and pass email to it as an argument,
  54.       ' so we can use some properties of the current mailitem
  55.       Call PostMsg(Msg)
  56.       bBadCount = True
  57.     End If
  58.  
  59.   ' bBadCount is a boolean variable checking if the match count
  60.   ' caused an error, we skip further checks if that is the case
  61.   ' because we only need one error
  62.  
  63.   If bBadCount = False Then
  64.   ' the count matched, but maybe we have invalid values
  65.     For Each cell In colGRange.SpecialCells(xlCellTypeConstants, 2)
  66.       If (cell.Value = “#N/A”) Then
  67.         MyBook.Close False
  68.         Call PostMsg(Msg)
  69.       End If
  70.     Next cell
  71.   End If
  72.  
  73.   ' at this point, if PostMsg was called,
  74.   ' bWasPosted would be True
  75.  
  76.   If bWasPosted = False Then
  77.   ' we didn’t post a msg to MyInbox, so there must be
  78.   ' nothing wrong with the attachment, so we can move it
  79.     With Msg
  80.       .UnRead = False
  81.       .Move ArchiveFolder
  82.     End With
  83.   End If
  84.  End If
  85. End If
  86.  
  87. ExitProc:
  88. On Error Resume Next
  89. XLApp.DisplayAlerts = False
  90. XLApp.Workbooks.Close
  91. XLApp.DisplayAlerts = True
  92. Kill attPath & Att
  93. XLApp.Quit
  94. On Error GoTo 0
  95. Set ArchiveFolder = Nothing
  96. Set objNS = Nothing
  97.  
  98. End Sub

plain text version
VBA:
  1. Sub PostMsg(Msg As Outlook.MailItem)
  2. '
  3. ' sub that actually puts the email in the inbox
  4. '
  5. Dim NotifyMsg As Outlook.MailItem
  6. Dim MyFolder As Outlook.MAPIFolder
  7. Dim objNS As Outlook.NameSpace
  8. Dim strMsg As String
  9.  
  10. Set objNS = GetNamespace(”MAPI”)
  11. Set MyFolder = objNS.Folders(”Mailbox - Jimmy Pena”).Folders(”Inbox”)
  12.  
  13. Set NotifyMsg = MyFolder.Items.Add(olMailItem)
  14.  
  15. With NotifyMsg
  16.   .Subject = “Invalid Attachment Received”
  17.   .Importance = olImportanceHigh
  18.   .To = “jpena@myemail.com”
  19.  
  20.   strMsg = “The following message was received by Queue Inbox on ” & Msg.ReceivedTime & “:”
  21.   strMsg = strMsg & vbCr & vbCr & Msg.Body
  22.   strMsg = strMsg & vbCr & vbCr & “This is an automatically generated message.”
  23.   .Body = strMsg
  24.   .UnRead = True
  25. End With
  26.  
  27. bWasPosted = True
  28.  
  29. NotifyMsg.Send
  30.  
  31. End Sub

After implementing this code, you'll see that it checks incoming mail items for the ones with the appropriate sender and subject, browses through the attachment for errors, and sends an email to your Inbox of choice if an error is found. If no error is found, it quietly marks the email as read and moves it to another folder.

Now, hands off!

Enjoy,
JP



Print This Post Print This Post  |  Email This Post Email This Post  |  Permalink  |  Subscribe to this feed Subscribe now!

Filed Under: Excel, Outlook, VBA, automation
Tags: , , , ,

This post has 313 views since April 14, 2008 – 5:49 pm.

Post a Comment

To post VBA code in your comment, use [VBA] tags, like this: [VBA]Code goes here[/VBA].


Browse Posts:


« Updated blog and new pages | Forwarding Selected Text to Another Email Address »
Subscribe to this feed Entries RSS    Subscribe to this feed Comments RSS               Code For Excel And Outlook                  Theme by Bob                   Disclaimer