Open File From Outlook to Excel and Save as Different Format Depending on Sender

Question

I use Stack Overflow a lot but this is my first post. I know just enough to be dangerous with VBA.

I originally wrote this piece of code for Outlook - its original purpose was to rename any attachment file and save it in a specific directory (I still need that functionality for the one person who sends me files denoted below as email@email.com).

Now I have more than one person sending files and need to modify the script to determine who the sender of the file is and (I know one sender always sends the attachment as an Excel XLSX file but I need it as a CSV) open the XLSX file in excel and save it as a pure CSV.

Obviously my method isn't working and I can't find any cases similar to what I'm trying to do on Stack Overflow. Is anyone willing to help me figure this out? Many thanks to everyone for all your help!

This is what I have now but my If statement doesn't appear to be working...

Public Sub saveAttachtoDisk(itm As Outlook.MailItem)
Dim objAtt As Outlook.Attachment
Dim saveFolder As String
Dim saveFolder2 As String
Dim dateFormat


dateFormat = Format(Now, "yyyy-mm-dd H-mm")
saveFolder = "c:temp1"
saveFolder2 = "c:\temp2"

' CASE 1

If objAtt.SenderName = "Sender's First & Last Name" Then
For Each objAtt In itm.Attachments
    ' open excel
    Workbooks.Open (objAtt)
   ' save as csv to queue directory for upload to FTP site
   ActiveWorkbook.SaveAs FileName:=saveFolder2 & "\" & dateFormat & ".csv",FileFormat:=CSV, CreateBackup:=False
   ActiveWorkbook.Saved = True
   ActiveWindow.Close
      Set objAtt = Nothing
End If

' CASE 2

If objAtt.SenderName = "email@email.com" Then
For Each objAtt In itm.Attachments
      objAtt.SaveAsFile saveFolder & "\" & dateFormat & "FC.csv"
      Set objAtt = Nothing
 Next
End If

End Sub

After David's modifications/suggestions, the code looks like this:

Hi @DavidZemens! Thank you so much for your well thought out answer and for pointing out the issues; your method makes a lot of sense to me. I have reconfigured the code with your suggestions, and I am getting a "Runtime Error 91 - Object Variable or With block variable not set" error which highlights the first line of my "If" statement. Can you identify what I might be doing incorrectly to get this error?

Option Explicit
Public Sub saveAttachtoDisk(itm As Outlook.MailItem)
Dim objAtt As Outlook.Attachment
Dim saveFolder As String
Dim saveFolder2 As String
Dim dateFormat
Const xlCSV As Long = 6
Dim xlsxPath As String
Dim wb As Object
Dim oExcel As Object
Set oExcel = CreateObject("Excel.Application")

dateFormat = Format(Now, "yyyy-mm-dd H-mm")
saveFolder = "c:\temp1"
saveFolder2 = "c:\temp2"

'CASE 1

If objAtt.SenderName = "John Smith" Then
   xlsxPath = saveFolder2 & "\" & dateFormat & ".xlsx"
   objAtt.SaveAsFile xlsxPath
   ' use excel to open and save the file as csv
   Set wb = oExcel.Workbooks.Open(xlsxPath)
   wb.SaveAs FileName:=Replace(xlsxPath, ".xlsx", ".csv"), FileFormat:=xlCSV
   wb.Close
   oExcel.Quit
End If

'CASE 2

If objAtt.SenderName = "email@email.com" Then
    For Each objAtt In itm.Attachments
          objAtt.SaveAsFile saveFolder & "\" & dateFormat & ".csv"
          Set objAtt = Nothing
 Next
End If

End Sub

After the most recent suggestions this is the new code with a new error

The error it gives me when a new email comes in is that the array is out of bounds and highlights the line with that says: Set objAtt = itm.Attachments(0)

Public Sub saveAttachtoDisk(itm As Outlook.MailItem)
Dim objAtt As Outlook.Attachment
Dim saveFolder As String
Dim saveFolder2 As String
Dim dateFormat
Const xlCSV As Long = 6
Dim xlsxPath As String
Dim wb As Object
Dim oExcel As Object
Set oExcel = CreateObject("Excel.Application")


