Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Change file type using VBA code

Posted on 2016-09-02
7
Medium Priority
?
103 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 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
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.

 

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
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

926 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