Integration of outlook with excel VBA

**Nanimadhu** · 02-02-2014, 07:53 AM

Hello Experts,

I'm in search of a VBA macro which will send a mail through outlook automatically with the count of files(.pdf + .msg files) in any given folder.

Let me explain my requirement.

I work for an organization, where we use Outlook for mailing purpose.

Everyday Inventory(.pdf files and .msg files) will be saved in a folder that is to be processed.

I want a macro which should automatically send the total inventory (.pdf files + .msg files) through my outlook mailbox(say: me@outlook.com) to a my superiors outlook mailbox(say: superior@outlook.com).

If the macro sends that email to my superior before me and my superior logon to computers at perticular time (say: 7:00AM daily), it will be of great help to plan our day at work.

Any help will be appreciated.

**jaslake** · 02-02-2014, 05:34 PM

Hi Nanimadhu

How will the Code know this...how or where is it defined?

in any given folder

**Leith Ross** · 02-03-2014, 08:36 PM

Hello Nanimadhu,

This macro should be a good starting point. It will let you browse for the Inventory folder. Once you have selected the folder, only the PDF and MSG files are copied to a Zip folder. The email subject message shows the count of each of the file types in the attached zipped folder. You will need to change the mail address to the one you want to use as "myboss@work.com" is not a real address. I have heavily commented code to make it easier to understand. Try this out and let me know if this is close to what you wanted.

Sub EmailInventory()
  
    Dim cnt         As Long
    Dim FileCnt(1)  As Long
    Dim FileFilter  As Variant
    Dim FolderPath  As Variant
    Dim Item        As Variant
    Dim MailAddress As String
    Dim MsgToSend   As String
    Dim n           As Variant
    Dim nByte       As Variant
    Dim oFiles      As Object
    Dim oFolder     As Object
    Dim olApp       As Object
    Dim oShell      As Object
    Dim ZipFile     As Variant
    Dim ZipFolder   As Variant
    Dim ZipHeader() As Byte
    

      ' Written: February 03, 2014
      ' Author:  Leith Ross

      ' Change this email address to whom the email will be sent.
        MailAddress = "myboss@work.com"
        
      ' Browse for the inventory folder.
        Set oShell = CreateObject("Shell.Application")
        Set FolderPath = oShell.BrowseForFolder(0, "Browse for Folder", &H50, 17)
        
      ' Check if "Cancel" button was clicked.
        If FolderPath Is Nothing Then Exit Sub
        
      ' Name of the Zip folder to hold files to email.
        ZipFolder = Environ("TEMP") & "\Inventory"
        
      ' Create the folder if it does not exist.
        If Dir(ZipFolder, vbDirectory) = "" Then MkDir ZipFolder
      
      ' Remove any previously saved files in the folder.
        For Each Item In oShell.Namespace(ZipFolder).Items
            Kill Item.Path
        Next Item
        
      ' Zip file path and name.
        ZipFile = Environ("TEMP") & "\Inventory_" & Hex(Timer) & ".zip"
        
          ' Create an empty Zip file using the EOCD (End Of Central Record) signature. Minimum Zip file size is 22 bytes.
            ReDim ZipHeader(21)
            ZipHeader(0) = 80: ZipHeader(1) = 75: ZipHeader(2) = 5: ZipHeader(3) = 6
            
            Open ZipFile For Binary Access Write As #1
                Put #1, , ZipHeader
            Close #1

          ' Open the selected folder.
            Set oFolder = oShell.Namespace(FolderPath)
            Set oFiles = oFolder.Items
                    
          ' Add these files to the Zip folder.
            For Each FileFilter In Array("*.pdf", "*.msg")
                oFiles.Filter 64, FileFilter
                FileCnt(cnt) = oFiles.Count: cnt = cnt + 1
                
                For n = 0 To oFiles.Count - 1
                    oShell.Namespace(ZipFolder).CopyHere oFiles.Item(n), 84
                Next n
            Next FileFilter
            
          ' Zip all the files in the folder.
            If Application.Sum(FileCnt) > 0 Then
                oShell.Namespace(ZipFile).CopyHere ZipFolder
            End If
            
        MsgToSend = "Inventory File Counts are as follows:" _
                    & vbCrLf & "PDF files = " & FileCnt(0) & vbCrLf _
                    & "MSG files = " & FileCnt(1)
            
      ' Create the email and send it.
        Set olApp = CreateObject("Outlook.Application")
        olApp.Session.GetDefaultFolder (6)
        
            With olApp.CreateItem(0)
                .To = MailAddress
                .Subject = "Inventory Totals"
                .Body = MsgToSend
                .Attachments.Add ZipFile, 1
                .Send
            End With

      ' Delete the zip folder and files.
        Kill ZipFile
        
End Sub

**Nanimadhu** · 02-08-2014, 08:27 AM

Thank you Leith Ross,

I'm looking for same kind of macro, but I need some editions to this macro to achieve what I'm actually looking for.

Apologies that I dint mentioned all these details in post.

Here are the editions am looking for,

1. Path from where it has to extract the count of .msg and .pdf files is "T:\inventory\(today's date)" : Macro should automatically take today's date(mm-dd-yyyy) as the target folder from which it has to extract the count of .msg and .pdf files.

Eg: T:\inventory\2-8-2014

2. Macro should automatically run and send mail when that today's date(mm-dd-yyyy) folder is created by other team for our processing.

3. Macro should automatically run and send mail even when I don't log-in to computer. That means, email has to be sent even I'm absent to office.

4. I would not need Zip file attached with email.

5. If the body of email consists of Folder name (here, 2-8-2014), it will be helpful.

Please let me know if you need any more clarifications and once again thanks for helping me out.