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

CYStudios

New Member
Joined
Feb 10, 2021
Messages
1
Office Version
  1. 2013
Platform
  1. Windows
Howdy

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
        .Display
    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
        .Save
        .Display
    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

1612981746980.png


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.
1612981682687.png
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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 MrExcel.com.
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 "mrexcel.com".
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
Back
Top