Avatar of Afzal Khan
Afzal KhanFlag for United States of America

asked on 

Need to save the .xls file as .xlsx without opening it through VBA code in outlook

Need help -

How to save the .xls file as .xlsx without opening it:

Below code is working properly and saving files but need to change the extension while saving .



Sub SaveLatestAttachment()
Dim olApp As Outlook.Application
Dim oMail As Outlook.MailItem
Dim NS As Outlook.NameSpace
Dim oFolder As Outlook.Folder
Dim SaveInFolderName As String
Dim SaveInFolder As String
Dim subFolderName As String
Dim strFile As String
Dim Item As Object
Dim Items As Outlook.Items
Dim x()
Dim i As Long

SaveInFolderName = CreateObject("WScript.Shell").SpecialFolders(16)
subFolderName = "EmailAttachments"
SaveInFolder = SaveInFolderName & "\" & subFolderName & "\"

Set olApp = New Outlook.Application
Set NS = olApp.GetNamespace("MAPI")
Set oFolder = NS.GetDefaultFolder(olFolderInbox).Parent.Folders("Test")
ReDim x(1 To oFolder.Items.Count, 1 To 4)
Set Items = oFolder.Items
Items.Sort "[ReceivedTime]", True
For Each Item In Items
    If TypeOf Item Is Outlook.MailItem Then
        Set oMail = Item
        If LCase(oMail.Subject) = "my daily report" Then
            If oMail.Attachments.Count > 0 Then
                For i = 1 To oMail.Attachments.Count
                    strFile = oMail.Attachments(i).FileName
                    If InStr(LCase(strFile), "my report") > 0 Then
                        MsgBox oMail.ReceivedTime
                        strFile = SaveInFolder & strFile
                        On Error Resume Next
                        Kill strFile
                        On Error GoTo 0
                        oMail.Attachments(i).SaveAsFile strFile
                        GoTo ExitSub
                    End If
                Next i
            End If
        End If
    End If
Next Item
ExitSub:
Set olApp = Nothing
MsgBox "Task Completed Successfully.", vbInformation
End Sub

Open in new window

VB ScriptOutlookVBA

Avatar of undefined
Last Comment
Subodh Tiwari (Neeraj)
Avatar of David Favor
David Favor
Flag of United States of America image

Or you can use a single command...

libreoffice --headless --convert-to output_file_extension[:output_filter_name] [--outdir output_dir] file

Open in new window


To convert to all manner of formats.

https://ask.libreoffice.org/en/question/2641/convert-to-command-line-parameter/ provides details.

Libreoffice runs under Java, or really JRE, so runs on all Operating Systems, including Windows.
Avatar of Afzal Khan
Afzal Khan
Flag of United States of America image

ASKER

Thanks Dave but its not working in VBA outlook
Avatar of Qlemo
Qlemo
Flag of Germany image

Sorry, that won't work that easily, AFzal. You cannot just change the extension of a file and expect it to work flawlessly afterwards. There is a reason the new Excel format is named XLSX, because it is totally different from the XLS format used before. XLSX is in fact a ZIP file containing a lot of XML.

So you  have to  open and save the file in Excel or something capable of converting from XLS to XLSX. E.g. you could use your code to save as XLS, then call David's command with ShellExecute to convert to XLSX (if using LibreOffice is feasible for you). Or use Excel Automation instead, to control Excel via Outlook VBA.
ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Afzal Khan
Afzal Khan
Flag of United States of America image

ASKER

Thanks Subodh , the code is working but one query how to run macros automatically when new email arrives in "Test" folder .
I am using below code in "ThisOutlookSession" but it is checking all incoming emails in outlook , just need to check "Test" folder only.

Private Sub Application_NewMail()
    Call SaveAttachments
End Sub 

Open in new window


Also "Run as Script" is not available.
You're welcome Afzal!

I have no experience with Outlook event codes so I suggest you to close this question and open a New Question with your new requirement so that other experts can help you.
Outlook
Outlook

Microsoft Outlook is a personal information manager from Microsoft, available as a part of the Microsoft Office suite. Although often used mainly as an email application, it also includes a calendar, task manager, contact manager, note-taker, journal, and web browser.

105K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo