29th October 2021
Task at hand: You receive daily reports with attachments, which you need to analyze programmatically. Therefore you want those attachments in your filesystem. You need an Outlook macro, which saves all those attachments to a directory. Outlook VBA to the rescue.
Below macro processes all e-mails which are selected in Outlook. It stores them in
C:\temp. You have to adapt handling for checking the e-mail subject, whether the e-mail in question is actually you expect to contain the report. VBA function
InStr() comes in handy here.
Sub saveAttachment() Dim oAttachment As Outlook.Attachment Dim mItem As Outlook.MailItem Dim sSaveFolder As String, dateFormat As String, partOfFilename As String Dim i As Long, nSelectedMsg As Long sSaveFolder = "C:\temp\" Set oExplorer = Application.ActiveExplorer If oExplorer Is Nothing Then MsgBox "No active explorer", vbOKOnly Exit Sub End If nSelectedMsg = oExplorer.Selection.Count For i = 1 To nSelectedMsg Set mItem = oExplorer.Selection.Item(i) dateFormat = Format(mItem.ReceivedTime, "yymmdd") ' Fiddle with subject-line of e-mail to get parts of filename If InStr(mItem.Subject, "...abc") > 0 Then partOfFilename = "...uvw" Else MsgBox "Macro applied to wrong e-mail", vbCritical Exit Sub End If For Each oAttachment In mItem.Attachments FileName = "DAILY_REPORT_" & partOfFilename & "_" & dateFormat & ".txt" ' oAttachment.DisplayName not needed, as we make our own filename ' MsgBox "filename=" & FileName, vbOKOnly oAttachment.SaveAsFile sSaveFolder & FileName Next Next End Sub
SaveAsFile saves the attachment with date and time as of date of receipt of e-mail. It also overwrites any file with same name.