Outlook VBA script to save file with subject as filename
Good day. New member here
Long story short:
I would like a script that grabs the attachments from a incoming mails and saves them in a specified folder with the subject as the filename.
The bigger picture:
I regularly recieve mails with attached pdf files. The pdf filename is always called "Vedlegg" ("attachement" in norwegian) and the subject field is always "ARBEIDSORDRE 589492/2012 ,VH35682 ,FORHÅNDSVIS" except for the numbers being different each time. I work in a car workshop and these pdf's are work-orders containing a description of what to repair on the different cars. Thus the numbers will be different on each mail, but I want them to be automatically stored with the subject field as the filename.
Also, I am aware that "/" can not be used in filenames. So I would like something that inserts a space instead and also maybe deletes the first and last word "ARBEIDSORDRE" and "FORHÅNDSVIS" as those are of no interest when I later want to search for it.
I have fund and tried several scripts that did similar to this, but none that does it excactly the way I'd want to.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: Outlook VBA script to save file with subject as filename
Thank you for the quick reply, but I wouldn't know where to put that line as I have no knowledge of VBA or any programming at all. My job is with automobilenetworking and hardware
I am using this today (which does almost what I want):
Sub SaveAllAttachments(objitem As MailItem)
Dim objAttachments As Outlook.Attachments
Dim strName, strLocation As String
Dim dblCount, dblLoop As Double
strLocation = "K:\Ny mappe struktur\Servicemarked\Volkswagen og Audi verksted\Gjennomganger - Teksteark\Jack\"
On Error GoTo ExitSub
If objitem.Class = olMail Then
Set objAttachments = objitem.Attachments
dblCount = objAttachments.Count
If dblCount <= 0 Then
GoTo 100
End If
For dblLoop = 1 To dblCount
strID = " from " & Format(Date, "mm-dd-yy")
strName = objAttachments.Item(dblLoop).Filename 'Get attachment name
strExt = Right$(strName, 4) 'Store file Extension
strName = Left$(strName, Len(strName) - 4) 'Remove file Extension
strName = strName & strID & strExt 'Reattach Extension
strName = strLocation & strName
objAttachments.Item(dblLoop).SaveAsFile strName
Next dblLoop
'objitem.Delete
End If
100
ExitSub:
Set objAttachments = Nothing
Set objOutlook = Nothing
For dblLoop = 1 To dblCount
strName = strLocation & Replace(Mid(objitem.subject, 13, Len(fn) - 25), "/", " ") & ".pdf"
objAttachments.Item(dblLoop).SaveAsFile strName
Next dblLoop
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: Outlook VBA script to save file with subject as filename
Tried it, but it seemed to do just the same as the other one (couldn't see any difference).
My new code now looks like this:
Sub SaveAllAttachments(objitem As MailItem)
Dim objAttachments As Outlook.Attachments
Dim strName, strLocation As String
Dim dblCount, dblLoop As Double
strLocation = "K:\Ny mappe struktur\Servicemarked\Volkswagen og Audi verksted\Gjennomganger - Teksteark\Jack\"
On Error GoTo ExitSub
If objitem.Class = olMail Then
Set objAttachments = objitem.Attachments
dblCount = objAttachments.Count
If dblCount <= 0 Then
GoTo 100
End If
For dblLoop = 1 To dblCount
strName = strLocation & Replace(Mid(objitem.Subject, 13, Len(fn) - 25), "/", " ") & ".pdf"
objAttachments.Item(dblLoop).SaveAsFile strName
Next dblLoop
'objitem.Delete
End If
100
ExitSub:
Set objAttachments = Nothing
Set objOutlook = Nothing
Re: Outlook VBA script to save file with subject as filename
but it seemed to do just the same as the other one (couldn't see any difference).
post sample of the outputted filenames
and objitem.subject
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: Outlook VBA script to save file with subject as filename
Sub SaveAllAttachments(objitem As MailItem)
i would assume that objitem should have a subject property
if not you have to return the subject from some property of objitem, else you can not build the filename from it
Code:
msgbox objitem.subject
as i do not have outlook installed i can not easily check the properties of mail ltems
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: Outlook VBA script to save file with subject as filename
Should I write this line in somewhere you mean
yes on a line on its own, anywhere in the procedure, near the top is probably best
should present a box with the subject of the email
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: Outlook VBA script to save file with subject as filename
I pasted that line in near the top and now I do get a popup with the subject and an "OK" button on it. Still doesn't autosave the file with subject as name though. My new code looks like this:
Sub SaveAllAttachments(objitem As MailItem)
MsgBox objitem.Subject
Dim objAttachments As Outlook.Attachments
Dim strName, strLocation As String
Dim dblCount, dblLoop As Double
strLocation = "K:\Ny mappe struktur\Servicemarked\Volkswagen og Audi verksted\Gjennomganger - Teksteark\Jack\"
On Error GoTo ExitSub
If objitem.Class = olMail Then
Set objAttachments = objitem.Attachments
dblCount = objAttachments.Count
If dblCount <= 0 Then
GoTo 100
End If
For dblLoop = 1 To dblCount
strID = " from " & Format(Date, "mm-dd-yy")
strName = objAttachments.Item(dblLoop).Filename 'Get attachment name
strExt = Right$(strName, 4) 'Store file Extension
strName = Left$(strName, Len(strName) - 4) 'Remove file Extension
strName = strName & strID & strExt 'Reattach Extension
strName = strLocation & strName
objAttachments.Item(dblLoop).SaveAsFile strName
Next dblLoop
'objitem.Delete
End If
100
ExitSub:
Set objAttachments = Nothing
Set objOutlook = Nothing
Re: Outlook VBA script to save file with subject as filename
Still doesn't autosave the file with subject as name though. My new code looks like this:
that is because you are not using the subject when you create the strname to save the file as
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: Outlook VBA script to save file with subject as filename
Would you know how the code should look like to do that?
see post #4, that should be the correct code, put a msgbox strname before thesavAs line, to make sure the file path\name is exactly how you want it
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: Outlook VBA script to save file with subject as filename
I didnt find a line called "savAs" so I put it over what seemed similar (at the very top), but this didn't do it either. My new code now looks like this:
NB: I'm aware that I most likely has missunderstood what you ment here?!
MsgBox strName
Sub SaveAllAttachments(objitem As MailItem)
Dim objAttachments As Outlook.Attachments
Dim strName, strLocation As String
Dim dblCount, dblLoop As Double
strLocation = "K:\Ny mappe struktur\Servicemarked\Volkswagen og Audi verksted\Gjennomganger - Teksteark\Jack\"
On Error GoTo ExitSub
If objitem.Class = olMail Then
Set objAttachments = objitem.Attachments
dblCount = objAttachments.Count
If dblCount <= 0 Then
GoTo 100
End If
For dblLoop = 1 To dblCount
strName = strLocation & Replace(Mid(objitem.Subject, 13, Len(fn) - 25), "/", " ") & ".pdf"
objAttachments.Item(dblLoop).SaveAsFile strName
Next dblLoop
'objitem.Delete
End If
100
ExitSub:
Set objAttachments = Nothing
Set objOutlook = Nothing
Re: Outlook VBA script to save file with subject as filename
My new code now looks like this:
does it work? what happens? it looks like it should be correct
I didnt find a line called "savAs"
i meant like
Code:
strName = strLocation & Replace(Mid(objitem.Subject, 13, Len(fn) - 25), "/", " ") & ".pdf"
msgbox strname ' just to see it the path\filename lloks like it is correct
objAttachments.Item(dblLoop).SaveAsFile strName
your code would be better if you avoided the goto 100 as it is not needed
for dblloop =1 to objAttachments.Count will skip if count is any value < 1 if a message has more than 1 attachment each will overwrite the previous
if any error occurs, it will go to your error handler, but without telling you, so you can not know which parts of the code have not been processed
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: Outlook VBA script to save file with subject as filename
Good morning and sorry for not elaborating, I forgot my cofee )
No, it didn't do anything at all - not even saving the attachment. If I understood you correctly above I have now pasted in and removed the different lines and ended up with this code (but it doesn't do anything - not even saving the attachment):
MsgBox strName
Sub SaveAllAttachments(objitem As MailItem)
Dim objAttachments As Outlook.Attachments
Dim strName, strLocation As String
Dim dblCount, dblLoop As Double
strLocation = "K:\Ny mappe struktur\Servicemarked\Volkswagen og Audi verksted\Gjennomganger - Teksteark\Jack\"
On Error GoTo ExitSub
If objitem.Class = olMail Then
Set objAttachments = objitem.Attachments
dblCount = objAttachments.Count
If dblCount <= 0 Then
End If
For dblLoop = 1 To dblCount
strName = strLocation & Replace(Mid(objitem.Subject, 13, Len(fn) - 25), "/", " ") & ".pdf"
MsgBox strName ' just to see it the path\filename lloks like it is correct
objAttachments.Item(dblLoop).SaveAsFile strName
Next dblLoop
'objitem.Delete
End If
100
ExitSub:
Set objAttachments = Nothing
Set objOutlook = Nothing
Re: Outlook VBA script to save file with subject as filename
but it doesn't do anything - not even saving the attachment):
so you get no mesagebox?
i am not sure with outlook if the attachments collection starts at 1, just in case try changing the loop to for dblloop = 0 to dblcount - 1
i can not test the code to see why it fails, as i do not have outlook installed, but if the messagebox does not show the loop is never running
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: Outlook VBA script to save file with subject as filename
No messagebox and no saving the attachement even when I changed the line you said. However, when I looked more closely at the VBA script in alt+f11 I noticed that the MsgBox strName didn't seem to be a part of the code... It had some kind of line separating it from the rest of the code, and when I moved it under the line "Sub SaveAllAttachments(objitem As MailItem)" and sent myself a mail with attachement I get this error:
---------------------------
Microsoft Visual Basic
---------------------------
Compile error:
Duplicate declaration in current scope
---------------------------
OK Help
---------------------------
And it highlights this line in yellow:
Sub SaveAllAttachments(objitem As MailItem)
My current code looks like this:
Sub SaveAllAttachments(objitem As MailItem)
MsgBox strName
Dim objAttachments As Outlook.Attachments
Dim strName, strLocation As String
Dim dblCount, dblLoop As Double
strLocation = "K:\Ny mappe struktur\Servicemarked\Volkswagen og Audi verksted\Gjennomganger - Teksteark\Jack\"
On Error GoTo ExitSub
If objitem.Class = olMail Then
Set objAttachments = objitem.Attachments
dblCount = objAttachments.Count
If dblCount <= 0 Then
End If
For dblLoop = 1 To dblCount
strName = strLocation & Replace(Mid(objitem.Subject, 13, Len(fn) - 25), "/", " ") & ".pdf"
MsgBox strName ' just to see it the path\filename lloks like it is correct
objAttachments.Item(dblLoop).SaveAsFile strName
Next dblLoop
'objitem.Delete
End If
100
ExitSub:
Set objAttachments = Nothing
Set objOutlook = Nothing
Re: Outlook VBA script to save file with subject as filename
I get this error:
that is because you used the variable strname, above where it is dimensioned, it should be below all the dim lines
as strname has no value at that point you would only get an empty messagebox
but at leas you can tell the code runs to that point
you need to put breakpoints in the code, or step through the code line by line to find what is actually happening
put a breakpoint at the line (click in the left margin, a red dot should appear)
For dblLoop = 1 To dblCount
the code should then break when it gets to that line, will appear highlighted in yellow
you can then step through the code on line at a time (F8), to see where it goes
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: Outlook VBA script to save file with subject as filename
Thanks for your time and effort here! I moved some lines around but I still get the grey line under MsgBox strName thingy. I managed to click on some red dots in there, and tried to run the code with and without those red dots, but no change. Now the code doesn't seem to do nothing, not even saving the attachement.
My new code looks like this:
Dim objAttachments As Outlook.Attachments
Dim strName, strLocation As String
Dim dblCount, dblLoop As Double
MsgBox strName
_________________________________________________________ (strange grey line)
Sub SaveAllAttachments(objitem As MailItem)
strLocation = "K:\Ny mappe struktur\Servicemarked\Volkswagen og Audi verksted\Gjennomganger - Teksteark\Jack\"
On Error GoTo ExitSub
If objitem.Class = olMail Then
Set objAttachments = objitem.Attachments
dblCount = objAttachments.Count
If dblCount <= 0 Then
End If
For dblLoop = 1 To dblCount
strName = strLocation & Replace(Mid(objitem.Subject, 13, Len(fn) - 25), "/", " ") & ".pdf"
MsgBox strName ' just to see it the path\filename lloks like it is correct
objAttachments.Item(dblLoop).SaveAsFile strName
Next dblLoop
'objitem.Delete
End If
100
ExitSub:
Set objAttachments = Nothing
Set objOutlook = Nothing
End Sub
Re: Outlook VBA script to save file with subject as filename
all you code must be within the procedure
Code:
_________________________________________________________ (strange grey line)
Sub SaveAllAttachments(objitem As MailItem)
Dim objAttachments As Outlook.Attachments
Dim strName, strLocation As String
Dim dblCount, dblLoop As Double
MsgBox strName ' should be empty messagebox as strname is empty at this point
strLocation = "K:\Ny mappe struktur\Servicemarked\Volkswagen og Audi verksted\Gjennomganger - Teksteark\Jack\"
On Error GoTo ExitSub
If objitem.Class = olMail Then
Set objAttachments = objitem.Attachments
dblCount = objAttachments.Count
If dblCount <= 0 Then
End If ' put breakpoint on next line, then step through code to see if it enters loop
For dblLoop = 1 To dblCount
strName = strLocation & Replace(Mid(objitem.Subject, 13, Len(fn) - 25), "/", " ") & ".pdf"
MsgBox strName ' just to see it the path\filename lloks like it is correct
objAttachments.Item(dblLoop).SaveAsFile strName
Next dblLoop
'objitem.Delete
End If
100
ExitSub:
Set objAttachments = Nothing
Set objOutlook = Nothing
End Sub
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: Outlook VBA script to save file with subject as filename
There I got the empty pop-up back! )
However it doesn't save the attachement. And I'm not sure what you ment with "put breakpoint on next line, then step through code to see if it enters loop" (yes I do feel like an idiot when it comes to this)
Re: Outlook VBA script to save file with subject as filename
And I'm not sure what you ment with "put breakpoint on next line
see post #20
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: Outlook VBA script to save file with subject as filename
Ok, tried my best here. Clicked in the margin in front of each line and tried to run the script again. It takes me to the script editor and I'm able to run the script line by line with F8. It seems to go through the lines one by one, but it skips those lines I've highlighted in red down here:
Sub SaveAllAttachments(objitem As MailItem)
Dim objAttachments As Outlook.Attachments
Dim strName, strLocation As String
Dim dblCount, dblLoop As Double
MsgBox strName ' should be empty messagebox as strname is empty at this point
strLocation = "K:\Ny mappe struktur\Servicemarked\Volkswagen og Audi verksted\Gjennomganger - Teksteark\Jack\"
On Error GoTo ExitSub
If objitem.Class = olMail Then
Set objAttachments = objitem.Attachments
dblCount = objAttachments.Count
If dblCount <= 0 Then
End If ' put breakpoint on next line, then step through code to see if it enters loop
For dblLoop = 1 To dblCount
strName = strLocation & Replace(Mid(objitem.Subject, 13, Len(fn) - 25), "/", " ") & ".pdf"
MsgBox strName ' just to see it the path\filename lloks like it is correct
objAttachments.Item(dblLoop).SaveAsFile strName
Next dblLoop
'objitem.Delete
End If
100
ExitSub:
Set objAttachments = Nothing
Set objOutlook = Nothing
End Sub
Re: Outlook VBA script to save file with subject as filename
despite the fact that you did not highlight the line
strName = strLocation & Replace(Mid(objitem.Subject, 13, Len(fn) - 25), "/", " ") & ".pdf"
i assume that was skipped too
now try changing the loop to start at zero instead of 1, see post #18
in fact, how about rereading all the previous posts?
you do not need a break point on every line, once the code stops you can step through line by line, until you press the run button (or F5)
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: Outlook VBA script to save file with subject as filename
Sorry for being a complete newbie here )
The line "strName = strLocation & Replace(Mid(objitem.Subject, 13, Len(fn) - 25), "/", " ") & ".pdf"" gets highlighted and doesn't seem to be skipped. Then I read through and tried to change the lines in the order of the posts and ended up with this code. The empty pop-up is therem but no attachements are saved:
Sub SaveAllAttachments(objitem As MailItem)
Dim objAttachments As Outlook.Attachments
Dim strName, strLocation As String
Dim dblCount, dblLoop As Double
MsgBox strName ' should be empty messagebox as strname is empty at this point
strLocation = "K:\Ny mappe struktur\Servicemarked\Volkswagen og Audi verksted\Gjennomganger - Teksteark\Jack\"
On Error GoTo ExitSub
If objitem.Class = olMail Then
Set objAttachments = objitem.Attachments
dblCount = objAttachments.Count
If dblCount <= 0 Then
End If ' put breakpoint on next line, then step through code to see if it enters loop
For dblLoop = 0 To dblCount - 1
strName = strLocation & Replace(Mid(objitem.Subject, 13, Len(fn) - 25), "/", " ") & ".pdf"
MsgBox strName ' just to see it the path\filename lloks like it is correct
objAttachments.Item(dblLoop).SaveAsFile strName
Next dblLoop
'objitem.Delete
End If
100
ExitSub:
Set objAttachments = Nothing
Set objOutlook = Nothing
End Sub
the error may have just caused VBA to terminate, without warning, though that is unusual
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: Outlook VBA script to save file with subject as filename
Anyone else have any good tips to me on this script? Thank you for great help this far though westconn1 - even though I don't know anything about what a code is you managed to make me understand most of what you said )
Re: Outlook VBA script to save file with subject as filename
where have you got to now?
i am not sure why you are still having a problem, as there is enough information for it to work with only minor changes
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: Outlook VBA script to save file with subject as filename
Then someone would kindly have to explain that to me, because I can't understand anything from this. I have no knowledge in anything related to computer coding. If this was an issue with a vehicle I'd nail it though )
Re: Outlook VBA script to save file with subject as filename
Then someone
looks like you are stuck with me, as you have had no other responses, many of the people who used to do outlook coding, in this forum, appear to have moved on to elsewhere
to help any further i need to know what is going on in your code, which i can not test, did you try changing the line as in post #28
when you step through the code, at which line does the macro stop?
when you write code you use indenting, as in a small example in post #4
when you post here use code tags and the formatting will display correctly, this makes it so much easier for anyone reading your code
If this was an issue with a vehicle I'd nail it though
if you can help me diagnose problems with late model CR diesels i would consider it a good trade
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: Outlook VBA script to save file with subject as filename
Hey there. Good new years to you! I've been busy over the holidays so please excuse my late reply. Regarding my script I've given up on it, but maybe I could ask for more details around your CR diesel problem out of curiosity. Could you try to describe what you are experiencing to me, and please include the car make/model and year?
Re: Outlook VBA script to save file with subject as filename
Could you try to describe what you are experiencing to me, and please include the car make/model and year?
unfortunately they are many, varied and ongoing, some i fix, others i run out of guesses
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete