Illegal Character Replacement Script - Auto Archiving - Convert to PDF if only info is inputted into sheet.


New Member
Feb 10, 2021
Office Version
  1. 2013
  1. Windows

Query 1:
Looking to remove all illegal characters on save for both Convert PDF & Save to xlsm
I have added buttons on sheets to change it from XLTM to XLSM
and one for the PDF to Email

Code looks like this,

VBA Code:
Sub Workbook_Open_Quote_Number()
    Range("E11").Value = Range("11").Value + 1
End Sub
Sub Email_Sheet_Click()

    Dim objOutlook As Object
    Dim objMail As Object
    Dim signature As String
    Dim PDF_FileName As String
    Dim oWB As Workbook
    Set oWB = ActiveWorkbook
     s = Range("L8").Value
     PDF_FileName = ".....\! New Generated Documeants\" & Sheets("STAGE").Range("L6").Value & ".pdf"
     Sheet3.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
         PDF_FileName, Quality:=xlQualityStandard, IncludeDocProperties _
         :=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Set objOutlook = CreateObject("Outlook.Application")
    Set objMail = objOutlook.CreateItem(0)
    With objMail
    End With
        signature = objMail.HTMLbody
    With objMail
        .BCc = Sheets("STAGE").Range("K14")
        .Subject = "xxxxx | Job Quote : " & Sheets("STAGE").Range("B10")
        .HTMLbody = "<BODY style=font-size:11pt;font-family:Calibri>Greetings;<p>Please find attached Quotation for Job: " & Sheets("STAGE").Range("b10") & "<p> Any questions please don't hesitate to ask." & "<br> <br>" & signature & "</font>"
        .Attachments.Add PDF_FileName
    End With

    Set objOutlook = Nothing
    Set objMail = Nothing
End Sub
Sub SaveAsCellName()

    Dim myFolderName As String
    Dim Path As String
    Dim FileName As String

        Path = "C:\Users\cyeoman\OneDrive - WESCO DISTRIBUTION\Documents - London\B2370 Projects Saved Files\2021 Jobs - File Folder Purple\! New Generated Documeants\"
        FileName = Range("L6").Value

    ActiveWorkbook.SaveAs FileName:=Path & FileName & ".xlsm", FileFormat:=52
    Application.DisplayAlerts = True

End Sub

Query 2:
Also was wondering if anyone had a string to save and generated a new sub folder in the directory Based off the auto generated file name?
Have it saving to sharepoint, but unclear without manually setting the path for certain user, if this can be done automatically.

Its a projects based quote system,

Ideally on year roll over i would also have it great a master Folder for the directory and then save a new folder, for shared cloud based archiving,
and get away from \! New Generated Documents\ manual drag and drop.

Sorry was tasked for two weeks to fill in for a co-worker, and well so much time wasted, leaned how to VBA on the fly, so the above is not the cleanest scripting


Query 3:
There is a customer submit aspect to this as well, basically the stage sheet populates cell on the customer copy, and once the PDF Email button is hit, it creates 4 pdf copies from the work sheet.
1st sheet isn't full page, but has my organization info can quote to on it, but i'd like to only have pages generated if data entry overflows into the next sheet, so the end user doesn't print off a couple of blank pages.

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Watch MrExcel Video

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back