Worksheet in MS Excel
What is already working (Outlook 2007):
• U7 is filled in and rest of address box is filled up with cells from tabsheet ‘Adressen’
• Button ‘Save’ saves the worksheet as an xls file (string X7 + U11) under the right directory F:/TLD NV/WERKBONNEN. If a filename already exists, warning message asking if name must be overwritten or saved under another name.
• Button ‘Send’ sends the file as a pdf file to the email address in cell ‘AC13′ Condition for this is that the active printer is a pdf printerdriver (we use Primo pdf)
• Dropdownlist with materials (AQ17 till AQ38) , total of hours, is already working.
Materials, adressesses and main Tabpage ‘Werkbon’ (Dutch name for Worksheet’ are now in one xls file.
The items in tabpage ‘Werkbon’ comes from the other 2 tabpages.
What I need now additionally is:
Trying to use IntelliSense when filling up
• material in colum AQ17 till AQ38.
• Code in U7
• Machinen° in AH11
BI11 ‘Job Finished?’ is a Checkbox with 2 options ‘Yes’ or ‘No’
BI12 ‘Leakreport’ CheckBox asks if a leakreport needs to be made ‘Yes’ or ‘No’ See further
BI13 ‘Mint report’ Checkbox asks if a maintenance report needs to be made ‘Yes’ or ‘No’ See further.
Splitting the original sheet in a separate file with only the ‘Werkbon’-tab page as ‘Werkbon.xls’ and the two other remaining tabpages ‘Adressen’ and ‘Materiaal’ in a new file a new name ‘data.xls’
Both stored in the same directory.
Werkbon.xls then gets its data from the separate data.xls file.
This is done because the size of a fiel to be send is then smaller because we only need the filled-in main Tabpage ‘Werkbon’ See further.
Button (already placed and working on the sheet) ‘Send xls’: sheet is send as an xls (filename = string X7 + U11) to address in cell K2 of Tabpage ‘Adressen’ (my address) found in Data.xls
Subject = string X7 + U11 + ‘ Werkbon'(no extension needed)
No body message needed in this mail
Before sending, warning message “File ‘string X7 + U11′ will be send as an xls. Agree, Yes or no”
New button (already placed on the sheet) ‘Send pdf Cpy’ file is send as a pdf (filename = string X7 + U11) to address in cell K2 of Tabpage ‘Adressen’ found in Data.xls
Subject = string X7 + U11 + ‘Werkbon’ (no extension)
No body message in the mail
Before sending, warning message “File ‘string X7 + U11′ will be send as a pdf. Agree, Yes or no”
Additionally button (already placed on the sheet) ‘Send All’, file is sent as a pdf file (filename string X7 + U11) to address in cell K2 of Tabpage ‘Adressen’ and to the addresses from To to Bcc3 (Cell ‘K3′ till ‘P3′) from that specifice selected client (U7) found in Data.xls.
Subject = string X7 + U11 + “Werkbon”
Body message with ‘Send All’ button
Dear sir/mrs,
Here my worksheet ‘W5′ for the ‘BI6′ job on ‘U13′ performed by technician ‘H15’on system ‘AN6′, reason for call ‘AN10′
Total of the job is ‘BL41′ $
If (condition!) something was filled in in TextBox ‘B31′, then add additionally text
“Remarks” followed by the content of TextBox ‘B31′
Followed with “Job description” and the then description of what was filled in in TextBox2
“The job is finished” or “The job is not finished” (condition for this ‘BI12′)
Thanks
Pete
Before sending, warning message “File ‘string X7 + U11′ will be send as a pdf to ‘to till Bcc3’Agree, Yes or no”
After sending, a message is Shown on Screen ‘File send to client ‘To‘ till ‘Bcc3′
New dropdown list to make in AH11. Source is column K in Machinelist.xls (stored same directory) with additional filter for client in U7. So only those items in dropdownlist AH11 are given which belongs to those associated to client U7 and also found in column A in Machinelist.xls.
Leakreport and Maintenance report.
Condition if these reports needs to be made are found in BI12 for the leakreport and BI13 for the Maintenance report
A word template, 1 page/report and already made for each report (provide those once agreed) is used and stored in the same directory and the data (+/- 12 items, max 15) for that specific client U7 and Machine AH11 is then found in Machinelist.xls and used to fill in the Wordtemplate to generate the 1-page report with +/- 20 lines. There’s a template for the leakreport and one for the maintenancereport.
In the generated Word document, we find back the same buttons as in the Excel file: ‘Save’, ‘Send Xls’, ‘Send pdf All’, ‘Send pdf Cpy’ and a new button ‘ Back to XLS Worksheet’ is added.
‘Save’ saves file as X7 + U11 + ‘Leakreport’ or ‘Maintenancereport’
The buttons are not seen in the generated pdf file or if this is too difficult to accomplish, they’re all sorted together in the footnote or on the not pdf printed second page.
Before sending, warning message “File ‘string X7 + U11′ ‘Leakreport/maintenance‘ will be send as a pdf to ‘To’ till ‘Bcc3”Agree, Yes or no”
After sending, a message is Shown on Screen ‘File send to client ‘X7’
Macro is completely added with your delivery because we have to adapt anyhow some cells, translate all sentences to Dutch (have done it now all in English because this is easier to program for you) and we will perhaps not use all the features and maybe we will re-arrange some of the lay-out of this sheet.
State also:
• needed timeframe
• Have you done similar jobs?
• If IntelliSense is possible as asked
