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,
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.
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 SubQuery 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.
