Add attachment to outlook email from excel workbook itself (using excel event 'BeforeClose')

Question

Regarding this question Automatically open a copy of the attached file ,and the accepted answer by Mr @FaneDuru.

I need after I edited the opened workbook,
then delete the attached file and save (add) the edited workbook into the email itself.
I wish to fulfill that task from excel workbook itself by adding code to the event BeforeClose.
I manged to remove the attached file.
and I know the code of adding attachment to the email, but I do not know how to use it from excel workbook itself.
In advance,grateful for all useful comments and answers.

Option Explicit
Option Compare Text

Public WithEvents myItem As Outlook.MailItem
Public EventsDisable As Boolean

Private Sub Application_ItemLoad(ByVal Item As Object)
    If EventsDisable = True Then Exit Sub
    If Item.Class = olMail Then
        Set myItem = Item
    End If
End Sub

Private Sub myItem_Open(Cancel As Boolean)
    EventsDisable = True
        If myItem.Subject = "Auto Plan" And Application.ActiveExplorer.CurrentFolder.Name = "MyTemplate" Then
            If myItem.Attachments.Count > 0 Then
                Dim obAttach As Attachment, strSaveMail As String, objExcel As Object
                  Set obAttach = myItem.Attachments(1)
                    strSaveMail = "C:\Users\Waleed\Desktop\outlook-attachments\"
                     obAttach.SaveAsFile strSaveMail & obAttach.DisplayName
                      Dim obAttachName As String
                       obAttachName = obAttach.FileName
                     
                     obAttach.Delete 'Remove attached file
                     
                     Set objExcel = CreateObject("Excel.Application")
                     objExcel.Workbooks.Open strSaveMail & obAttach.DisplayName
                  
                  'Add the below line to workbook itself on event (BeforeClose)
                  myItem.Attachments.Add strSaveMail & obAttachName
                  
                objExcel.Visible = True
               'AppActivate objExcel.ActiveWindow.Caption 'using AppActivate causes error
              Set objExcel = Nothing
            End If
        End If
    EventsDisable = False
End Sub

Answer 1

In order to fulfill the whole process, you should proceed as I will try explaining.

Firstly, I would like to make a brief, describing the invoked process: 1. Open the template from its location. 2. Use Outlook Application_ItemLoad event which triggers myItem_Open, which save the attachment, delete it (from the mail) and open it in Microsoft Excel. 3. You modify the saved attachment, save it, go back to the mail window and press Send. 4. Outlook ItemSend event will reattach the previously saved workbook (now modified) and the mail containing the saved workbook will be sent.

1. Please, copy the next two variables declaration on top of ThisOutlookSession code module (in the declarations area):

Private Const strSaveMail As String = "C:\Users\Waleed\Desktop\outlook-attachments\"
Private wbName As String 'to keep the attachment name

remove strSaveMail As String declaration, strSaveMail = "C:\Users\Waleed\Desktop\outlook-attachments\" and give the attachment workbook name to wbName from myItem_Open code.

2. The adapted code event should look like:

Private Sub myItem_Open(Cancel As Boolean)
    EventsDisable = True
        If myItem.Subject = "Auto Plan" And Application.ActiveExplorer.CurrentFolder.Name = "MyTemplate" Then
            If myItem.Attachments.Count > 0 Then
                Dim obAttach As Attachment, objExcel As Object
                Set obAttach = myItem.Attachments(1)
                obAttach.SaveAsFile strSaveMail & obAttach.DisplayName
                wbName = obAttach.DisplayName 'to be used later, when the workbook will be reattached                     
                obAttach.Delete 'Remove attached file
                     
                Set objExcel = CreateObject("Excel.Application")
                objExcel.Workbooks.Open strSaveMail & wbName                  
                objExcel.Visible = True
                Set objExcel = Nothing
            End If
        End If
    EventsDisable = False
End Sub

3. Modify the previously saved and opened workbook, save it and bo back to the mail window and press Send. The Outlook ItemSend event will be triggered and will re-attach the saved workbook:

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
   If TypeName(Item) = "MailItem" Then
        Set MyItem = Item
        On Error GoTo Err_Handler
        If MyItem.Subject = "Auto Plan" And MyItem.Attachments.Count = 0 Then
            MyItem.Attachments.Add strSaveMail & wbName, 1 
        End If
   End If
   Exit Sub
Err_Handler:
    MsgBox Err.Number & vbCrLf & Err.Description
   Cancel = True 'if an error will be raised, the mail sending is cancelled, to see what problem does appear...
End Sub

The mail will be sent with the modified workbook as attachment.

Using the global variables will work well, if no any error (during testing period) will appear. Because of that, both of them may be saved in Registry and use without any problem, even if an error has been raised.

The Open event can be modified to have Excel open (attached) workbook in a maximize window, just behind the mail window.

Going back to the mail window can also be automated, as you could see in my answer at your last question, on the issue.

If anything not clear enough, please do not hesitate to ask for clarifications.

But try the code/solution as it is and only after seeing it working try modifying it, if necessary...