Forward Message after editing attachments and include edited attachments

[Jeffclanders]

Hi All,
I have a process where someone receives an email with possibly a variety of attachments. There will always be one attachment that is an excle file. This excel file must be opened from the attachment, edited and/or approved with the first level approval, and then saved to the desktop, and reattached to the email to be fowarded to a supervisor for final approval. At the final approval stage, the excel file is once again opened by the supervisor and a pdf is made of one sheet along with an approval.

I have macros already that allow for the push of a button to approve the excel sheet and add a signature to thre excel file, as well as another macro for supervisor approval to add the supervisor name and apprvoal to the excel file and then save it as a pdf.

I also can create a new email with a macro as well. However, this all needs to be done within the context of a forwarded mail to keep all conversations and attachments, other than the excel file, intact along the way so others can view them.

The question is how can I cobble all 0of these macros together? I need someone to be able to open an email, open the excel file, make any edits as well as apprvoe it, then save it to the desktop, and also attach the updated file back to the email to be forwarded on to the next person. The same will apply for when the supervisor is required to open the forwarded email, approve it, and then create a pdf of a sheet and attach the edited excel file, along with newly created pdf to the forwarded email they received to send it on again?

 

[Diane Poremsky]

how about forward, open excel sheet, edit it then create the pdf and attach?

Attaching the pdf is no problem - as long as it's saved in a specific location - the last macro at Create a New Message using an HTML File or Stationery shows how to find the newest file in a folder. You'll then add the file to outlook using .attachment.add sNew

Are your macros excel or outlook macros? It doesn't really matter, but if it's a macro for one program, you need to reference the other application.

 

[Jeffclanders]

Ok Diane, thanks for the reply, so if I understand you, I can accomplish the goal of opening an email, editing the excel attachment, saving that excel file to desktop, additionally saving a pdf version to desktop, and then reatttaching both the pdf and the edited excel file back on the mail to be forwarded?

I have some previous macros from you to send files by email and to forward ones I highlight and select. Would I simply use the forward email macro and modify it? I don't need the highlight and select option, unless it is needed for the current email that is open when someone opens it to make the excel edits and saves the file.

The reason for the forward is that the email comes from an outside contractor and any details in the email need to be forwarded on to the next person for any instructions. This is basically an approval process where an outside contractor sends the email with attached files to an employee at my organziation, then the employee opens the file, saves it to their desktop, as well as saves a second copy as a pdf, then attaches both to the forwarding email to go to the next supervisor for final approval on both.

My macros are in Outlook for forwarding, and I have some in the excel file for saving etc.

 

[Diane Poremsky]

Yes, that is all possible. If you know when you receive the mail (or start the macro) that you will be approving it and have a macro that marked it approved, it should be possible to use one macro (not knowing how you mark it approved could throw a wrench into it) - or, for a few more clicks, you could, open Excel verify it will be approved, then run excel macro to approve, save, forward and attach.

 

[Jeffclanders]

Well, I have a macro that simply throws an image of the signature into the excel file which serves as the approval. This first apprvoer sends the saved excel file to the supervisor, who then uses a similar macro to throw their signature in the same excel file in their respective approval field. It is at this point that I have set up a few macros to save the file as both a pdf and a separate file to save the file as excel. These all work well and they are based on userid to add some level of security. My on;ly sticking point is how to attach the updated excel file from the first approver to a forwarded email and remove the orignal attachment. I then need the second approver to be able to save the updated excel file with both approval signatures as both excel and pdf, and then attach them to an email to continue the forwarding without any of the original attachments, only the updated ones.
I can either do this using a brand new email, or I can use a forward. Right now, a forward is necessary only until I get all of the signatures uploaded. We do not use Notro or anything like that here, so in lieu of those options and my signature image, we have to include the word approved in the body of the email being forwarded, The excel and pdf still need to be created with this process, but the one place I'm stuck at is creating th new email and attaching updated files, which I think you provided, but more importnantly, how to forward the mail items multiple times always updating the attachments. I hope that make sense.

 

[Diane Poremsky]

To copy files from one message to another, you need to use the windows scripting - not hard to do, but forward is generally easier. If you didn't want the forward header, you can get the body & subject from the original message - it'll look like a new message. Adding text to the message body is not hard either - you can use word object model and use insertbefore.

The first macro at Log Messages and Attachment Names uses insertbefore. You'd use a similar method to use excel's object model in outlook. (The second macro on the page just sends data to excel, it doesn't use Excel's object model.)

This opens and excel workbook - you just need to save the workbook and pass the path/filename to it.

Set oApp = CreateObject("Excel.Application")
oApp.Workbooks.Open FileName:=strPath
oApp.Visible = True

or use the method here - Save and Open an Attachment using VBA

A macro for Saving by file type is here - Save Attachments to the Hard Drive