Reporting. So simple with VBA

reporting 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!


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s