Auto-Create Email Macro?

Okay, so after spending 16 hours a day for the last couple of days working on reformatting the attached workbook to more closely align with daily OPs, I am to the finish line but there's one more thing I want to be able to do from each Order Worksheet:

Once I have all order details entered, there's a table to the very top right of the actual order worksheet that contains data that has to get sent in an email. As one can see, I've already got a button marked, "Send Flagged Email" and here is what I would like to happen when I click on it (it used to just be an email link (still there, but will be deleting once I get this button working), but that still involved too many steps to complete for every single order entered, hence the Macro for the button):

On Click:
1. Open a new Outlook Email window, with a predetermined group email address entered in the "To" field
2. Enter "Flagged Order: "in the Subject line, then copy and paste the value from $N$1, followed by a space, then copy and paste the value from $M$3 & $N$3, also in the subject line, resulting in the following Subject Line: "Flagged Order: US FOODS California (4C) SRF# 45779"
3. Copy and paste cell range $M$1:$T$15 into the very top of the email, BUT DO NOT SEND - I may have need to enter more info/details before the email gets sent.

Any VBA geniuses that would like to show me the code to generate for this Macro, and tell me how to save it to my Workbook so I can assign it to the same button on all ten Order worksheets? I tried just recording a new Macro doing all the steps above, and that did nothing but highlight $M$1:$T$15 and sit there looking at me like I'm a moron when I clicked the button.

Thanks in advance,

Bryan
Final Stretch.xlsx

So if I'm reading this correctly, this will actually take a JPG of the required table showing flagged and non-flagged items on it instead of copying and pasting actual cells, creating essentially an editable table in the email?

I appreciate the effort on this, but it's not quite what I'm hoping to achieve. Ideally, the two columns in the table that say "Adj Qty" will be able to have quantities entered into them by our Director of Operations and Director of Business Development in a reply email to advise if we need to adjust quantities up or down for each flagged and non-flagged item. Is this possible, or am I just asking too much of VBA/Excel in this instance?

Thanks for the initial reply!

-Bryan

Originally Posted by sintek

This is how I understood your logic...Perhaps you should explain in more detail what it is you are wanting to achieve...

I thought I did in my initial reply. I need the macro to copy the named range of that particular table in the worksheet and paste it into the email that is generated by it. Again, this is so staff on the email list can review and enter adjustment quantities for each item in that table in a reply email. Obviously I can manually copy and paste the range into an email, and it works perfectly. However, it's still an extra step in the process that could save me a LOT of time if there is a macro that does all of this, except "clicking" send, for me. I enter an average of 15 POs a day that have flagged items on them, and I used to have to literally type each and every detail of the flagged items email out, resulting in numerous typos and errors, and this button that copies and pasts data from an auto-populated table would eliminate that (as long as I transferred the PO over to the Order worksheet correctly, that is).

I hope this clarifies things a bit more. Again, I sincerely appreciate the initial work on this - people on this forum are exceptional, and I'd have been so lost and hopelessly frustrated without each and every one of you!

-Bryan

Originally Posted by sintek

Which part...All of it...or only the table part?
This is how I understood your logic...Perhaps you should explain in more detail what it is you are wanting to achieve...


are you wanting this in the body of the email...

*Yes, this is exactly what I am wanting. I missed this part of your reply in my second reply/explanation. Sorry! Not enough coffee yet!

this would mean the whole point of creating the table - extracting existing data from the main Order Worksheet automatically - would be rendered pointless if I was still manually entering data on it, so you're correct that this would not work for my needs.

Originally Posted by sintek

Okay...see if amended above is what you are after....

Reading through the modified code, it looks like this script creates a temporary copy of the table into a new/temporary worksheet, converting formulas to values instead, which will definitely work in theory (I haven't actually tried it yet, as I'm getting ready for work). One question, though: will this keep all formatting aspects of column widths and row heights, as well?

I don't like asking silly questions, but obviously if I was versed in VBA and Excel at the same level many of you here are, I wouldn't be here asking for help. LOL!!!

I am truly grateful for the help with this issue, and I will test this script later this morning and see if it achieves what I am hoping to with this portion of the workbook.

Thanks, Sintek!

-Bryan

Okay, so I finally had time to try this out. When I go to add this to Module 1, I get an error that states "Expected End Sub". So I tried just creating this as a new macro without the "Sub SendFlaggedEmail()" heading and comments, which removed it from macros available to the workbook, so I traded one error for another. I've deleted this code from Module 1 altogether for now, as the second error was a result of referencing a macro that didn't exist, which also disabled other correctly-functioning macros. How do I add this code to the list of macros in the workbook and resolve the "Expected End Sub" error message?

Your assumption is correct in this case, since I was trying to assign the code you provided to the button that corresponds with initiating the actions, and to the best of my knowledge I had to assign it a name that made sense to me ("SendFlaggedEmail()" and paste that code in the sheet for Module 1 so it was embedded in the workbook itself. Again, my knowledge of Excel and VBA is extremely limited, which is why I come to these forums for guidance and new knowledge on how to do things that eventually make my job (and personal life) much easier. I apologize if I come across as an idiot, but I honestly feel like this is what these forums are supposed to be for: those with advanced knowledge helping those of us with just the basics or to learn things we don't already know how to do.

I hope you know just how much I appreciate your help with this particular issue, Sintek. You've been invaluable to this project.

-B

Originally Posted by sintek

You've obviously made an error when pasting the code in the module...
I am asumimg you have two Sub Headings... i.e.

Please Login or Register  to view this content.

Thanks!!

-B

I have assigned this macro to your button....see attached...

Okay, I just clicked the button on the attachment, and I get an "ActiveX component can't create object" error (#429). When I click "Debug", the row that has the command, "With CreateObject('outlook.application').CreateItem(0)" is highlighted. Does anyone have any insight as to what's going wrong here? I don't know if it's relevant or not, but I need this macro to work on both MacOS and Windows operating systems (from what I understand, VBA works the same regardless of the OS and version of Excel being used...?)

Thanks!!

-B