CDO Add Attachment And Change Name Of File

[Mavyak]

I need to add attachments to multiple emails. The data regarding the attachments are stored in a database table. The primary key for the table is the file_id. The file_id is also the name of the file on my hard drive. Also in the table is a more descriptive name for the file. I need to add the file as an attachment, but I need to give it the more descriptive file name, "Resume.doc" as opposed to the name as it's stored on my hard drive, "217965.doc". Here is my code:



With m
'Add attachments
r3.Open "SELECT [DIRECTORY] + CONVERT(VARCHAR(50), [File_Id]) + [file_Extension], [NAME] FROM Attachments WHERE Email_ID = " & strEmailId, c, adOpenForwardOnly, adLockOptimistic
While Not r3.EOF
If fso.FileExists(base_path & r3.Fields(0)) Then
Set NewAttachment = .Attachments.Add
'NewAttachment.Type = CdoFileData
NewAttachment.ReadFromFile base_path & r3.Fields(0)
NewAttachment.Source = base_path & r3.Fields(0)
NewAttachment.Name = r3.Fields(1)
End If
r3.MoveNext
Wend
r3.Close
.Update
End With
Even though I'm setting the Name property of the NewAttachment object to the more descriptive value from my query, the attachment added to the email has the file_id as its name.

Am I missing something?

Mavyak


Edit:
I should point out that m is a Message object, NewAttachment is a MAPI.Attachment object, c is an ADODB.Connection object, r3 is an ADODB.Recordset object, strEmailid is a string variable, and base_path is a string variable.

[Mavyak]

Bump.

[Slyboots]

The only way I know of to do this, is to first save the file to disk. Once you've set it as an attachment, you're only renaming the attachment OBJECT, NOT the file that will be saved by the user.

[Mavyak]

I was afraid of that. I've got upwards of 15,000 files. Looks like my process to create a *.pst file from database records just doubled (or tripled!) in time.

[Mavyak]

Since all the files were named <record_id> + <file_extension>, I just used a FileSystemObject to rename the file to the more descriptive name before I added it as an attachment. Immediately after adding it as an attachment, I used the same FSO to change the name back to the record_id value.