+ Reply to Thread
Results 1 to 2 of 2

Change an Excel macro to work in Word

  1. #1
    Registered User
    Join Date
    02-27-2010
    Location
    ilm, nc
    MS-Off Ver
    Excel 2003
    Posts
    1

    Change an Excel macro to work in Word

    I came across this macro online. It is for Excel. I wanted to run it in Word but have no idea what needs to be changed. Any help would be greatly appreciated.

    Sub Mail_Workbook_2()
    'Working in 2000-2007
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String

    Set wb1 = ActiveWorkbook

    If Val(Application.Version) >= 12 Then
    If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
    MsgBox "There is VBA code in this xlsx file, there will be no VBA code in the file you send." & vbNewLine & _
    "Save the file first as xlsm and then try the macro again.", vbInformation
    Exit Sub
    End If
    End If

    With Application
    .ScreenUpdating = False
    .EnableEvents = False
    End With

    'Make a copy of the file/Open it/Mail it/Delete it
    'If you want to change the file name then change only TempFileName
    TempFilePath = Environ$("temp") & "\"
    TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
    FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))

    wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
    Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)

    With wb2
    On Error Resume Next
    .SendMail "", _
    "This is the Subject line"
    On Error GoTo 0
    .Close SaveChanges:=False
    End With

    'Delete the file
    Kill TempFilePath & TempFileName & FileExtStr

    With Application
    .ScreenUpdating = True
    .EnableEvents = True
    End With
    End Sub
    Last edited by juiceman; 02-28-2010 at 12:32 AM.

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Word macro help*PLEASE*

    please take a moment to read the forum rules, then adjust your thread title and add code tags.

    thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1