Outlook mail function, protected mode, and opening pdf attachment issues

I will be very grateful for any expert to please kindly help me on the attached document which currently works but with the following issues:

1. Email sub routine not working. Does not automatically send emails with the attachment to the specified recipients (I.e. admin, the assessor and the assessor’s manager’s email addresses)
2. Does not fully execute in protected mode. I need it to always work in protected mode
3. Does not clear the ligature form (for next use) after completing or executing the processes.
4. Unable to open the attachment/hyperlinked pdf sent/updated into master sheet (LigMaster).

Thank you. Your help much appreciated.

Hi mobuk

Issue 4 appears to be handled by moving lines of Code around. Create the PDF first then create the Hyperlink:
Issue 2: What needs protected...LigatureAssessmentForm?

I'll look at issues 1 and 3.

Please understand, this is your project and you know what you wish to do...and why...I have no clue.
I see what this line of Code is doing. Please explain your INTENTION...why is it doing what it's doing?

Hi jaslake,

Thanks for the response.

Issue 4: When I moved the lines of code around as you advised, it did not attach the formatted PDF document to the mastersheet(LigMaster)

Issue 2: I do protect cells that contain formulas and also those that do not need user inputs. The cells that I do protect are the following but when I do protect, it doesn't function: T2:T7, V3:V7, X2:X7, AB3:AB7, AD2:AD7, S9:AG9, S10:S27, and AA10:AA27.

FOR THE CODE: ActiveWorkbook.SaveAs "C:\ligatures\" & Sheets("Sheet1").Range("AB7") & "_" & Sheet1.Range("AB4") & "_" & Sheet1.Range("AE2") & "_" & Format(Now(), "dd-mm-yyyy") & ".xlsx", FileFormat:=52
It is saving the active workbook as C:\ligatures\documentIDnumber_departmentName_locationName_dateOfSubmission. If you click open the ligMaster sheet tab in the attachec document, you will see sample results.

Hi mobuk

The Code in the attached appears to address all 4 Issues...let me know.

Please notice the Code in ThisWorkbook Module.

Hi jaslake,

Issues 3 and 4 still not resolved.

Issue 3 : I get the error message 'cannont open the specified file' after clicking on the attached file's link. And then the page 'freezes' - you cannot select other cells on that same page (LigMaster) and cannot move the cursor to left or right.

Issue 4: After execution, it does not clear the active workbook for next use. BUT when I run the 'Sheet1.ClearLigatureAssessmentForm' macro on its own, it clears the active workbook.

Also, I got the following error message after execution: 'Document not saved. The document may be open or an error may have been encountered when saving.'

Hi mobuk


Sorry...don't know what to say. Just ran the Code again on your Sample File in both Excel 2007 and Excel 2010 and received the expected results with no error.

Are you getting the issues on your Sample File?

Hi jaslake,

Thanks a lot. Yes I am getting the issues on my Sample File in Excel 2010.

Hi mobuk

I simply don't understand. The only thing I can suggest is to upload the offending File and allow me to test the offending File in Excel 2010 on my platform.

Want to make certain I uploaded the correct File.

Hi jaslake,

Thank you so much for this. Please kindly find attached and the results I got are in LigMaster sheet.

Hi mobuk

Honestly don't know.

Link to PDF works as expected.
Clearing of LigatureAssessmentForm works as expected.
No error messages.

Ran the Code for times...you may have received emails...didn't see "Send".

Sorry...simply don't know.

Hi jaslake,

Thank you very much for your help. As you advised, I will ran the code for some times and will also try it on other PC. Thank you once again, very much appreciated.

jaslake said:

Hi mobuk

Honestly don't know.

Link to PDF works as expected.
Clearing of LigatureAssessmentForm works as expected.
No error messages.

Ran the Code for times...you may have received emails...didn't see "Send".

Sorry...simply don't know.

Click to expand...

Meant to say "Ran the Code four times"

Hi jaslake,
Hope you’re doing great. This is about the third time I’m trying to re-send this reply. I hope this get through.

