Brian,
Here is the full code as it stands. It was posted above the snipet and a few other comments above.
For now any code actions should only be performed on the 1st Sender & Subject combination 2 and 3 are only testing that the attachment is saved on receipt from those 2 Sender & Subject.
But - Upon receipt of an valid Sender & Subject combination (i.e. conditions met for email 2 or 3) my code/actions/process that is supposed to be specific to email 1 kicks off.
In other words my code is not distinguishing when it should run and when it should not based on the email received.
I hope that makes sense
Here is what mode is actually doing:
[vba]
- Save the attachment to the designated folder.
- Calling code from PERSONAL.XLSB!TA_Unzip and unzipping the attachment (As I know it will be in a zip file).
- Saving the unzipped file to a new folder.
- Close the Excel Personal Wkbook.
- Opening Access 2010.
- Importing the "saved file" (original attachment).
- Running the macro "Report Process" which is a series of macros that creates an Excel report with multiple worksheets.
- Programatically emails the reports to designated receipients (Access 2010 through Outlook 2010 with help from ClickYES).
- Send completion email to my self for verification
- Close Access
- Mark the original email as read.
Private WithEvents Items As Outlook.Items
Option Explicit
Private Sub Application_Startup()
Dim olApp As Outlook.Application
Dim objNS As Outlook.NameSpace
Set olApp = Outlook.Application
Set objNS = olApp.GetNamespace("MAPI")
Set Items = objNS.GetDefaultFolder(olFolderInbox).Items
End Sub
Private Sub Items_ItemAdd(ByVal Item As Object)
Dim Msg As Outlook.MailItem
Dim attPath As String
Dim Att As String
Dim myAttachments As Attachments
Dim XLApp As Object ' Excel.Application
Dim appAccess As Object ' Access.Application
Dim XlWK As Object ' Excel.Workbook
Dim boolDownload As Boolean
boolDownload = False
On Error Goto ErrorHandler
'Only act if it's a MailItem
If TypeName(Item) = "MailItem" Then
Set Msg = Item
If Msg.Sender = "Doe, Jane" And Msg.Subject = "Test1" Then
attPath = "G:\Daily\TA\"
boolDownload = True
ElseIf Msg.SenderEmailAddress = "someone@gmail.com" And Msg.Subject = "Test2" Then
attPath = "G:\Daily\TA\"
boolDownload = True
ElseIf Msg.Sender = "Doe, John" And Msg.Subject = "Test3" Then
attPath = "G:\Daily\TA\"
boolDownload = True
End If
If boolDownload = True Then
' open wkbk and run import macro
Dim olDestFldr As Outlook.MAPIFolder
' New Excel.Application
Set XLApp = CreateObject("Excel.Application")
' save attachment
Set myAttachments = Item.Attachments
Att = myAttachments.Item(1).DisplayName
myAttachments.Item(1).SaveAsFile attPath & Att
' open personal.xls where macro is stored, and run macro
On Error Resume Next
XLApp.Workbooks.Open ("C:\Documents and Settings\gregory.l.young\Application Data\Microsoft\Excel\XLSTART\PERSONAL.XLSB")
On Error Goto 0
XLApp.Run ("PERSONAL.XLSB!TA_Unzip")
XLApp.Workbooks.Close
Kill attPath & Att
XLApp.Quit
' Get a reference to the Access Application object.
Set appAccess = CreateObject("Access.Application")
' open TA database and build reports with timer pause to allow time to run
Dim tim As Long
appAccess.OpenCurrentDatabase ("G:\Daily\TA\TA.accdb")
tim = Timer
Do While Timer < tim + 2
DoEvents
Loop
' hide the application.
appAccess.Visible = False
appAccess.DoCmd.RunMacro "Report Process"
' Close the database and quit Access
'appAccess.CloseCurrentDatabase
'appAccess.Quit
' Close the object variable.
Set appAccess = Nothing
' mark as read and move to msgs folder
Msg.UnRead = False
'Msg.Move olDestFldr
End If
End If
ProgramExit:
Exit Sub
ErrorHandler:
MsgBox Err.Number & " - " & Err.Description
Resume ProgramExit
End Sub
[/vba]