Solved

VBA Scripting within Outlook

Posted on 2014-10-24
6
317 Views
Last Modified: 2014-10-24
The setup:
Within Outlook I have a rule that moves specific emailed reports to 8 different folders pending on the report. Each email has a csv attached. For this purpose we can say the Subject line is Report1, Report2, etc... The sender for all the reports is SIEMTool. The files get moved to a pst labeled "My Outlook Data File" --> Folder "Inbox"--> "SIEMTool" --> "Report1", "Report2", etc.... Sometimes report2 will not have an attachment due to size restrictions.

I would like to create a script that will create a folder at "c:/reports" labeled with the report date "yyyymmdd", then save all the report attachments from the same date into the folder labeled as "report1.csv", "report2.csv", etc...

Sorry for not having a starting point with this. Never coded in Outlook, just excel. I can provide more detail if needed.
0
Comment
Question by:m_travis
  • 3
  • 3
6 Comments
 
LVL 13

Expert Comment

by:Alexei Kuznetsov
ID: 40402460
Here is the script:
Sub SaveReports(Item As MailItem)
    targetDir = "C:\Reports\" & Format(Item.ReceivedTime, "yyyymmdd")
    If Dir(targetDir, vbDirectory) = "" Then
        MkDir targetDir
    End If
    For Each attach In Item.Attachments
        attach.SaveAsFile targetDir & "\" & attach.FileName
    Next
End Sub

Open in new window

Just use it in your Outlook rule (use "run a script" action).

The scripting solution is ok here, but you have to enable scripts in Outlook (reducing security). Also if you have no experience, you may have troubles in supporting the script in the future.

As one of the developers of ReliefJet Essentials for Outlook, I can recommend it for scriptless solution. It provides Save Attachments utility that can be used directly in your rules (use "perform a custom action" and select the utility). You can configure it by specifying "C:\Reports\{DATE#yyyyMMdd}" as a folder to save attachments to and basically that's all. It will automatically save attachments from your incoming messages to the folder on your disk. You can configure additional parameters (like attachment masks and others).
0
 
LVL 1

Author Comment

by:m_travis
ID: 40402482
I wasn't looking at a paid solution. That would be the benefit of VBA.
0
 
LVL 13

Expert Comment

by:Alexei Kuznetsov
ID: 40402513
Then just use the script provided =)
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 1

Author Comment

by:m_travis
ID: 40402614
The scripted worked perfectly, but (there is always a but) I need VBA code for the following psuedo code:
if email.subject contains "xyz" then
- save attachment as "Report1.csv"
else if email subject contains "123" then
- save attachment as "Report2.csv
End if
0
 
LVL 13

Accepted Solution

by:
Alexei Kuznetsov earned 500 total points
ID: 40402671
Sub SaveReports(Item As MailItem)
    attachName = ""
    If InStr(1, Item.Subject, "xyz", vbTextCompare) > 0 Then
        attachName = "Report1.csv"
    ElseIf InStr(1, Item.Subject, "123", vbTextCompare) > 0 Then
        attachName = "Report2.csv"
    Else
        Exit Sub
    End If
    
    targetDir = "C:\Reports\" & Format(Item.ReceivedTime, "yyyymmdd")
    If Dir(targetDir, vbDirectory) = "" Then
        MkDir targetDir
    End If
    
    Item.Attachments.Item(1).SaveAsFile targetDir & "\" & attachName
End Sub

Open in new window

Make sure to add one more condition to your rule: "which has an attachment" because the script doesn't perform any error checks.
0
 
LVL 1

Author Closing Comment

by:m_travis
ID: 40403039
The solution provided covered everything I was asking for. Thank you for the help.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
MS Outlook is a world-class email client application that is mainly used for e-communication globally.  In this article, we will discuss the basic idea about MS Outlook, its advanced features, and types of MS Outlook File formats.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now