As per your advise, I did ran it for 4 times. I also received the 2 emails from your tests but the emails had no attachment of the required file/document.

Yes, thanks a lot. The Clearing of LigatureAssessmentForm works.

As a beginner, I have tried for the past days but got nowhere and will be grateful if you could please help me out on the following, please:
Issue 1: File/PDF document not being attached to emails being sent. Please help.

Issue 2: Cell V2 is a dynamic named range (“AssessorsFull”). But when I add data to the named range to beyond 3 rows and select from those beyond the 3[SUP]rd[/SUP] row, the form get populated with mixed data from different users in the named range.

Issue 3: In processing each form, I get the following error message: “Document not saved. The document may be open, or an error may have been encountered when saving.”

Issue 4: Without filling in the form or even a single required field/cell and clicking the macro button, the process executes. Which it shouldn’t until the required user inputs are fulfilled. E.g. V2, AB2, AE2:AE6, and T10:AG10 (T:AG)

I depending on your expertise and help, please. Thank you.

Hi mobuk

I'll try to get back into this tomorrow...may be Friday...have a lot on my plate at the moment.

Tell me if these are resolved:

• Link to PDF works as expected.
• Clearing of LigatureAssessmentForm works as expected.

With regard to this...what should be attached...PDF file?

Issue 1: File/PDF document not being attached to emails being sent

Click to expand...

With regard to this...I'll need to look at it...not sure I understand...

Issue 2: Cell V2 is a dynamic named range (“AssessorsFull”). But when I add data to the named range to beyond 3 rows and select from those beyond the 3[SUP]rd[/SUP] row, the form get populated with mixed data from different users in the named range.

Click to expand...

With regard to this...haven't encountered the Error Message as yet...will try to understand why...

Issue 3: In processing each form, I get the following error message: “Document not saved. The document may be open, or an error may have been encountered when saving.”

Click to expand...

With regard o this...noticed it before...should be simple resolution...

Issue 4: Without filling in the form or even a single required field/cell and clicking the macro button, the process executes. Which it shouldn’t until the required user inputs are fulfilled. E.g. V2, AB2, AE2:AE6, and T10:AG10 (T:AG)

Click to expand...

Hi jaslake,
I very much appreciate your help and immediate response. As you have a lot, may be Saturday or Sunday will be ok for you to look at it?

· No, Link to PDF does not work as expected. It still pops up the message: ‘the file cannot be open’
· Yes, Clearing of LigatureAssessmentForm works as expected. Thanks a lot.

· Issue 1: Yes, the PDF file should be attached

· Issue 2: If you click open the “AssessorDB” sheet in ‘MOW1 Ligatures Yes.xslm’, the data in the AssessorDB is the dynamic named range (“AssessorsFull”). If you add about 6 or more rows of data on to that sheet (i.e. AssessorDB) from row 3 onwards, and you go back onto the form (i.e. back on to the Sheet1 – LigatureAssessmentForm and select an email in cell V2 from one of your newly added rows, it populates the form with wrong data.

· I believe for issues 3 and 4, you understand clearly.

Thank you.

Hi mobuk

Please attach your most recent Workbook (with most recent Code). Need to make certain we're on the same page.

Thanks...

Hi jaslake,

As requested, please find attached my most recent Workbook. Thanks

Hi mobuk

In your Current Workbook Version you've converted LigMaster to an Excel Table. Believe it or not, this is a significant change in the Structure of your Workbook. An Excel Table is a List Object not a Sheet Object and the Code needs revised to accommodate that.

Are you done with Workbook Design? Anymore significant Structural changes?

Hi jaslake,

Please kindly revert the LigMaster back to the original range format instead of the table.
Yes I am done with Workbook Design. No more significant structural changes.

Hi mobuk

This is an image of what I receive for the Email and the Hyperlink...



The PDF File is attached to the Email and the Hyperlink opens as expected.

The Code in the attached runs as expected (for me) with no error messages. Let me know.