Solved: Txt File to Folder

[Justinlabenne]

I have never done any macros using Outlook, I am drawing a blank on getting started on my question. I have a text file sent to me daily that i have to save in my work directory everyday. I just overwrite the same text file daily. I currently do this manually, is there a code I could run to automatically save this file in the same spot every day? Thanks, any help to get started is appreciated.

[Killian]

hi,

the code below will check each file that arrives in your inbox to see if it has a single attachment with a specified name. In the outlook VBE, select the Application.Item_Add event and paste it in. With outlook, you have use it in a class module and initialize the namespace (highlight ItemAdd and hit F1 for info on this). My Outlook isn't hooked up at the moment so I cant test it but I think it should work if you to put in the appropriate values :-)

You may also want to add a message box or form to let you know it's happenned and do something with the mail item afterwards

Enjoy :-)

[VBA]
Private Sub myOlItems_ItemAdd(ByVal i As Object)
Dim myOlMItem As Outlook.MailItem
Dim MY_TEXT_FILE As String
Dim MY_FILE_PATH As String


MY_TEXT_FILE = "test.txt"
MY_FILE_PATH = "C:\temp\"

Set myOlMItem = myOlItems.Item(i)
With myOlMItem.Attachments
If .Count = 1 Then
If .Item(1).FileName = MY_TEXT_FILE Then
.Item(1).SaveAsFile (MY_FILE_PATH & MY_TEXT_FILE)
End If
End If

End Sub
[/VBA]

[Justinlabenne]

Thanks, my Outlook is at work also, I will give this a shot tonight and test it out. Ill post again if I run into any questions. Thanks again.

[Killian]

hold on, now I look at it again, that's not right.
the argument in the procedure is an mailItem object, not the index of one like I thought, so you can just go right in and use it:
[VBA]
Private Sub myOlItems_ItemAdd(ByVal i As Object)

Dim MY_TEXT_FILE As String
Dim MY_FILE_PATH As String

MY_TEXT_FILE = "test.txt"
MY_FILE_PATH = "C:\temp\"

With i.Attachments
If .Count = 1 Then
If .FileName = MY_TEXT_FILE Then
.SaveAsFile (MY_FILE_PATH & MY_TEXT_FILE)
End If
End If

End Sub
[/VBA]

or something like that...

K :-)

[Justinlabenne]

I am assuming the

is an mailItem object, not the index of one

means that Mailitem object is a txtfile or stand alone file?, and then the "Index of one" is a e-mail msg with an attachment. Is this correct? Oh, and thanks again Killian.

[Killian]

No, sorry, I'm not being clear.

The procedure declaration:
Private Sub myOlItems_ItemAdd(ByVal i As Object)
means the 'i' refers to a MailItem object - the new mail item that fires the ItemAdd event

don't worrk abount the index thing, that was just me being dumb

K :-)

[Justinlabenne]

No prob, thanks for your help and your time, you might hear back from me tomorrow if i run into trouble, later.

[Justinlabenne]

Not sure how to call this or invoke it, I pasted this code into a class module, but I have no idea how to get it to run when I am ready to send the txt file to the folder. I tried removing the "Private" and placing this in a standard module to call it, but so far I have had no luck with initial testing phase, any direction on what I am missing? Here is the code with the file name in place:

[vba]Private Sub myOlItems_ItemAdd(ByVal i As Object)

Dim MyText_File As String
Dim MyFile_Path As String

MyText_File = "eiprc02@.p ETNa 34.8.6 Prod. Rptg Summary by Shift Rpt.txt"
MyFile_Path = "H:\"

With i.Attachments
If .Count = 1 Then
If .FileName = MyText_File Then
.SaveAsFile (MyFile_Path & MyText_File)
End If
End If
End With
End Sub[/vba]

[Killian]

yes, quite a lot missing from my first post - must have been a half asleep
you need this in the class module before your code to set the relevant references:

[vba]Dim myOlApp As New Outlook.Application
Public WithEvents myOlItems As Outlook.Items[/vba]

And if you place this:
[vba]Set myOlItems = myOlApp.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Items[/vba]

in you Application_Startup event to get a refeence to the application, things should start to happen... I would think
(you'll have run that startup event or restart the app first)

Hope it works.... K:-)

[Justinlabenne]

Sorry for the delay; Here is what I have but I keep getting the error (OBJECT REQUIRED)

In "ThisOutlookSession" code module:

[VBA] Private Sub Application_Startup()
Set myOlItems = myOlApp.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Items
End Sub
[/VBA]

