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
CodePudding user response:
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.
- 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.
- 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
- Modify the previously saved and opened workbook, save it and bo back to the mail window and press
Send
. The OutlookItemSend
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...