Save email attachment based on email subject

Question

Ever day at 12 am there is an automatic email with an excel attachment from a vendor service with a specific subject. I am using rules and code to attempt to save the attachment and insert the information into a database I have created upon being received in the inbox.

I have tried code that I have found online however I don't know if doesn't work because of some network/ security setting my company has or if its he code it self.

Rule:

CODE:

Public Sub CribMaster2Database(itm As Outlook.MailItem)
Dim objAtt As Outlook.Attachment
Dim saveFolder As String

saveFolder = "c:\temp\"
    If olItem.Subject = "Test" Then
        For Each objAtt In itm.Attachments
            objAtt.SaveAsFile saveFolder & "\" & objAtt.DisplayName
            Set objAtt = Nothing
        Next
    End If

End Sub

Time to learn debugging! How do you run the code? How do you know if the code ran? How do you know you have a problem? What's the problem? Set debug points and use the debugger or add MsgBox statements! But... my first real question is 'how are you getting your macro to run?' — Cody Ｇ, Aug 04 '17 at 13:03
Tun run such macro, depending on your security settings, you have to create a certificate and select it to sign your `VBA` code. Look for `Digital Certificate for VBA Projects` to create the certificate. In `Outlook VBA`, the menu is `Tools/Digital Signature`. Finally, you have to use the `Outlook Rules Wizard` to fire your script on incoming mails. — Axel Kemper, Aug 04 '17 at 13:09
Your save folder is incorrect - you're adding an extra path separator. `saveFolder & "\" & objAtt.DisplayName` - saveFolder already has a final \ so this will show as `c:\temp\\MyFileName.xls` — Darren Bartrup-Cook, Aug 04 '17 at 13:47

score 1 · Accepted Answer · answered Aug 04 '17 at 13:45

1

Add code to the ThisOutlookSession to watch your folder for arrivals.
CribMaster_ItemAdd fires whenever something arrives in your watched folder.

At the very top of the module:

Dim WithEvents CribMaster As Items

Const SAVE_PATH As String = "c:\temp\"

Private Sub Application_Startup()

    Dim ns As Outlook.NameSpace
    Set ns = GetNamespace("MAPI")

    'Change `holi4683` to the name of your account
    '(should be visible just above your inbox).
    Set CribMaster = ns.Folders.Item("holi4683") _
            .Folders.Item("Inbox").Items

End Sub

Sub CribMaster_ItemAdd(ByVal Item As Object)
    Dim olAtt As Attachment
    Dim i As Integer

    With Item
        For i = 1 To .Attachments.Count
            Set olAtt = .Attachments(i)
            olAtt.SaveAsFile SAVE_PATH & olAtt.DisplayName
            .UnRead = False
            DoEvents
        Next i
    End With
    Set olAtt = Nothing

End Sub

I'd usually use a rule to move the emails to a subfolder and watch that folder - means I don't have to worry about meeting invites, etc.
To do this you'd change your watched folder like this:

Set CribMaster = ns.Folders.Item("holi4683") _
        .Folders.Item("Inbox") _
        .Folders.Item("SubFolder").Items

Restart Outlook for the code to work, or manually run the Application_Startup() procedure.

answered Aug 04 '17 at 13:45

Darren Bartrup-Cook

18,362
1
23
45

So I don't use the rule to fire the script anymore? – holi4683 Aug 08 '17 at 11:52
Sorry, can't see the picture of your rule. No, you don't have to use the rule as the code will fire when an item arrives in your inbox. At the moment it will fire for all items, so you may want to set up a rule to move the specific emails into another folder and watch that or check the subject line of emails as they arrive. – Darren Bartrup-Cook Aug 08 '17 at 12:05
Ok, I changed the rule to put the emails into a folder named CribMaster. Now when I run the code I get an error at line 10: `Run time error 438: Object doesn't support this property or method` I have the following there ` Set CribMaster = ns.Folders.Item("name of account").Folders.Item("Inbox").Folders.Item("CribMaster").Items ` – holi4683 Aug 08 '17 at 14:15
Not sure there. You have the code in the `ThisOutlookSession`? Your top level folder is named correctly in the code - probably your email address? What version of Outlook is it? This is written in 2010. – Darren Bartrup-Cook Aug 08 '17 at 14:27
Yes, Yes and 2013 – holi4683 Aug 08 '17 at 14:31
Looking at other posts (https://stackoverflow.com/questions/11151811/reference-a-folder-by-name) you could try `Set CribMaster=ns.GetDefaultFolder(olFolderInbox).Folders("CribMaster").Items` or maybe remove `Item` from the original line (leave the final `Items` in there). – Darren Bartrup-Cook Aug 08 '17 at 14:38
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/151407/discussion-between-holi4683-and-darren-bartrup-cook). – holi4683 Aug 08 '17 at 14:42
Sorry, I can't: `Your request was denied because of its content categorization: "Chat (IM)/SMS;Technology/Internet"` (surprised the whole site hasn't been blocked yet - images are, hell they even blocked images on their own intranet for a bit earlier). – Darren Bartrup-Cook Aug 08 '17 at 14:46
Ok changed that line and it finishes that line of code along with the sub, what is supposed to fire the second sub? – holi4683 Aug 08 '17 at 14:46
Drag an email into the folder and it should fire. – Darren Bartrup-Cook Aug 08 '17 at 14:47
Ok it works, Idk why it doesn't recognized the rule moving it but it does fire when I manually move it. Thank you! – holi4683 Aug 08 '17 at 14:58
That's weird. It should recognise it however it's moved in there. I'll have to look into that. – Darren Bartrup-Cook Aug 08 '17 at 15:15

Save email attachment based on email subject

1 Answers1