I have to prepare a daily, weekly, monthly, quarterly report… This is what managers do. Why? Why writing reports is more important than leading people or solving problems or proposing ideas?
Reports are important but in most cases reporting can be automated. We have to stop for a while and start working smart.
- Can we talk about my idea?
- Unfortunately I have to prepare a report. I have no time.
- What report?
- I have to prepare a financial statements. Download time logs from our experts, copy and paste them from hundreds of e-mails. Horrible…
- Why don’t you use your brain and computer for doing all these things?
Let’s look at an example of an application that downloads attachments and move e-mails from Inbox to a separate folder. Here I will describe a procedure that may be used to download attachments from our e-mails.
Sub DownloadAttachments(oMail As Outlook.MailItem, Output As String) For Each Atmt In oMail.Attachments FileName = Output & Atmt.FileName Atmt.SaveAsFile FileName Next Atmt End Sub Sub MailProcessing Dim ns As NameSpace Dim Inbox As MAPIFolder Dim Item As Object Dim Atmt As Attachment Dim FileName As String Dim Output As String Output = "D:\Attachments" Set ns = GetNamespace("MAPI") Set Inbox = ns.GetDefaultFolder(olFolderInbox) ' No messages = finish If Inbox.Items.Count = 0 Then MsgBox "There are no messages in the Inbox.", vbInformation, _ "Nothing Found" Exit Sub End If ' For each email in Inbox For Each Item In Inbox.Items If TypeOf Item Is Outlook.MailItem Then Dim oMail As Outlook.MailItem: Set oMail = Item DownloadAttachments oMail, Output ' Here you could process an attachment or email End If Next Item End Sub
VBA language gives us a very simple method to loop through our e-mails and attachments. We could process them using For Each loop. It is possible to merge hundreds of attachments and send the result further. It is possible to merge e-mails from specified sender or domain.
I need a day for writing a macro that processes e-mails automatically. Doing it manually takes about 2 hours. I need to prepare 365 reports a year and many others similar reports. I could reuse my macro. It is worth it!