?
Solved

VBA Scripting within Outlook

Posted on 2014-10-24
6
Medium Priority
?
335 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 14

Expert Comment

by:Alexei Kuznetsov (Outlook MVP)
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 14

Expert Comment

by:Alexei Kuznetsov (Outlook MVP)
ID: 40402513
Then just use the script provided =)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 14

Accepted Solution

by:
Alexei Kuznetsov (Outlook MVP) earned 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article will help to fix the below errors for MS Exchange Server 2013 I. Certificate error "name on the security certificate is invalid or does not match the name of the site" II. Out of Office not working III. Make Internal URLs and Externa…
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…
Suggested Courses

765 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