VBA Help - Modify Working Code that attaches Excel file to Email (Mac email) - Designed by Ron Bruin

[Johnny Thunder]

Hi everyone, found a cool bit of code and need to modify one line but not sure how to do it and all my attempts have failed.

Currently, the line is copying the ActiveSheet and pasting it to DestWB to make a new file and then attaching that new file to an Email - I want to just attach a copy of the current workbook (the whole workbook) instead of individual sheets or even an array.

Here is the code, and a commented "'<----------- " on the line I need modified

Option Explicit

Sub Mail_With_Mac_Excel_with_Mail_In_Catalina_And_Up()
    'Only working in Excel 2016 and up for the Mac with Apple Mail as mail program
    'Do not forget to also add the functions into your own workbook
    'More Mail codes : http://www.rondebruin.nl/mac/mail.htm
    '18-April-2020
    Dim Sourcewb As Workbook, DestWB  As Workbook, sh As Worksheet
    Dim strbody As String, TempFileName As String

    'Check if the AppleScriptTaskExcelScriptFile is in the correct location
    If CheckAppleScriptTaskExcelScriptFile(ScriptFileName:="RDBMacMailCatalinaAndUp.scpt") = False Then
        MsgBox "Sorry the RDBMacMailCatalinaAndUp.scpt file is not in the correct location, " & _
        "Visit http://www.rondebruin.nl/mac/macmail/macmail2016.htm for more information."
        Exit Sub
    End If

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

    'Set reference to the source workbook
    Set Sourcewb = ActiveWorkbook

    'Create the body text in the strbody string
    strbody = "Hi there" & vbNewLine & vbNewLine & _
        "This is line 1" & vbNewLine & _
        "This is line 2" & vbNewLine & _
        "This is line 3" & vbNewLine & _
        "This is line 4"

    'Copy the ActiveSheet to a new workbook, you can also use : Sourcewb.Sheets("MySheetName").Copy
    'Or for more sheets : Sourcewb.Sheets(Array("Sheet1", "Sheet3")).Copy
    ActiveSheet.Copy    '<------------- --------------------------------------------------------------------This Line
    Set DestWB = ActiveWorkbook
  
    'Enter the name of the file we just create
    'This file name will be used in the function to save the file
    TempFileName = "Part of " & Sourcewb.Name & " " _
        & Format(Now, "dd-mmm-yy h-mm-ss")


    'Call the MacExcelWithMacMailCatalinaAndUp function to save the new file and create the mail
    'When you use more mail addresses separate them with a ,
    'Change yes to no in the displaymail argument to send directly
    'Look in Mail>Preferences for the name of the signature(you can use any signature in the signatures section)
    'Look in Mail>Preferences for the name of the mail account
    'Sender name (thesender) looks like this : "Your Name <your@mailaddress.com>"
    'Do not change the attachment and FileFormat argument
    
    MacExcelWithMacMailCatalinaAndUp subject:="This is a test macro", _
    mailbody:=strbody, _
    toaddress:="myemail@myemail.com", _
    ccaddress:="", _
    bccaddress:="", _
    displaymail:="yes", _
    attachment:=TempFileName, _
    thesignature:="", _
    thesender:="", _
    FileFormat:=Sourcewb.FileFormat
 
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

 

[mrshl9898]

couldn't you just remove that line?

ActiveSheet.Copy

 

[Johnny Thunder]

Nope, it crashes the file doing that.

 

[Johnny Thunder]

@Ron de Bruin Any help on this one?

 

[mrshl9898]

@Fluff Any solutions?