Bulletin Address Coupons

The following contacts on CiviCRM (CRM for short) will be mailed a printed copy of Bulletin:

• Any Member or Associate with an Expiry Date greater than two years ago;
• Any Donor (i.e. not a Member or Associate) who has made a donation in the past two years; and
• Anyone else who has asked to be added to our Mailing List in the past two years (i.e. Inquiries).

Two copies must be sent to Library and Archives Canada and to Jim McIntosh (or whoever is maintaining the archives for the Party).

Bulletin is mailed quarterly:

• Early March, includes the Financial Statement for the past year compared to the previous year, and the spending budget for the past year and the new year;
• Early June, to promote the annual BBQ and announce the AGM or Convention;
• Early September with details for the AGM/Convention and Registration and Proxy forms; and
• Early December including a report on the AGM/Convention with election results, an appeal for year-end funds and a reminder of the tax credits.

Each copy is mailed with a return envelope and a donor coupon. The donor coupon also has the address positioned to fit in a standard #10 window envelope. (A donor coupon is not required for Library and Archives Canada

Before sending Bulletin to the printer It is best to first determine the number of copies to be mailed. Additional copies are required for sending to new members. The number is usually rounded up by 15-25 for this purpose.

Extracting Data from CRM

CRM has separate Search functions for Memberships, Donations and Inquiries. It is not possible to "Extract" donation information when searching for Members, or Membership information (other than Member or Associate) such as Start and End Dates when searching for donations. Make a separate extract for each category and merge the results.

A contact can have multiple addresses and Emails. Select only the "Primary" one for the Extract record.

Phone Numbers

