Solved

VBA Scripting within Outlook

Posted on 2014-10-24
6
330 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Mailbox Overload?
If you troubleshoot Outlook for clients, you may want to know a bit more about the OST file before doing your next job. IMAP can cause a lot of drama if removed in the accounts without backing up.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

705 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