Graphics used in the Mail Merge
The Warley Painters Logo was scanned in from the letter that was given to me by Warley Painters. The
The banner was print screened from the website and then using Microsoft Picture Manager the image was cropped.
The banners in the footer and the header were created in Adobe Fireworks CS3. The text used was Verdana 13 pt in green font.
The watermark was created by using the logo that I had created using Paint. This was then imported into Microsoft Word using the printed watermark features. The image was then inserted at 300% this was to ensure that the image could be seen but the text above it is not obscured.
Data Imported for the Mail Merge
A table was created in Microsoft Access 2003 as shown below. It contains information on the customers contact details. It also includes the work done and the price of the cost of that information.
Indents used in the document
The mail Merged fields
The mail merged fields and the salutation were set at a 9.5 mm. This allowed the merged fields to be brought in from the left hand margin so that it could clearly be seen.
The Body of Text
The main body of text was set at 9.5 mm (from the left) and15.9 mm (from the right).
Table Modification
Shading of cells
BASIC FEATURES of The Mail Merge
Imported data from an external source
The screen shots below show the table created for the mail merge document in both Design view and Data Sheet. It shows the fields that have been used to create not only the fields for the letter but also for the information merged into the table at the end of the letter.
Setting of the fields
The screenshot below shows how the fields were entered into the mail merge document . using the mail merge toolbar
Testing the Mail Merge document
Before the database was fully constructed I first tested if the mail merged data was set up correctly and the right information had been brought across from the Access Database. To do this I filled in two complete lots of information for two companies.
This is a screen shot showing the mail merged fields after the data from the Access Database has been imported into the letter. As you can see from the screenshot below the data is well spaced out there is no errors in the information brought across and there are adequate spacing between the fields so that it can easily be read.
Testing the Mail Merge document 2
Using the same test data as above I checked whether the fields had incorporated the correct information into the table at the back of the mail merge.
As you can see from the screenshot the fields are merged correctly, a print out of the test data will be shown in the appendix.
ADVANCED FEATURES of the Mail Merge
The use of Formulas
The following is a description of the formulas that I have entered into the table to ensure that any information inserted into is added up correctly and automatically.
Subtotal before VAT
This formula was used to calculate all of the costs for the work that was done. This formula will add up the mail merged amount fields entered from the database fields. After inserting a field into the table the formula {=SUM(ABOVE)}. This formula adds up all of the values above. This is the 10 amounts that will be brought in from the mail merge
Testing of the Subtotal before VAT
The formula used in this mail merged example was to add up the column of values below. Adding up the values it should give an example of 33,950
VAT Calculation
This field calculates the VAT based upon the amount of the total from the previous field. This is calculated by using a product calculation. To do this I inserted a field and placed in the formula {=product(b15,17.5%)}.
This screenshot shows the formula after it has been inserted
This screenshot shows a calculation of formula for VAT as you can see the value is £5941.250.
To test the calculation I used the calculator accessory and did the calculation based upon the above amount this matched the calculation that appeared in the table.
Amount after VAT calculation
The amount after VAT calculation is a sum to add up the two fields the subtotal before VAT, plus the VAT field. To create this calculation I added a field and inserted the formula {=SUM(b15,b16)}.
Testing the Amount after VAT calculation
As you can see from the screenshot below that calculation brings back a figure of £39,891.25.
To test the calculation I added up the two values from the VAT field and the Subtotal before VAT using the accessory calculator and it gave me a figure of 39891.25.
Total Amount Payable after Discount Calculation
The Total Amount Payable field is a simple calculation to add up the Total Discount field and the Amount after VAT field. To do the calculation I inserted a field and placed the formula {=sum(b17,b18)}.
Testing the Total Amount Payable after Discount Calculation
As you can see from the screenshot below that calculation brings back a figure of £33,907.562.
To test the calculation I added up the two fields with the accessory calculator which returns the same amount as the calculation.
The Signature Field
The Signature is very complex I have used two different formulas to complete it; a nested IF function and an Insert picture function.
The IF function allows you to set a question, and then tests that question as to whether or not something is at that value. If it is at that value it returns a pre set parameter. If it is not at that value then the formula returns a second pre set parameter.
The Insert picture function allows you to insert a picture into word, similar to what is done with the File>insert picture>From file function.
What I wanted to do was create a formula that would look at the Approved by Merge filed from the Access Database. The screen shot below shows the actual formula that I inserted to return the four signatures.
The formula begins with the IF function of: If the approved field is Matthew James, then insert the picture of the signature image of mdjames signature.bmp from the alevel folder. If the approved field does not Matthew James then carry out a second IF function. This second IF function looks to see if the approved by name is Martin Grainger. If it is then return the signature image of grainger signature.bmp. If not then carry out a third IF function. This third IF function looks to see if the approved by name is Morine stewart. If it is then return the signature image of m stewart signature.bmp. If this field does not match then carry out the fourth and final IF function. The final IF function looks to see if the approved field is Tony Henley, if it is then return the image of tony Henley.jpg. If not then return the value of N/A.
As you can see from the screenshot the approved by field has the name Tony Henley. The signature that is returned matches the name of Tony Henley.
Macros
List of Individual macros created Microsoft Word Macro facilities
To help staff with the making of this letter and future letters I created three macros:
- Open a new mail-merge blank with mail merged address fields of customers
- Insert Washout Text to tell potential customers of the event
- To insert Staff signature into a letter
MACRO 1: Open a new mail-merge blank with mail merged address fields of customers
To create the macro the first thing that I had to do was to save the mail merge document as a template (.dot) file in a new folder called “Warley Painters and Builders”. The screenshot below shows the document I have created as the template.
The screenshot below shows the file being saved.
Once the template had been saved the next thing I did was to create the macro it self to do this I used the record macro field in the Microsoft Word, Tools drop down menu. I named the macro as Macro_New_Mailmerge.
The macro Macro_New_Mailmerge does the following procedure:
- Change the file by opening the directory “Warley Painters and Builders” in My Documents
- Open the file “Mailmerge.dot”
STOP MACRO
MACRO 2: Insert Washout Text to tell potential customers of the event
The recorded macro Insert_Washout_Text was created to insert the washout text. This text tells customers about the event that is happening, when and where it is. To create this macro the first thing that I did was to create a new Microsoft Word document with the “Washout” text. The screenshot below shows the Word document I created:
This was then saved as Washout.doc as shown below.
After creating the saved document of text I create the macro it self to do this I used the record macro field in the Microsoft Word, Tools drop down menu. I named the macro as Insert_Washout_Text as shown below
The recorded macro Insert_Washout_Text does the following procedure:
- Open the file “Washout.doc”
- Select all of the text in the File
- Copy all of the selected text
- Close down the file “Washout.doc”
- And display the mail merge template mailmerge.dot
- Paste the copied text into the current file
- Format the text so that is matches the current file text
- Then backspace to delete any spaces.
STOP MACRO
MACRO 3: Insert_Signature_field
The recorded macro Insert_Signature_field was created to insert the signature of staff members. This signature field uses the formula as described above. It works by looking at the approved by field at the bottom of the first page. It then returns an appropriate signature based upon the name that appears after the document is merged.
To create the macro I used the macro recording facility n the Tools Menu of the Microsoft Word Toolbar and named it Insert_Signature_field:
The recorded macro Insert_Signature_field does the following procedure:
- Open the file “signature field.doc”
- Select all of the text in the File
- Copy all of the selected text
- Close down the file “signature field.doc”
- And display the mail merge template mailmerge.dot
- Paste the copied text into the current file
STOP MACRO
Visual Basic code for Macros added to this document
The screenshot below shows the Visual Basic code for the three macros that I have created for this mail merge document.
Modification of the toolbar to add new Macros
The standard MS WORD™ toolbar was then modified to include a new icon for each of the three macros that I have just created To do this I selected the Tools menu from the Microsoft Word Toolbar and chose the options Customise > Commands > Macro as shown below.
The three new macros where then dragged and dropped onto the toolbar. To modify the image for each of the macros I right clicked onto each of the buttons added to the toolbar and then selected Edit Button Image as shown below.
MACRO 1: Open a new mail-merge blank with mail merged address fields of customer
The following screenshot shows how the Macro_New_Mailmerge button was modified on the toolbar
MACRO 2: Insert Washout Text
The following screenshot shows how the button Insert_Washout_Text was modified on the toolbar.
MACRO 3: Insert_Signature_field
The following screenshot shows how the Insert_Signature_field button was modified on the toolbar
