[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 133
  • Last Modified:

Change file type using VBA code

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
Member_2_7964962
Asked:
Member_2_7964962
  • 4
  • 3
1 Solution
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
 
Member_2_7964962Author Commented:
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
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
Member_2_7964962Author Commented:
Thank you.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
 
Member_2_7964962Author Commented:
Solution worked.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
great! Happy to help
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now