CRM allows for five Phone Locations (Home, Work, Billing, Main and Other) and five Phone Types (Phone, Mobile, Fax, Pager and Voicemail). In theory a contact could have 25 different phone numbers. In practice, the page where they enter their contact information only allows for two numbers; "Primary Tel# - (for voice)" and "Tel# Mobile (for SMS)." Most people with a cell phone enter the same number in both places. (It might help if the page put "Cell Phone" first and then "Home Phone (if different)" second. It might also be useful to have a field for "Work Phone." The previous Excel Contact list also had a Work phone number, which could be in either the Phone or Mobile Type. Alternatively, we could ignore work phones and not print them on the address/donor coupons.

Search for and Extract Members

1. Click on <Memberships - Find Memberships>.
2. Check the "Party Membership" box.
3. In the "End Date" select "Choose date range."
4. Click on the calendar for the "From" date.
5. In the "Year" drop down field, select two years back and click on the first day of the month. Leave the "To" date blank
6. Click on [Search].
7. Select all records and select "Action" "Export Members." This will bring up the Export page
8. Click on "Select fields for export."
9. Select "Bulletin Members" from the "Use Saved Field Mapping" list.
10. Click on [Continue]. This will bring up the page showing the fields to be extracted. The following fields are required for a donor/mail coupon for Members and Associates; Sort Name, Addressee, Supplemental Address 1, Street Address, City, State, Postal Code, Country, 2 Home and 2 Wok phones, Email, Member or Associate?, Membership Expiration Date, Do Not Mail and Is Deceased.
11. Click on [Extract] and wait for it to show the downloaded file.
12. Open the downloaded file and save it as the current Member Mailing List in the Ontario Libertarian Party/Members folder. (e.g. Mail 17-06.xlsx). Rename the sheet "Members."
13. Delete all records with a "1" in Is Deceased or Do Not Mail.
14. Change the Label on the last column from Membership ID to Msg
15. Sort the file by Membership Expiration Date.
16. Copy the following formula to the Msg field of the first record and propagate it to all records - �="Your Membership expired "&TEXT(N2,"dd-MMM-yy")&". To renew please go to ontariolibertarian.ca/join"�(N2 is the cell containing the Membership Expiration Date for the first record.)
17. Scroll down to a record with an expiry date in the current month. Change the word "Expired" to Expires on" and propagate it to all remaining records.
18. Scroll down to the first record with a date about a month after the expected mailing date and replace the Msg with the following formula and propagate it to all remaining records. �="Membership Expiry Date: "&TEXT(N143,"dd-MMM-yy")&". Please Make a donation to the Party"
19. Copy the Msg column and Paste Special Values.
20. Delete the Membership Expiration Date column
21. Save the file.

Search for and Extract Donors

For Donors, instead of the membership Expiry date, We need the Receive Date of the last donation made in the last two years.

1. Click on <Donations - Find Donations>
2. Donation Dates <Choose Date Range>, From the beginning of this month 2 years ago.
3. Check the Donation Status Completed box and click [Search]
4. Export all donations, using Saved Field Mapping "Bulletin Donors."
5. Open the downloaded file and save as the current Donors Mailing List in the Ontario Libertarian Party/Members folder. (e.g. Mail 17-06 Donors.xlsx)
6. Sort by Member or Associate?" and delete all records for Members and Associates.
7. Delete all records with a "1" in Is Deceased or Do Not Mail.
8. Eliminate duplicate Names
   1. Sort by Received Date, then by Sort Name
   2. Insert a column after Sort Name, label it Last
   3. In the first row, insert the formula - =IF(A2=A3,0,1) in the Last column and propagate it to all rows.
   4. Copy the contents of the column, and then <Paste - Special - Values>.
   5. Sort by Last and delete all rows with a "0" in Last.
   6. Delete the Last column
9. Use the same procedure to create the Home, Cell and Work Phones as for Members.
10. Change the label in the last column to "Msg"
11. Sort by Receive Date,
12. For all records with a Receive Date more than a year ago, insert a message such as; �"Thank you for your support. Please make a donation to help us compete in the next election."
13. For all records with a Receive Date in the last year, insert a message such as: �"Thank you for your donations. We appreciate your support."

(There may be a way to select "donations" or "donation" depending on the donor.)

1. Copy the Msg column and <Paste-Special-Values>.
2. Delete the columns for Receive Date and Total Amount
3. Fill the Member or Associate? Column with "Donor".
4. Copy the sheet to the first file (Mail 17-06.xlxs) as the second sheet and rename it "Donors"

Search for and Extract Inquiries

1. <Contacts - Advanced Search>
2. For Groups select "Inquiries (no membership, no donation)"
3. Exclude "Do not mail"
4. Select <Change Log> bar
5. For "Modified Between" in the "From" field select the first day of the current month 2 years ago.
6. Click on [Search]
7. Export all records using Field Mapping "Bulletin Mailing"
   1. For "Merge Options" select - Merge All Contacts with the Same Address
   2. Check the "Postal Mailing" box
8. [Continue] and [Export]
9. Open the downloaded file.
10. Sort by country and delete all US addresses that look like test data
11. Delete all records with ***MOVED*** in the "Street Address" field.
12. Save as the current Inquiries Mailing List in the Ontario Libertarian Party/Members folder. (e.g. Mail 17-06 Inquiries.xlsx)
13. NOTE The Party is required to send two copies of each issue to Library and Archives Canada. This record will be in this file with Internal Contact ID = 558. Do not delete this record.
14. Use the same procedure to create the Home, Cell and Work Phones as for Members.
15. Change the label in the last column to "Msg"
16. For all records, insert a message such as; �"Please send a donation to help the Party, or better yet, join us at ontariolibertarian.ca"
17. Fill the Member or Associate? Column with "Inquiry".
18. Save the file.

Search for and Extract Exchanges

A few records are identified as "Exchange." The primary one is Library and Archives Canada, and we must send two copies of each issue to them. Another is Karen Selick, an early member and active in a number of liberty oriented groups.

MS Publisher and Window Envelopes

The donor/mail coupon is formatted so that the address will show in a #10 window envelope. These are printed 3 per page a Publisher Mail Merge file, Mail Coupons Merge.pub. (MS Publisher is used so that the 3 address boxes can be fixed at the correct positions on the page. MS Word does not allow Merge Fields in a text box so is difficult to format properly.)

MS Publisher does not have a "Next Record" merge command, so each sheet of 3 address coupons must be one record, or one row in the spreadsheet. The second and third coupons must have unique names or column headers.

MS Publisher supports the "Address Block" which allows for extra lines in the address (e.g. Supplemental Address 1 and 2) but does not 'print' blank lines if the field is blank. However the Address Block is matched to only one set of address fields in each row of 3 addresses. Therefore the second and third addresses on the page must spell out each line. If the Supplemental Address 1 is blank, a blank line will be 'printed.' Accordingly, all addresses with data in the Supplementary Address field(s) should be in the first group of records.

One other limitation of MS Publisher; if you change the list of contacts all of the Merge Fields are removed and new ones must be inserted. To avoid this, copy the records to the same MS Publisher file used last time, e.g. Address Coupon Print.xlsx.

Create 3-up Merge File

Combine each group of extracted contacts (Members, Donors and Inquiries) into the Print file, Bulletin Mailing Coupons.xlxs workbook. Make sure all columns for each group are in the same sequence.

1. Copy the Member contacts to the Print file
2. Copy the Donors to the Print File, including the Header row,
3. Make sure this Header row matches the Print file Header row and delete it
4. Repeat these last two steps for the Inquiries contacts.
5. Change the Label (column Heading) "Home-Phone-Mobile" to "Cell." Change the label "Work-Phone-Phone" to "Work."
6. Insert a column labeled "Home" after the "cell" column If "Home-Phone-Phone" is the same as "Cell", leave "Home" blank, otherwise make it the same as "Home-Phone-Home." [=IF("Home-Phone-Phone"="Cell","","Home-Phone-Phone")]
7. Filter the "Work-Phone-Mobile" to select non-blank fields. For any row with a blank in the "Work" column, fill it with the "Work-Phone-Mobile" field.
8. Delete the columns for "Home-Phone-Phone" and "Work-Phone-Mobile."

Merge contacts at same address

In order to reduce the number of mailings,

1. Delete any records flagged with ***MOVED*** in the Address column.
2. Sort the (1-up) file by Postal Code (Column G) before organizing into 3-up.
3. Insert a column labeled Duplicates after the Postal Code.
4. In the first record in Duplicates, insert the formula [=IF(OR(G2=G1,G2=G3),"DUP","") and propagate to all records. This will flag records with duplicate Postal Codes.
5. Then scan for duplicate addresses.
   1. In some cases it may be the same contact. Keep the Member record and delete the Donor or Inquiry record.
   2. One may be a company (delete) and the other the owner (keep).
   3. If both are Members keep both.
   4. If the last name is the same, change one to "Mr. & Mrs." and delete the other.
   5. If they have different last names, copy the name from one record (preferably an Inquiry) and paste it into the "Supplemental Address 1" of the other record and delete the first record.
6. Move any records with a "Supplemental Address 1" to the top of the file.
7. Move the Library and Archives and Jim McIntosh recordS to the top of the file. Both receive two copies of each issue.

Create 3-up format

1. Determine the number of pages by dividing the number of records by 3. For example, if there are 520 records, there will be 173.333 or 174 pages.
2. Starting at the row number equal to the number of pages + 2 cut and paste (move) the remaining records into the second set of columns.
3. Determine the number of blank coupons. Blank coupons should be at the bottom of the third and second set of columns. In the example, there will be 2 (174 x 3 = 522).
   1. If there is only 1, it should be in the third set of columns. The second set should have the same number of records as the first.
   2. If there are 2 blank coupons, the second set will have one less record as well.
   3. Move the excess records to the third set of columns.

Create Donor/Address coupons

1. Start MS Publisher and open the Mail Coupons.pub file. Confirm you want to use the Print file as the merge file.
2. Either merge to the Printer, or to a new file and print the new file.
3. The printed sheets have guide marks on the sides for cutting the pages into coupons. Cut them manually with an Exacto or hobby knife, or take them to a copy centre or office supply store and have them cut the pages. (This expenses is recorded as "Office Supplies".)
4. Remove any blank coupons.

= Stuff and Mail Bulletins - Currently Maureen Dance is paid $200 per issue to stuff the envelops with the Address/Donor Coupon, Bulletin and #9 Return Envelope. Since the Treasurer currently prints the address coupons, he purchases the required stamps. Either he will pick up the Bulletins from the printer and deliver them with the address coupons and stamps to Maureen, or he will arrange for MDS Express to pick up and deliver them. Alternatively, if Maureen is willing to buy stamps (e.g. on her credit card) and be reimbursed with her fee, the coupons could be printed and cut before Bulletin goes to the printer and mailed to her.

Maureen will then mail any excess copies of Bulletin to the Campaign Director or whoever is responsible for for sending out New Member Kits.

Maureen will include receipts for any postage she has purchased with the two copies of Bulletin sent to the Treasurer.

The Treasurer will prepare a 2-up invoice (one for Maureen and one for the Party) for Maureen's services (Professional Fees) and for any stamps she purchased (Postage).

Resend as Required

1. Some Bulletins will be returned by the post office, either because the person has moved or the address is incomplete (e.g. missing unit number).
2. If the contact has an email ID, Email the contact and then flag the contact's address with ***MOVED***. There are two email templates in CRM to facilitate this process; "Moved" and "Moved - donor/candidate". If contact does not have an email ID and has made a donation and has not yet received an Official Receipt, follow up by telephone.

The contact may

1. Reply to the email, in which case update the contact address
2. Use the link in the email to update the address on their own, in which case the MOVED flag will be removed.
3. Take no action, in which case the contact's record will not be updated. If this is a contact who has made a donation and has not yet received an Official Receipt, follow up by telephone.