Solved

Change file type using VBA code

Posted on 2016-09-02
7
40 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 18

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 18
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

706 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now