Save attachment from outlook to a specific folder on computer, sent by a specific sender and save the file (both excel and pdf) with date & time stamp

[vckothari]

We on a daily basis receive emails with attachments from our custodian and that needs to be saved in a folder which represent year say 2021(already created), in the year, which month say Sep21 and in that folder a sub folder which has both monthly and daily. The next is the file name should have the date, time incorporated before its saved. I am fine if the folders cannot be created automatically but if the latter (file) can be saved with all name details in a common folder i a fine. The issue is that the email has both types of file xlsx & pdf and second is getting file name changed while saving. So technically you receive the email, both attachment gets saved in a common folder and (simple one) its renamed with current timings and date.

 

[JEC]

This might help you. "Time" is not allowed in filenames since it contains ":"
So I just changed the name to the date of today.

Note: you run this code from the Excel VBA editor.

Sub fmail()
fpath = "C:\Users\xxx\Downloads\"
 
 With CreateObject("outlook.application").GetNamespace("MAPI").GetDefaultFolder(6)
    For Each it In .items
      If it.Sender = "Name of sender" Then
        For Each att In it.attachments
           att.SaveAsFile fpath & Date & Mid(att, InStr(Len(att) - 6, att, "."))
        Next
      End If
    Next
 End With
End Sub

 

[vckothari]

This might help you. "Time" is not allowed in filenames since it contains ":"
So I just changed the name to the date of today.

Note: you run this code from the Excel VBA editor.

Sub fmail()
fpath = "C:\Users\xxx\Downloads\"
 
 With CreateObject("outlook.application").GetNamespace("MAPI").GetDefaultFolder(6)
    For Each it In .items
      If it.Sender = "Name of sender" Then
        For Each att In it.attachments
           att.SaveAsFile fpath & Date & Mid(att, InStr(Len(att) - 6, att, "."))
        Next
      End If
    Next
 End With
End Sub

Thank buddy..the email will have attachments pdf and excel, will this code cater to both ? Why need to run this code on excel , should it be not from outlook ? I have seen file taking date and time from the system and saving it as name…

 

[JEC]

It saves Excel Files as xlxs and PDF files as .pdf. That is what you want right?
You can change the name of the files as you like. If you use NOW instead of DATE you will get an error because of illegal characters in your filename.

 

[Anthony47]

To "timestamp" the file you could use Format, like in

myVar = Format(Now, "yyyy-mm-dd_hh-mm-ss")

Use myVar instead of Date in your macro

Hth

 

[vckothari]

It saves Excel Files as xlxs and PDF files as .pdf. That is what you want right?
You can change the name of the files as you like. If you use NOW instead of DATE you will get an error because of illegal characters in your filename.

Yes i need to save both pdf and excel as is in format wise. But just confirm why do i need to run from excel and if yes I am clue less how will it trigger evertime that email comes. I will try this code..

 

[vckothari]

Sure

To "timestamp" the file you could use Format, like in

myVar = Format(Now, "yyyy-mm-dd_hh-mm-ss")

Use myVar instead of Date in your macro

Hth

 

[vckothari]

Sure , will check this out