Results 1 to 3 of 3

Saving Excel Files to Correct Format with Macro

Threaded View

Smurlos Saving Excel Files to Correct... 03-08-2010, 10:32 PM
Huron Re: Saving Excel Files to... 03-09-2010, 12:35 PM
Smurlos Re: Saving Excel Files to... 03-09-2010, 06:49 PM
  1. #1
    Registered User
    Join Date
    10-04-2009
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    77

    Saving Excel Files to Correct Format with Macro

    I have a macro which copies a sheet to a new workbook and then saves the workbook and attaches it to an e-mail.

    When I open the attachment in the e-mail, it shows the following error:

    "the file that you are trying to open is in a different format than specified by the
    file extension. Verify that the file is not corrupted and is from a trusted source before
    opening the file. Do you want to open the file now?"

    When I click "yes" to open the file, the file is fine. However, when the email is sent to customers, the error scares them a bit and a lot of them do not wish to open the file.

    My code is as follows:

        Dim FileExtStr As String
        Dim FileFormatNum As Long
        Dim Sourcewb As Workbook
        Dim Destwb As Workbook
        Dim TempFilePath As String
        Dim TempFileName As String
        Dim OutApp As Object
        Dim OutMail As Object
    
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
    
        Set Sourcewb = ActiveWorkbook
        
        Sheets("Custom").Copy
        
        Set Destwb = ActiveWorkbook
    
    
    With Destwb
    FileExtStr = ".xls"
    End With
    
    ' 'Change all cells in the worksheet to values if you want
    With Destwb.Sheets(1).UsedRange
    .Cells.Copy
    .Cells.PasteSpecial xlPasteValues
    .Cells(1).Select
    End With
    Application.CutCopyMode = False
    
    'Save the new workbook/Mail it/Delete it
    TempFilePath = Environ$("temp") & "\"
    TempFileName = "Confirmation Letter - Ref " & Ref & ""
    
     Set OutApp = CreateObject("Outlook.Application")
        OutApp.Session.LogOn
        Set OutMail = OutApp.CreateItem(0)
        
    With Destwb
    .SaveAs TempFilePath & TempFileName & FileExtStr
    
    With OutMail
    
    ' Compose the message
    Msg = EmailBody
    
                .To = "" & Email_To & ""
                .CC = ""
                .BCC = ""
                .Subject = "" & Email_Subject & ""
                .Body = Msg
                .Attachments.Add Destwb.FullName
                If Attachment1 = "" Then
                ' Do Nothing
                Else
                .Attachments.Add "c:\HBPOS\Letters\" & Attachment1.Value
                End If
                If Attachment2 = "" Then
                ' Do Nothing
                Else
                .Attachments.Add "c:\HBPOS\Letters\" & Attachment2.Value
                End If
                If Attachment3 = "" Then
                ' Do Nothing
                Else
                .Attachments.Add "c:\HBPOS\Letters\" & Attachment3.Value
                End If
                If Attachment4 = "" Then
                ' Do Nothing
                Else
                .Attachments.Add "c:\HBPOS\Letters\" & Attachment4.Value
                End If
                If Attachment5 = "" Then
                ' Do Nothing
                Else
                .Attachments.Add "c:\HBPOS\Letters\" & Attachment5.Value
                End If
                If Attachment6 = "" Then
                ' Do Nothing
                Else
                .Attachments.Add "c:\HBPOS\Letters\" & Attachment6.Value
                End If
                .Send
            End With
    .Close SaveChanges:=False
    End With
    
    'Delete the file you have send
    Kill TempFilePath & TempFileName & FileExtStr
    Thanks in advance
    Last edited by Smurlos; 03-09-2010 at 06:48 PM.

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