You don't need a preposition. You could simply use:
- effective July 1, 2017
- effective 12:00 AM July 1, 2017
- effective close of business June 30, 2017 (or in association with some other event, although some types of events, such as the expiration date, would probably call for a preposition)
Instead of just "effective", you could say "to become effective", which seems a little less abrupt to me. If you really want to add a preposition:
- "on" would commonly be used with just a date. "effective on July 1, 2017"
- "at" would commonly be used with a time and date. "effective at 12:00 AM July 1, 2017" or with an event "effective at close of business June 30, 2017"
- "with" would commonly be used to associate it with an event. "effective with the close of business June 30, 2017"
"With effect of" wouldn't apply. That would typically refer to something that results, like "with the effect of your losing me as a customer".
"From" could be used with "effective", but not for a cancellation. "From" implies that something starts at that point and continues. Everything ends with a cancellation, so your non-membership would continue, but cancellation is a one-time event. But you could talk about membership "effective from..." if your notice was about starting a membership.
The word "effective" calls attention to the fact that the action will actually take place at a later date, not immediately upon receipt of your notice. That helps to reduce human error, but it isn't necessary. Your examples 5 and 6 are also fine "with/at the end of the current (2017/2018) membership year." In fact, my "on/at/with" examples could be used without the word "effective".
Peter's answer includes "effective immediately". That's a case where "effective" adds emphasis rather than error reduction. But that's also an example where "effective" isn't necessarily required.
Your examples 7 and 8 are covered above.
I wasn't aware of the AmE vs. BrE differences that Peter mentions in his answer, so in case it isn't obvious by now, my answer reflects AmE.