So I've been given some very redundant reporting tasks, and I know I can automate them so I don't have to spend hours every month doing them. unfortunately, I just started learning VBA about 2 weeks a go. I can definitely help people with some SQL Server stuff!
Basically, I want to flag specific emails based on the subject, email address. Then save it, open it up in Excel and call a macro to do some formatting so it can be uploaded into a database. After all that is done, save it and email it back to the sender... But I can't seem to get outlook to do ANYTHINGla;sjkdfl;asjkdf
Any help would really be appreciated, here's what I've got so far, hopefully it's just a silly mistake.
Option Explicit
'
'for outlook
'
'Place this code in the "ThisOutlookSession" class module
'
'This code will:
'
'Monitor the inbox
'Check for the existence of a specific kind of email with an attachment
'Save the attachment
'Open the file through Excel
'Format the Data to be uploaded to the database, (or for CareClaim Analyst)
'
'
Private WithEvents olInboxItems As Items
'
'
'Application_Startup() is a reserved function that will automatically
'be called when Outlook starts.
'
Private Sub Application_Startup()
Dim objNS As Outlook.NameSpace
Dim X As Integer
Set objNS = GetNamespace("MAPI")
Set olInboxItems = Session.GetDefaultFolder(olFolderInbox).Items
End Sub
'
'This event is fired when the Inbox receives a new message
'or from manually moving a message from another folder back to
'the inbox folder
'
Private Sub olInboxItems_ItemAdd(ByVal Item As Object)
On Error Resume Next
Dim olMailItem As MailItem
Dim olAttName As String
Dim objNS As Outlook.NameSpace
Dim olDestFldr As Outlook.MAPIFolder
Dim ExcelApp As Object ' Excel.Application
Dim ExcelWK As Object ' Excel.Workbook
Dim Att As String
Set objNS = GetNamespace("MAPI")
If TypeOf Item Is MailItem Then
Set olMailItem = Item
If olMailItem.Attachments.Count = 1 _
And olMailItem.SenderEmailAddress = "[email protected]" _
And InStr(olMailItem.Subject, "this is a test of automation") > 0 Then
'open wb and run import macro
Const AttPath As String = "C:\Users\jvaughn\Desktop\Test"
Set olDestFldr = objNS.Folders("Inbox - *my personal email*").Folders("Inbox").Folders("Test")
' New Excel.Application
Set ExcelApp = CreateObject("Excel.Application")
' save attachment
Set olAttName = olMailItem.Item(1).FileName
olMailItem.Attachments.Item(1).SaveAsFile AttPath + olAttName + Date
' open macro is stored,
' just in case it doesn't open on its own
On Error Resume Next
' open workbook and run macro
ExcelApp.Workbooks.Open (AttPath + olAttName + Date)
ExcelApp.Run (PERSONAL.XLSB!TestingMacro1)
ExcelApp.Workbooks.Close
Kill AttPath & Att
ExcelApp.Quit
' mark as read and move to msgs folder
olMailItem.UnRead = False
olMailItem.Move olDestFldr
End If
End If
End Sub
Bookmarks