Solved

VBA Scripting within Outlook

Posted on 2014-10-24
6
312 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
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!

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Follow this checklist to learn more about the 15 things you should never include in an email signature from personal quotes, animated gifs and out-of-date marketing content.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
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 …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

746 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

8 Experts available now in Live!

Get 1:1 Help Now