Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Excel 2007 vs 2003 email macro

    Hi,
    I have been using the macro below to email worksheets. Last week my employer upgraded my laptop to Excel 2007. The problem is, not everyone who I send the worksheets to can open the xlsx file format, as they still have Excel 2003 '.xls', or administerd desktops that won't let them save the xlsx file.

    Is there a way to change this macro to have it send the worksheet in xls format?
    I appreciae any assitance you can provide...
    Sub EmailWithOutlook()
    Dim oApp As Object
    Dim oMail As Object
    Dim WB As Workbook
    Dim FileName As String
    Dim wSht As Worksheet
    Dim shtName As String
    Application.ScreenUpdating = False
    ' Make a copy of the active worksheet
    ' and save it to a temporary file
    ActiveSheet.Copy
    Set WB = ActiveWorkbook
    FileName = WB.Worksheets(1).Name
    On Error Resume Next
    Kill "C:\" & FileName
    On Error GoTo 0
    WB.SaveAs FileName:="C:\Documents and Settings\Myname\My Documents" & FileName
    'Create and show the Outlook mail item
    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
    With oMail
    'Uncomment the line below to hard code a recipient
    '.To = "testuser@test.com"
    'Uncomment the line below to hard code a subject
    .Subject = FileName
    'Uncomment the lines below to hard code a body
    '.body = "Dear John" & vbCrLf & vbCrLf & _
    '"Here is the file you asked for"
    .Attachments.Add WB.FullName
    .Display
    End With
    'Delete the temporary file
    WB.ChangeFileAccess Mode:=xlReadOnly
    Kill WB.FullName
    WB.Close SaveChanges:=False
    'Restore screen updating and release Outlook
    Application.ScreenUpdating = True
    Set oMail = Nothing
    Set oApp = Nothing
    End Sub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Try changing the line to explicitly save it as an older version:

    WB.SaveAs FileName:="C:\Documents and Settings\Myname\My Documents" & FileName, FileFormat:=xlExcel8

    Steve

  3. #3
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Ok, thanks I'll try your suggestion!

  4. #4
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Thumbs up

    It worked great, thanks for your help!!

Posting Permissions

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