dateFormat = Format(Now, "yyyy-mm-dd H-mm")
saveFolder = "c:\temp1"
saveFolder2 = "c:\temp2"
xlsxPath = saveFolder2 & "\" & dateFormat & ".xlsx"

**'Case 1**

If itm.SenderName = "John Smith" Then
If itm.Attachments.Count > 0 Then  <-- note: I had this as <> and had same   error
    Set objAtt = itm.Attachments(0)
Else: GoTo EarlyExit
End If
End If

objAtt.SaveAsFile xlsxPath
'## Use excel to open and save the file:
Set wb = oExcel.Workbooks.Open(xlsxPath)
wb.SaveAs FileName:=Replace(xlsxPath, ".xlsx", ".csv"), FileFormat:=xlCSV
wb.Close
'## Get rid of the XLSX version if it's no longer needed
On Error Resume Next
Kill xlsxPath
On Error GoTo 0

EarlyExit:
oExcel.Quit

**' Case 2**

If itm.SenderEmailAddress = "email@email.com" Then
For Each objAtt In itm.Attachments
      objAtt.SaveAsFile saveFolder & "\" & dateFormat & "FranklinCounty.csv"
      Set objAtt = Nothing
 Next
End If

*Obviously my method isn't working* <-- No, this isn't obvious. What's the specific problem. Is there an error? I see probably a few, but need you to confirm what line raises error, and what specific error, etc. — David Zemens, Mar 21 '17 at 18:15
Damon if you've attempted my answer, feel free to revise your question with the current code you're attempting, I'll take a look later tonight — David Zemens, Mar 21 '17 at 21:07
Hi David, sorry I'm getting used to the stack overflow UI for editing. My edits are above. :) Many thanks again. — Damon McCall, Mar 21 '17 at 21:08
you need to `Set objAtt = itm.Attachments(0)` -- otherwise your objAtt is nothing. also, you'll need to check to see whether `itm` *has* attachments, otherwise you'll get a subscript out of range error — David Zemens, Mar 21 '17 at 23:15
@DavidZemens I am finally back to working on this. I'm going to paste the most recent code in the original post above with my comments. — Damon McCall, Mar 25 '17 at 05:29
That did it! Everything seems to be working now, thank you so much for your help!! :) — Damon McCall, Mar 27 '17 at 02:18

David Zemens · Accepted Answer · 2017-03-27T13:31:00.660

This is an error:

Workbooks.Open (objAtt)

Because Open method expects a string file path, not an Outlook.Attachment object.

Also, because I don't see any early-bound reference to the Excel object model, so you can probably expect a compile error: User-defined type not defined on the Workbooks.Open line. You'll need to create an object to hold Excel Application:

Dim oExcel as Object
Set oExcel = CreateObject("Excel.Application")

Further, your variable CSV is not declared, nor assigned any value, so that will most likely raise another error if you get the code to compile.

'## Require explicit declaration of Excel constants, unless you're using early-binding 
Const xlCSV as Long = 6

NOTE: Using Option Explicit at the top of your code modules will prevent you from writing hacky code with undeclared variables, unenumerated constants, typos in variable names, etc.

Since you can't use Workbooks.Open on an Attachment, first, you want to Save the attachment to disk, then use Excel to open the saved file (from disk), then you can use the SaveAs to save it as different format. This will result in duplicate files (one XLSX, and one CSV), you can use the Kill statement on the one you don't want to keep.

Dim xlsxPath As String
Dim wb as Object 'Excel.Workbook
xlsxPath = saveFolder2 & "\" & dateFormat & ".xlsx"
'## This assumes the file will always be XLSX format

'## get a handle on your mail item:
If itm.Attachments.Count <> 0 Then
    Set objAtt = itm.Attachments(1)
Else: Goto EarlyExit
End If
objAtt.SaveAsFile xlsxPath
'## use Excel to open and save the file:
Set wb = oExcel.Workbooks.Open(xlsxPath)
wb.SaveAs FileName:=Replace(xlsxPath, ".xlsx", ".csv"), FileFormat:=xlCSV
wb.Close
'## Get rid of the XLSX version if it's no longer needed
On Error Resume Next
Kill xlsxPath
On Error GoTo 0

Then, quit Excel before your End Sub:

EarlyExit:
oExcel.Quit()
End Sub

Open File From Outlook to Excel and Save as Different Format Depending on Sender

1 Answers1