VBA in Outlook.

[TFCJamieFay]

Hi all,

I have a marco in MS Outlook that has a variable called "Atmt". This the filename of an email attachment (ie. "MyWorkbook.xls"). How do I extract all the letters up to the ".XLS" bearing in mind the filename length will vary? Also, sometimes the file may be a ".CSV" file, so I need to remove this as well.

Many thanks guys,

Jay

 

[Richard Schollar]

Hi Jay

MsgBox Left$(Atmt,InstrRev(Atmt,".")-1)

 

[TFCJamieFay]

Thanks for your quick response Richard, but I keep getting an error message:

Run Time Error "5" - Invalid procedure call or argument.

This is a section of the code:

If SubFolder.Items.Count > 0 Then
For Each Item In SubFolder.Items
For Each Atmt In Item.Attachments
If Right(Atmt.FileName, 3) = "xls" Or Right(Atmt.FileName, 3) = "csv" Then
'fName = Left$(Atmt, InStrRev(".", Atmt) - 1)
FileName = "C:\Email Attachments\" & fName & Item.EntryID & _
Format(Item.CreationTime, "yyyymmdd_hhnnss_") & Atmt.FileName
Atmt.SaveAsFile FileName
MsgBox Left$(Atmt, InStrRev(".", Atmt) - 1)
i = i + 1
End If
Next Atmt
Next Item
End If

It errors out on the MsgBox line. I inserted it there just to see what it was returning. What I want to do is assigned the new shorter filename to the variable called "fName" and then use this as the first item when creating the FileName.

Thanks again.

 

[Richard Schollar]

You need to access the filename property of the Atmt object:

Dim strFileName As String
 
strFileName = Atmt.FileName
 
'then process strFileName:
 
MsgBox Left(strFileName,InstrRev(strFileName,".")-1)

However, you have already confirmed that the filename contains xls or csv - you if you already know this, you just need to remove the last 4 characters of the filename.

 

[DonkeyOte]

or use REPLACE

=REPLACE(REPLACE(string,".csv",""),".xls","")

 

[TFCJamieFay]

Thanks Richard, that works perfectly!!