How to remove code from ThisWorkbook before emailing as attachment

[ahazeldene]

Hi,

I have two modules in my workbook, which I call automatically within ThisWorkbook, with a "Private Sub Workbook_Open" command:

Please Login or Register  to view this content.

These modules refresh my workbook and then email the refreshed workbook to a colleague.

The issue is that when my colleague opens the workbook, the ThisWorkbook command tries to run, and causes an error as he does not have the API required.

My question therefore is how can I add one more step to my VBA, so that the commands from ThisWorkbook are removed before the file is emailed?

Hopefully this makes sense, would be great to get it working!

Many thanks,
Antony

[sintek]

Hi ahazeldene

Could this work

Please Login or Register  to view this content.

[ahazeldene]

Hi,

Thank you for the suggestion, but the above solution involves manual intervention, meaning that I have to click "YES" for the workbook to update and send.

The workbook is scheduled in Task Scheduler to open at 08:15 every morning. The idea is that the workbook, once opened by Task Scheduler, will automatically refresh and email as an attachment, with no manual intervention by me. This allows the whole process to run even if I am not at my desk.

The current problem is that when my colleague receives the email and opens the attachment, the code in ThisWorkbook tries to run again, and he gets errors as he does not have API tool.

Is there a way to delete/deactivate the code from ThisWorkbook before the email is sent? This way when my colleague receives the email and opens the attached workbook, the VBA will not try and run on his machine.

Thanks,
Antony

[sintek]

Why don't you save the workbook as a normal xls. and not a macro-enabled document before emailing. Or...does your colleague have to get the document to make use of the macros.

[ahazeldene]

Hi,

I do save the workbook as a .xls file. This causes the below warning when the user opens the workbook:

2017-04-10 08_05_03-Excel.png

He then gets another warning saying that the workbook had has macros disabled, does he want to enable?

I would like this workbook at run completely autonomously on my side via Task Sceduler and then VBA in ThisWorkbook. This part is working. What I need is to somehow disable the code from ThisWorkbook before the workbook is sent as an attachment.

Here is my code in full:

ThisWorkbook:

Please Login or Register  to view this content.

Module RefreshData:

Please Login or Register  to view this content.

Module SendEmail:

Please Login or Register  to view this content.

Thanks!

[bakerman2]

Why not copy the sheet(s) to a new workbook and send that as attachment ?

[FDibbins]

Why don't you save the workbook as a normal xls. and not a macro-enabled document before emailing. Or...does your colleague have to get the document to make use of the macros.

That was my thoughts too, except to save As ,.xlsX that will make sure the code is stripped out

[ahazeldene]

Hi,

Thanks for the suggestions. In regards to the saving the file as an .xlsx file, I have changed my SendEmail module, but get the below error:

2017-04-10 10_33_25-Excel.png

My VBA reads:

Please Login or Register  to view this content.

Any ideas why it is not working?

Thanks!

[cytop]

Because you are using SaveCopyAs which saves a copy of the workbook, but the code remains and you are changing the extension to XLSX.

When the file is opened, Excel detects the code but 'sees' an XLSX (no code) extension and assumes the worst. You need to change the FileFormat parameter of the SAVE command to save the workbook without code

Please Login or Register  to view this content.

OK, so I haven't looked too closely at the file name and whether that code is trying to save a file with the same name as an existing file - so you need to make sure. It would be a simple matter to save the file to the TEMP directory and attach to the email from there.

[bakerman2]

You cannot set or change FileFormat using SaveCopyAs.
Like I said earlier export your Sheet or Sheets to a new workbook and use SaveAs. Then you can set fileformat as required.

PS no need to use FileExtStr when you set fileformat. XL will add proper File extension according to FileFormat used.

[FDibbins]

If they are using an older version of excel 2003 or earlier, they wont be able to open .xlsx files

[ahazeldene]

Hi,

Thanks for the input. I substituted the code suggested above, and still receive the warning message:

2017-04-10 08_05_03-Excel.png

Also, now once the workbook has been emailed, the codes behind the refreshed data revert back to codes (rather than refreshed data), and the workbook is unfortunately unusable.

My current code reads:

Please Login or Register  to view this content.

Perhaps I now need a new argument that copies the whole sheet and then Paste:=xlPasteValuesAndNumberFormats?

If someone could help adjust the above code so that it can save the workbook in a agreeable format and email the data without unwinding the API refresh, that would be really appreciated.

Thanks.

[bakerman2]

Try this.

Please Login or Register  to view this content.

[ahazeldene]

Hi,

The above suggestion solves the issue of "corrupt or unsafe" file extension, but I still get the API refresh issue:

Data after refresh:

2017-04-12 08_14_27-Abdur Morning Update - Excel.png

Data once emailed and opened:

2017-04-12 08_14_45-Morning Update 12-Apr-17 [Read-Only] - Excel.png

Is it possible to add an argument to the code above that copies the whole sheet and then pastes as Paste:=xlPasteValuesAndNumberFormats? I think that this would solve the second and only outstanding issue I have.

Appreciate any suggestions,
Thanks.

[ahazeldene]

Hi,

Does anyone have any ideas on this? I am still unable to send the file with the refreshed data, it shows as #Calc as above.

If anyone has any ideas how to modify the below code I would appreciate the input. I would like the data, once refreshed, to be saved as values and formats (no underlying codes):

Please Login or Register  to view this content.

Thanks,