Automating Saves Imports Links and Runs Queries

Question

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.

Seraaj Isaacs · Answer

Hi Everyone 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

Automating Saves, Imports, Links and Runs Queries