In a standard code module:

[VBA] Private Sub myOlItems_ItemsAdd(ByVal i As Object)
Dim MyText_File As String
Dim MyFile_Path As String
MyText_File = "eiprc02@.p ETNa 34.8.6 Prod. Rptg Summary by Shift Rpt.txt"
MyFile_Path = "H:\"
With i.Attachments
If .Count = 1 Then
If .FileName = MyText_File Then
.SaveAsFile (MyFile_Path & MyText_File)
End If
End If
End With
End Sub
[/VBA]

In a class module:

[VBA] Dim myOlApp As New Outlook.Application
Public WithEvents myOlItems As Outlook.Items
[/VBA]


Not sure were its wrong or right or anything at all even, Thanks for taking the time Killian, appreciate your help, I dont know what's missing here.

[Killian]

Hi again,
I have access to Outlook today so I've had a look and got this working...

In my class module I have this:[VBA]Public WithEvents myOlItems As Outlook.Items[/VBA]
This makes the outlook items accessible when an event fires

In "ThisOutlookSession" (select in the project explorer), select the Application_Startup event. Add the 2 lines of code to make it look like this:
[VBA]Private Sub Application_Startup()
Dim myOlApp As New Outlook.Application
Set myOlItems = myOlApp.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Items
End Sub[/VBA]
Then, still in "ThisOutlookSession", select the Application_ItemSend event and amend to look like this:
[VBA]Private Sub Application_ItemSend(ByVal i As Object, Cancel As Boolean)
Dim MyText_File As String
Dim MyFile_Path As String
MyText_File = "eiprc02@.p ETNa 34.8.6 Prod. Rptg Summary by Shift Rpt.txt"
MyFile_Path = "H:\"
If i.Attachments.Count = 1 Then
If i.Attachments(1).FileName = MyText_File Then
i.Attachments(1).SaveAsFile (MyFile_Path & MyText_File)
End If
End If
End Sub[/VBA]
If you then close Outlook (save the project when prompted) then restart and send yourself the attachment, It should work... at least it does for me

Good luck

[Justinlabenne]

Appreciate it again Killian, when I get to work tonight , I will test it out. Thanks again.

[Justinlabenne]

I've got it all in but it still doesn't run at all, tried switching the files, that it looked for to an excel attachment, different txt files etc... not really sure what the issue is. On a side note: The txt file isnt actually an attachment, it is e-mailed from our database straight to me, when opening the mail item, it auto opens the file, it's not actually attached to the mail, it is the mail item? Any way, I m not to worried about it, I did a little digging in the KB here, and found this code by DRJ, to move selected items to an outlook folder, possibly anyway to modify this to send to a folder on my network. Thnks for all your help again Killian, not sure what my issue is:

Code from DRJ:

[VBA]
Sub MoveItems()
' Move selected items to the "Saved Items" folder
Dim Messages As Selection
Dim Msg As MailItem
Dim NamSpace As NameSpace
Dim Proceed As VbMsgBoxResult

Set NamSpace = Application.GetNamespace("MAPI")
Set Messages = ActiveExplorer.Selection

If Messages.Count = 0 Then
Exit Sub
End If
For Each Msg In Messages
' Flag status:
' Complete:=1
' Marked :=2
' Nothing :=0
If Msg.FlagStatus = 0 Then
Proceed = MsgBox("Are you sure you want to move the message " _
& """" & Msg & """" & " to the folder " & """" & "Saved Items" & """" & "?", _
vbYesNo + vbQuestion, "Confirm Procedure")
If Proceed = vbYes Then
Msg.FlagStatus = olNoFlag
Msg.Move NamSpace.Folders("Personal Folders").Folders("Saved Items")
End If
End If
Next
End Sub
[/VBA]

Not sure how to modify for my network folders (Network Drive is K:\) but this works exactly how I would like it, if it could be changed, Apologies for the changes to the post.

[Killian]

The code from DRJ moves selected emails to an Outlook folder, "Saved Items" in "Personal Folders". It is possible to use this code to save the email to a network folder by changing the line:
Msg.Move NamSpace.Folders("Personal Folders").Folders("Saved Items")
to:
Msg.SaveAs "K:\SavedMessages", olMSG

(Make sure you have a folder, K:\SavedMessages, or what ever you put in here)

That should do it...

[Justinlabenne]

Killian, you are awesome, I only tested it once but, it went right through, so far, so good, Thanks for all your help, if I run into any more problems with this I will post back. Thanks again.