JORDJB999
06-03-2008, 09:00 AM
Hi people,
i'll be honest from the start. I know nothing about vba! i have found most of this code on the web and pretty much copy & pasted it together, ive solved most of my problems, through trial and error but this one i just cant seem to figure it out. So i'm after some expert advice :)
Basically ive got this code in outlook and it save's the csv attachments in a folder, opens,applies an excel macro, saves again, and thats it. The thing is it works perfectly once but not after that unless i shut down outlook an re open it. When i got into debug the section at the bottom about the save as time & date is highlighted. Im using outlook an excel 2007. Any help would be much aprieciated.
'expose the items in the target folder to events
Dim WithEvents TargetFolderItems As Items
Private Sub Application_Startup()
'some startup code to set our "event-sensitive" items collection
Dim ns As Outlook.Namespace
Set ns = Application.GetNamespace("MAPI")
Set TargetFolderItems = ns.Folders.Item("Personal Folders").Folders.Item("Temp").Items
Set ns = Nothing
End Sub
Private Sub TargetFolderItems_ItemAdd(ByVal Item As Object)
'when a new item is added to our "watched folder" we can process it
Dim olAtt As Attachment
'you may want to run some tests on the item/attachment(s)
If Item.Attachments.Count = 1 Then
Set olAtt = Item.Attachments(1)
olAtt.SaveAsFile "C:\temp\" & olAtt.Filename
End If
'pass the filepath to the print routine
PrintAtt ("C:\temp\" & olAtt.Filename)
Set olAtt = Nothing
End Sub
Sub PrintAtt(file As String)
Dim xlApp As Object
Dim wb As Object
'in the background, create an instance of xl then open, print, quit
Set xlApp = CreateObject("Excel.Application")
On Error Resume Next
xlApp.Workbooks.Open _
("C:\Program Files\Microsoft Office\Office12\XLSTART\opips.XLSM")
On Error GoTo 0
xlApp.Workbooks.Open (file)
xlApp.Run ("opips.XLSM!stat2")
ActiveWorkbook.SaveAs _
Filename:="C:\Documents and Settings\JORDAN\My Documents\Opips Orders\online_order_" _
+ Format(Date, "dd-mm-yy") + "_" + Format(Time, "hh-mm-ss"".csv")
ActiveWorkbook.Save
ActiveWorkbook.Close savechanges:=False
xlApp.Quit
'tidy up
Set wb = Nothing
Set xlApp = Nothing
End Sub
~VBA Tags added by Oorang
.
i'll be honest from the start. I know nothing about vba! i have found most of this code on the web and pretty much copy & pasted it together, ive solved most of my problems, through trial and error but this one i just cant seem to figure it out. So i'm after some expert advice :)
Basically ive got this code in outlook and it save's the csv attachments in a folder, opens,applies an excel macro, saves again, and thats it. The thing is it works perfectly once but not after that unless i shut down outlook an re open it. When i got into debug the section at the bottom about the save as time & date is highlighted. Im using outlook an excel 2007. Any help would be much aprieciated.
'expose the items in the target folder to events
Dim WithEvents TargetFolderItems As Items
Private Sub Application_Startup()
'some startup code to set our "event-sensitive" items collection
Dim ns As Outlook.Namespace
Set ns = Application.GetNamespace("MAPI")
Set TargetFolderItems = ns.Folders.Item("Personal Folders").Folders.Item("Temp").Items
Set ns = Nothing
End Sub
Private Sub TargetFolderItems_ItemAdd(ByVal Item As Object)
'when a new item is added to our "watched folder" we can process it
Dim olAtt As Attachment
'you may want to run some tests on the item/attachment(s)
If Item.Attachments.Count = 1 Then
Set olAtt = Item.Attachments(1)
olAtt.SaveAsFile "C:\temp\" & olAtt.Filename
End If
'pass the filepath to the print routine
PrintAtt ("C:\temp\" & olAtt.Filename)
Set olAtt = Nothing
End Sub
Sub PrintAtt(file As String)
Dim xlApp As Object
Dim wb As Object
'in the background, create an instance of xl then open, print, quit
Set xlApp = CreateObject("Excel.Application")
On Error Resume Next
xlApp.Workbooks.Open _
("C:\Program Files\Microsoft Office\Office12\XLSTART\opips.XLSM")
On Error GoTo 0
xlApp.Workbooks.Open (file)
xlApp.Run ("opips.XLSM!stat2")
ActiveWorkbook.SaveAs _
Filename:="C:\Documents and Settings\JORDAN\My Documents\Opips Orders\online_order_" _
+ Format(Date, "dd-mm-yy") + "_" + Format(Time, "hh-mm-ss"".csv")
ActiveWorkbook.Save
ActiveWorkbook.Close savechanges:=False
xlApp.Quit
'tidy up
Set wb = Nothing
Set xlApp = Nothing
End Sub
~VBA Tags added by Oorang
.