Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.1k views
in Technique[技术] by (71.8m points)

vba - Save attachment from an email in to a folder that changes every month

i 'm trying to get a VBA macro in Outlook that will save an email's attachment to a specific folder (that changes every month) and add the YYYYMM of prior month received to the file name.

The outlook rule identifies that an email header contains 'NTMR' from a person.

And when it does so, it runs the script where it saves the attachment in a folder.

So when the macro identifies the month of the email received, it saves in the folder that is one month behind. For instance:

email received on DD/04/17 as 'Here is the NTMR file for you', it will save the file in a folder within 201703 parent folder as NTMR - 201703

So the path of the file will be C:UsersalitalhDownloadsTest201703Source FilesNTMR 201703

I have come up with the follownig macro - please advise as To how i can fix it?

Public Sub saveAttachtoDisk(itm As Outlook.MailItem)
    Dim objAtt As Outlook.Attachment
    Dim saveFolder As String
    Dim dateFormat As String
    saveFolder = "C:UsersalitalhDownloadsTest"
    dateFormat = Format(Now, "yyyymm" - 1, 1)

    For Each objAtt In itm.Attachments
        objAtt.SaveAsFile saveFolder & "" & dateFormat & "" & "Source Files" & "" & objAtt.DisplayName & dateFormat
        Set objAtt = Nothing
    Next
End Sub

how can i strip out the NTMR from the header and put it in the filename?

I have another macro that sets up the folder prior to the email so we don't need to create another folder

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Work with MSDN Split Function

Example on your Subject line Here is the NTMR file for you split it by space character (" ")

Code Example

Sub Example()
    Dim Item As Outlook.mailitem

    Set Item = ActiveExplorer.Selection.Item(1)

    Debug.Print Item.subject ' Print on Immediate Window (Ctrl+G)

    Item.subject = Split(Item.subject, " ")(3)

    Debug.Print Item.subject ' Print on Immediate Window (Ctrl+G)

End Sub

Your subject = (Here)(1) (is)(2) (the)(3) (NTMR)(4) (file)(5) (for)(6) (you)(7)

Now Split(subject line), "space")(3) While assigning to string variable

Dim FileName As String
FileName = Split(Item.subject, " ")(3)

Replace objAtt.DisplayName with FileName


Dim FileName As String
For Each objAtt In itm.Attachments
    objAtt.SaveAsFile saveFolder & "" & _
                       dateFormat & "" & _
                    "Source Files" & "" & FileName & dateFormat
Next

By default, or when Limit equals -1, the Split function splits the input string at every occurrence of the delimiter string, and returns the substrings in an array.
When the Limit parameter is greater than zero, the Split function splits the string at the first Limit-1 occurrences of the delimiter, and returns an array with the resulting substrings.
For example, Split("a:b:c", ":") returns the array {"a", "b", "c"},
while Split("a:b:c", ":", 2) returns the array {"a", "b:c"}.


To get previous month try DateAdd Function

Example

Option Explicit
Public Sub Example()
    Dim PrevMonth As String

    PrevMonth = Format(DateAdd("m", -1, Date), "yyyymm")
    Debug.Print PrevMonth
End Sub

Some useful date functions worth exploring in other contexts include DateDiff, DatePart, DateSerial, Day, Month, Year, and IsDate. IsDate (which checks whether a string is a valid date) is particularly useful for things like UserForms where you may want to force the user to type a valid date into a certain textbox.



与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...