CODEWITHSUNDEEP
stringvbaoutlookextract
Sev09
Sev09

Reputation: 873

VBA script to save attachment as part of subject line for file name

I need a solution to add a VBA script to a rule in Outlook that searches for the word before the phrase "was" in the email subject line, then save the attachment (excel file) using that word in a designated folder.

I'm familiar with the InStr() method, but for whatever reason, I can't figure out how to work it in with the current code. Anyone have any ideas?

This is the current working script I have before adding this new line:

Public Sub saveAttachtoDisk(itm As Outlook.MailItem)
Dim objAtt As Outlook.Attachment
Dim saveFolder As String
Dim saveFolder2 As String
Dim attname As String
Dim fso As FileSystemObject
Dim stream As TextStream


saveFolder = "C:\Users\testuser\Desktop\Files\Reports"
saveFolder2 = "\\SERVER\C\Users\Service\Documents"
    For Each objAtt In itm.Attachments
        objAtt.SaveAsFile saveFolder & "\" & objAtt.DisplayName
        attname = objAtt.DisplayName
        Call updatelog(attname)
        objAtt.SaveAsFile saveFolder2 & "\" & objAtt.DisplayName
        Call updatelog(attname)
        Set objAtt = Nothing
    Next

Thanks

Upvotes: 0

Views: 2066

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33175

Here's a generic function that will return the word preceding a trigger word.

Function WordBeforeTrigger(sInput As String, sTrigger As String) As String

    Dim vaSplit As Variant
    Dim i As Long
    Dim sReturn As String

    vaSplit = Split(sInput, Space(1))

    For i = LBound(vaSplit) + 1 To UBound(vaSplit)
        If vaSplit(i) = sTrigger Then
            sReturn = vaSplit(i - 1)
            Exit For
        End If
    Next i

    WordBeforeTrigger = sReturn

End Function

This is how to use it in the Immediate Window

?wordbeforetrigger("Report1 2012 was run on 7/11/2013 at 11:58:35pm","was")
2012
?wordbeforetrigger("I was home","was")
I
?wordbeforetrigger("","was")

?wordbeforetrigger("doesn't contain","was")

Those blank lines are empty strings. You need to code to account for when the word isn't found and an empty string is returned. You could modify the function to return sInput if Len(sReturn)=0 or something like that.

It appears you want to pass in DisplayName and "was" and it will return the value. You can store that in a string variable and use it as your attachment name.

There's nothing wrong with using Instr, but you'll notice I didn't. I prefer to use Split and navigate the resulting array rather than Instr.

Upvotes: 2

Related Questions