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

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
673
Office Version
  1. 2016
Platform
  1. MacOS
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

VBA Code:
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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Watch MrExcel Video

Forum statistics

Threads
1,111,703
Messages
5,541,306
Members
410,545
Latest member
Upsindustrial20
Top