Solved

Change file type using VBA code

Posted on 2016-09-02
7
58 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
  • 4
  • 3
7 Comments
 
LVL 19

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 500 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 19
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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 

Author Comment

by:Member_2_7964962
ID: 41782451
Thank you.
0
 
LVL 19
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 19
ID: 41793010
great! Happy to help
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
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…

821 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