Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Change file type using VBA code

Posted on 2016-09-02
7
Medium Priority
?
90 Views
Last Modified: 2016-09-10
I am using the code below to change an email attachment to a txt format, but when I open the file the file is not readable  Looking for a way to change an email attachment such as a Word document to a text format.


strDateFileName = Year(Now) & "-" & Month(Now) & "-" & Day(Now)
 ' Dot extension name
 strFileExtension = ".txt"



    ' Get the path to your My Documents folder
    strFolderpath = CreateObject("WScript.Shell").SpecialFolders(16)
   
    On Error Resume Next
    Set objOL = CreateObject("Outlook.Application")
    Set objSelection = objOL.ActiveExplorer.Selection

    ' Set the Attachment folder. (Folder must exist.)
    strFolderpath = strFolderpath & "\Attachments\"

    For Each objMsg In objSelection
    Set objAttachments = objMsg.Attachments
   
    lngCount = objAttachments.Count
      If lngCount > 0 Then
        For i = lngCount To 1 Step -1
         strFile = objAttachments.Item(i).FileName
         strFile = strFolderpath & strFile ''& "-" & strDateFileName & strFileExtension
         objAttachments.Item(i).SaveAsFile strFile & strFileExtension

    'use ShellExecute to open the file
    'this may not work with zip extension if you use Compressed folders
   '' ShellExecute 0, "open", strFile, vbNullString, vbNullString, 0
   
    Next
    End If
    Next
   
ExitSub:
0
Comment
Question by:Member_2_7964962
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 22

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 2000 total points
ID: 41782033
if you want to change a file to a text file, you must do more than give it a TXT extension -- it's contents must also be converted.  This usually requires opening it in the application that created it and running code to do that. For Word, here is code to convert the active document to text:
Sub ConvertToTextFile()
'160902 strive4peace
   'converts the active document to text format
   'and saves it in the same folder
   
   Dim sPathFilename As String _
      , sPathFilenameText As String _
      , iPos As Integer
      
   With ActiveDocument
      sPathFilename = .Path & "\" & .Name
   End With
   
   'get position of file extension
   iPos = InStrRev(sPathFilename, ".")
   
   'replace file extension
   sPathFilenameText = Left(sPathFilename, iPos) & "TXT"
   
   ActiveDocument.SaveAs2 FileName:=sPathFilenameText _
      , FileFormat:=wdFormatText
      
   Debug.Print sPathFilenameText
   MsgBox sPathFilenameText & " has been saved" _
      & vbCrLf & vbCrLf & "Path and Filename are also on the debug (immediate) window" _
      , , "Done"
End Sub

Open in new window

0
 

Author Comment

by:Member_2_7964962
ID: 41782203
In my instance I am grabbing the Outlook attachment instead of the ActiveDocument.  How would I translate the code below to the ActiveDocument.SaveAs2 code?   When I run the following statement the file doesn't save: objAttachments.Item(i).SaveAs strFile2, wdFormatText


 Set objSelection = objOL.ActiveExplorer.Selection

    For Each objMsg In objSelection
    Set objAttachments = objMsg.Attachments
0
 
LVL 22
ID: 41782367
if you store the macro in the Normal.dotm file, it will be available to anything you would open.  So you could then open the document and run the macro ... be better, however, to change the code to put it in the path where you want it to go, so modify:
sPathFilenameText = Left(sPathFilename, iPos) & "TXT"
to be:
sPathFilenameText = sPathFilename = c:\myPath\" &  Left(.Name, iPos) & "TXT"

Open in new window

WHERE
myPath is the path where you want to store
(and change the drive letter too, if desired)
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

Author Comment

by:Member_2_7964962
ID: 41782451
Thank you.
0
 
LVL 22
ID: 41782457
you're welcome

that should be:
sPathFilenameText = "c:\myPath\" &  Left(.Name, iPos) & "TXT"

Open in new window

WHER
c:\myPath\ is something like:
c:\Data\Docs\
0
 

Author Closing Comment

by:Member_2_7964962
ID: 41792985
Solution worked.
0
 
LVL 22
ID: 41793010
great! Happy to help
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question