I have a VBA code which sends excel Workbooks as E-mail Attachments. how can i change the file format of the attached file?

[bleeet]

hi guys
I have a script which automatically sends an email with the workbook attached to people after a macro has been run. The problem I am now facing is that I want the file attachment to be attached as a xlxs file so when they open the workbook the macro will not run again for the people that open the file from the email.
here is the code I am using

[COLOR=#222222][FONT=Verdana]Sub email()[/FONT][/COLOR][/FONT][/COLOR]


    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim OutApp As Object
    Dim OutMail As Object
    Dim tempFilename2


    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" & vbNewLine & _
                   "be no VBA code in the file you send. Save the" & vbNewLine & _
                   "file as a macro-enabled (.xlsx) and then retry the macro.", vbInformation
            Exit Sub
        End If
    End If


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


    ' Make a copy of the file.
    ' If you want to change the file name then change only TempFileName variable.
    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)


    Set OutApp = CreateObject("Outlook.Application")
    
    Set OutMail = OutApp.CreateItem(0)


    On Error Resume Next
   ' Change the mail address and subject in the macro before you run this procedure.
    With OutMail
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = "Estates Data Pull"
        .Body = "Hi felix here is today's data results"
        .Attachments.Add wb2.FullName
        
        .Send
    End With
    On Error GoTo 0


    wb2.Close SaveChanges:=False


    
End Sub


[COLOR=#000000][FONT=Arial]

 

[BrianMH]

Keep in mind this will save the workbook as a new file name without the module in it. I've added a line you can uncomment if you want to save the workbook in place before saving as a new file name.

Sub email()
    Dim wb1 As Workbook


    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim OutApp As Object
    Dim OutMail As Object
    Dim tempFilename2 As String
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With


    Set wb1 = ActiveWorkbook




    ' Make a copy of the file.
    ' If you want to change the file name then change only TempFileName variable.
    TempFilePath = Environ$("temp") & "\"
    TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
    FileExtStr = ".XLSX"
    tempFilename2 = TempFilePath & TempFileName & FileExtStr


    'uncomment the next line if you want to save the workbook as it is before saving as a new type
    'wb1.Save
    
    Application.DisplayAlerts = False
    wb1.SaveAs tempFilename2, xlOpenXMLWorkbook
    Application.DisplayAlerts = True
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    ' Change the mail address and subject in the macro before you run this procedure.
    With OutMail
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = "Estates Data Pull"
        .Body = "Hi felix here is today's data results"
        .Attachments.Add tempfile2


        .Send
    End With
    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.EnableEvents = True


End Sub

 

[bleeet]

thanks BrianMh but it is not attaching the excel file to the email

 

[BrianMH]

Change

.Attachments.Add tempfile2

to

.Attachments.Add tempFilename2

 

[bleeet]

thank you so much BrianMh it worked

If you happen to have time I have another question for you would you happen to know how I can have a certain range of cells from a sheet in the workbook appear in the body of the email?

 

[BrianMH]

Glad to help. If you do a search on the forums and google you should be able to find several topics on that question, I don't know the answer to that one off the top of my head as I never use that process. It's a common question though so I'm confident you will find something.