Macro to attach sheet clled Movement

[howard]

I have VBA Code to create an email in outlook and attach the sheet called "Movement." The file to be attached to be named Group Movement.XLS and to be valued i.e.. no formulas to be shown on the attached sheet. I have VBA code below which I need amended to comply with my request

Your assistance is most appreciated

 Sub Email_Movement()


 ThisWorkbook.Activate                           'start in THIS workbook
ztext = [bodytext]                              'read in text from named cell
Zsubject = [subjectText]
  Dim OutApp As Object
Dim OutMail As Object
Dim File As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
File = ThisWorkbook.Path & "\" & ThisWorkbook.Name ' use the current workbook

With OutMail
    .To = Join(Application.Transpose(Sheets("Email").Range("AA1:AA3").Value), ";")
    .CC = Join(Application.Transpose(Range("AB1:AB7").Value), ";")
    .BCC = ""
    .Subject = Zsubject
    .Body = ztext
    
    'Attach the sheet "Movement"
    Sheets("Movement").Copy
    .Attachments.Add File:=ActiveWorkbook.FullName, _
                      Type:=olByValue, _
                      Name:="Movement.xlsm"
    
    .Display
End With

On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

 

[excel_learnerz]

To modify your code to save the "Movement" sheet as a separate file with the name "Group Movement.xls" and attach it to the email as a value-only worksheet, you can follow these steps:

1. Save the "Movement" sheet as a new workbook in XLS format with only values and no formulas.
2. Attach the new workbook to the email.
3. Delete the temporary new workbook after attaching it to the email.

Here's the modified code:

Sub Email_Movement()

    ThisWorkbook.Activate                           'start in THIS workbook
    ztext = [bodytext]                              'read in text from named cell
    Zsubject = [subjectText]
    Dim OutApp As Object
    Dim OutMail As Object
    Dim File As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    File = ThisWorkbook.Path & "\" & "Group Movement.xls"

    ' Save the sheet "Movement" as a new workbook with values only
    Sheets("Movement").Copy
    With ActiveSheet.UsedRange
        .Value = .Value
    End With
    ActiveWorkbook.SaveAs Filename:=File, FileFormat:=xlExcel8
    ActiveWorkbook.Close SaveChanges:=False

    With OutMail
        .To = Join(Application.Transpose(Sheets("Email").Range("AA1:AA3").Value), ";")
        .CC = Join(Application.Transpose(Range("AB1:AB7").Value), ";")
        .BCC = ""
        .Subject = Zsubject
        .Body = ztext

        ' Attach the new workbook
        .Attachments.Add Source:=File

        .Display
    End With

    On Error GoTo 0

    ' Delete the temporary new workbook
    Kill File

    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub

this code saves the "Movement" sheet as a new workbook in XLS format with only values and attaches it to the email. After attaching the new workbook to the email, the temporary file is deleted.

 

[howard]

Thanks for the advise and code