Hi Everyone,
I will try my best to describe my situation to you. I am not a developer but an MIS Analyst
Every Morning I come in and I receive 5 files, each in their own email, in my outlook Inbox.
I want to automate this process as our department has discussed automations with the source of the emails but they want to charge us an arm and a leg to do it. These discussions happened in 2010, and this process has been part of my morning ever since. My work load increases but this feels like it gets in my way of things. Please help. I'd like to press one button and this should just happen.
The following is My Process Every Morning:
- Go to directory Department/Department Section/Work/February 2015/
- If todays date is the 13th of February 2015
- Create a Folder in .../February 2015/ for the previous day, naming it - '20150212'
I need the solution to create these folders every morning before i get in the office at 8am.
NOTE: That if the month changes I need to create the month folder as well 'February 2015', 'March 2015', etc.
Go to Outlook
- Open email 1, save 'file 1' in 20150212, close email. Open email 2, save 'file 2' in 20150212, close email. Open email 3, save 'file 3' in 20150212, close email. Open email 4, save 'file 4' in 20150212, close email. Open email 5, save 'file 5' in 20150212, close email.
- Delete all mails
I need the solution to automatically save the files in the newly created folder above and they need to identify the new month as well.
NOTE: The Files also get sent on the Saturday, and when I come in on Monday I usually have to save it for the date of Friday.
Go to Folder 20150212
- Open 'file 3'
- Delete First Row, Delete Hidden Column A, Delete Row that is immediately under the last row record.
- Format the now Current Column A (Select whole column, Format Cells, General, and in the given text box Insert 0000000000, Click OK).
- Save file as 'file 3 20150212' and save as a text (Tab delimited) file. Warning comes up of features may not be supported etc asking if you want to continue saving? Select Yes. Close file.
- Open 'file 5'
- Delete First Row, Delete Hidden Column A, Delete Row that is immediately under the last row record.
- Go to the last column which has data, usually Column AL but may be subject to change, and format (Select whole column, Format Cells, Select Number, Click OK)
- Save file as 'file 5 CompanyName 20150212' and save as a text (Tab delimited) file. Warning comes up of features may not be supported etc asking if you want to continue saving? Select Yes. Close file.
The solution needs to be able to do this automatically and also identify the next day and add the appropriate date.
My Process is much longer but if you could just assist me with a solution for this I would be overwhelmed with appreciation to you. Please assist.
Sincerely
Seraaj
Please dont have a TLDR moment. I really would appreciate a solution with fine tuned note of all the detail :)
I know there is someone in here who's brain is exploding with knowledge on matters of this degree.
I look forward to your response.
Loading
Seraaj IsaacsPosted Feb 17, 2015, 6:23 AM
So Far I've managed to get the files to save in a specific folder but it still needs a bit of fine tuning.
I still need the following:
- For my VBA to check whether folder exists on the Drive or server e.g "February 2015". If folder exists create within the folder another folder in the following format yyyymmdd, specifying the previous day. so if today is 20150217, the folder's name should be "20150216".
- Once folder is created, save files in that created folder.
I also require code to delete the mail items in the subfolder afterwards.
This code works in Outlook 2010 for those who want to use it :)
___________________________________________________________
Sub GetAttachments_final() 'saves excel file in specified outlook folder on specfied drive location
On Error GoTo GetAttachments_err
Dim ns As NameSpace 'gives access to outlook folders (MAPI)
Dim Inbox As MAPIFolder 'name of the folder
Dim Item As Object
Dim Atmt As Attachment 'attachment object
Dim FileName As String 'name and save path
Dim i As Integer
Dim SubFolder As MAPIFolder
'set values of the variables
Set ns = GetNamespace("MAPI")
Set Inbox = ns.Folders("Mailbox - Mailbox Name").Folders("Inbox")
i = 0
Set SubFolder = Inbox.Folders("Sub Folder Name") 'sub folder to search
'search folder for attachments
If SubFolder.Items.Count = 0 Then
MsgBox "There are no messages in the Sub folder." _
, vbInformation, "Nothing Found"
Exit Sub
End If
'examine each attachment in folder - only saves emails with excel attachments
If SubFolder.Items.Count > 0 Then
For Each Item In SubFolder.Items
For Each Atmt In Item.Attachments
If Right(Atmt.FileName, 3) = "xls" Then
FileName = "\\Serversample\sample folder\another folder\another one\Analysis\" & _
Format(Item.CreationTime, "yyyymmdd") & Atmt.FileName 'saves with file creation time as unique identifer on server
'FileName = "\\Serversample\sample folder\another folder\another one\Analysis\" & Item.EntryID & Atmt.FileName 'includes file unique indentifier
' FileName = "\\Serversample\sample folder\another folder\another one\Analysis\" &
Atmt.FileName 'location to save file
Atmt.SaveAsFile FileName
i = i + 1
End If
Next Atmt
Next Item
'summary measures of actions
If i > 0 Then
MsgBox "I found " & i & " attached files." _
& vbCrLf & "I have saved them into the Analysis folder" _
& vbCrLf & vbCrLf & "Have a nice day.", vbInformation, "Finished!"
Else
MsgBox "I didn't find any attached files in your mail.", vbInformation, _
"Finished!"
End If
'clears the memory
GetAttachments_exit:
Set Atmt = Nothing
Set Item = Nothing
Set ns = Nothing
Exit Sub
'dealing with errors
GetAttachments_err:
MsgBox "An unexpected error has occurred." _
& vbCrLf & "Please note and report the following information." _
& vbCrLf & "Macro Name: GetAttachments" _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Error Description: " & Err.Description _
, vbCritical, "Error!"
Resume GetAttachments_exit
End If
End Sub
_________________________________________________________________
Feel like I'm talking to myself here :( ladies and gents please help