Solved: Save attachments with sequence number

[jwise]

I receive multiple emails from a person. Each of these e-mails usually has multiple attached files (all PDF's). Sometimes I receive a file with a duplicate file name. The caveat is that the file contents may be different, so I need to modify the attached file name with a "sequence number" so that I can save these duplicate names. For example, I would like to save "AV MB.PDF" as "AV MB_0001.PDF". I would append the "_nnnn" sequence number to all attached PDF files from this person

I have code that I found on another website that does this via a macro, but it does not save this sequence number anywhere. If I use the macro a second time, it starts back with one.


I have wriiten a few Excel macros, but zero Outlook macros. I found the "UserProperties" object on a MS website, I can't figure out how to create the added user property. Here is the code:
Sub BldSeqNum()
'
' http://msdn2.microsoft.com/en-us/library/aa211095(office.11).aspx
'


Dim myOL As Outlook.Application
Dim nsObj As Outlook.NameSpace
'Dim Inbox As MAPIFolder
'Dim X As New Collection
Dim myItem As Item
Dim myProp As UserProperties


Set myOL = CreateObject("Outlook.Application")
Set nsObj = myOL.GetNamespace("MAPI")
'Set myFolder as olApp.
'Set Inbox = ns.GetDefaultFolder(olFolderInbox)
'Set myItems = nsObj.Folders(

Set myItem = myOL.nsObj.Items(1)

'Set X = myOL.ns.Collection
Set myProp = myItems.UserProperties.Add("PDF_Seq_Num", olNumber)

End Sub




There have been many attempts at this. I just can't figure out how to create the sequence number. TIA

[JKwan]

Try this:

Sub BldSeqNum()
Const olFolderInbox = 6
Dim FileName As String, Extension As String
Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")
Set objFolder = objNamespace.GetDefaultFolder(olFolderInbox)
If ActiveInspector Is Nothing Then 'if message not opened up full screen
Set MailItm = ActiveExplorer.Selection.Item(1)
Else
Set MailItm = ActiveInspector.CurrentItem
End If
If TypeName(MailItm) <> "MailItem" Then
MsgBox "This only works on mail items. Exiting."
Exit Sub
End If
intCount = MailItm.Attachments.Count
If intCount > 0 Then
For i = 1 To intCount
FileName = Left(MailItm.Attachments.Item(i).FileName, InStr(MailItm.Attachments.Item(i).FileName, ".") - 1)
Extension = Right(MailItm.Attachments.Item(i).FileName, 3)
MailItm.Attachments.Item(i).SaveAsFile "C:\Temp\" & _
FileName & "_" & Format(i, "000#") & "." & Extension
Next
End If

End Sub

[jwise]

Thank you for the code.

I see a couple of problems. There is no guarantee that I will receive all these emails on the same day. If I've correctly interpreted your code, then I could receive emails on successive days and wind up with duplicated file names. I believe this sequence number must be stored and retrieved between invocations of Excel to guarantee that I will have no duplicate names. I could add a timestamp to the name; I considered that before but I did not want to make the name so long. The real names used in these files are often very long anyways. That was the reason I tried to use "UserProperties" to create a new variable that Outlook preserved between sessions.

The other problem that I see is that I am actually only doing this against one sender. You did not see that because the code I showed was just concerned with creating this UserProperty variable.

I'm going to try to use this code with my attempt at defining the persistent variable.

[JKwan]

Here is a fix for your SEQUENCE NUMBER, I am using the Registry to keep track of it.

Sub BldSeqNum()
Const olFolderInbox = 6
Dim FileName As String, Extension As String
Dim SeqNum As Long
Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")
Set objFolder = objNamespace.GetDefaultFolder(olFolderInbox)

SeqNum = GetSetting(appname:="OLSequenceNum", Section:="Outlook", Key:="SequenceNum", Default:=1)
If ActiveInspector Is Nothing Then 'if message not opened up full screen
Set MailItm = ActiveExplorer.Selection.Item(1)
Else
Set MailItm = ActiveInspector.CurrentItem
End If
If TypeName(MailItm) <> "MailItem" Then
MsgBox "This only works on mail items. Exiting."
Exit Sub
End If
intCount = MailItm.Attachments.Count
If intCount > 0 Then
For i = 1 To intCount
FileName = Left(MailItm.Attachments.Item(i).FileName, InStr(MailItm.Attachments.Item(i).FileName, ".") - 1)
Extension = Right(MailItm.Attachments.Item(i).FileName, 3)
MailItm.Attachments.Item(i).SaveAsFile "C:\Temp\" & _
FileName & "_" & Format(SeqNum, "000#") & "." & Extension
SeqNum = SeqNum + 1
Next
End If
SaveSetting appname:="OLSequenceNum", Section:="Outlook", Key:="SequenceNum", Setting:=SeqNum
End Sub

[jwise]

I think my problem is "object definitions" I get "type mismatch 13" on the "Set myProp = myItem.UserProperties..." statement.
Dim myOL As Outlook.Application
Dim nsObj As Outlook.NameSpace
Dim Inbox As MAPIFolder
Dim myItem As Object
Dim myProp As UserProperties

Set myOL = CreateObject("Outlook.Application")
Set nsObj = myOL.GetNamespace("MAPI")
Set Inbox = GetObject("", "Outlook.Application").GetNamespace("MAPI"). _
GetDefaultFolder(olFolderInbox)

Set myItem = Inbox.Items(1)

Set myProp = myItem.UserProperties.Add("SeqNum", olNumber)

Set myProp = Nothing
Set myItem = Nothing
Set Inbox = Nothing
Set nsObj = Nothing
Set myOL = Nothing
End Sub




I think my "Dim" statements for "myItem" and myProp" are inconsistent.

When I look at the "Outlook Object Model" in the HELP file, I see that UserProperties is under the Items Collection. I am assuming that what this means is that a UserProperty is something that an Item has. I further assume that the folder (the Inbox in this case) has NO user definable attribute that I can create, access in a later invocation of Outlook, update and save. Is this correct?

[jwise]

This is slick. Putting the sequence number in the regsitry is a great idea, and it will surely be saved between invocations. Thanks!

I'm still curious if the "UserProperty" was a bad idea. I'm having trouble understanding how to navigate down the object model, and the difference in collections and objects. I see the obvious difference in the terms, but I don't see how you put this difference in code. For any collection, can one use the "With" construction or "For each..."?

Thanks again. Very nice solution.

[JKwan]

I am glad that I was able to help.

Unfortunately, in regard to the object model I am not able to help there. Maybe someone else can answer them and I will learn more.

[jwise]

Thanks again for the help.