Local group [club] membership renewal by email, not post?

[John O'Malvern]

@Mark_
The code (above) says , I believe,
IF GA flag is false 'member not signed up
name a pdf file including GA in the filename
generate that pdf
Else
just the Renewal

the code comment:- 'Gift Aid Report Only Included If Selected
should for clarity read
'Gift Aid Report Only Included If Selected *by this IF in the code*

It seems to me that I need to test for the GA declined flag first?
IF not declined
and IF not signed up
generate pdf

 

[John O'Malvern]

So you have three states?

1. They have yet to be asked about Gift Aid
2. They have been asked and have agreed to sign up
3. They have been asked and have declined

For 2, send them the gift aid form.
For 3, do not send them the gift aid form
For 1, what do you want to do?

Really this data belongs in a separate table, not in the Contacts table (though you can leave it there if you're lazy!)

In a linked table you could record the ContactID as a foreign key, the date of request and their response.

If the ContactID is not present then they haven't been asked yet. If it is there then you know when they were asked and also whether they want to do it or not.

If you leave it in the Contacts table then a Null value for DateAsked would indicate that they have yet to be asked and a boolean field can indicate their response.

Not really the three states as you say.
GA is encouraged as soon as they join.
Some ignore/think about it/procrastinate etc; some sign up, and some say 'no I won't'.
We send a GA Declaration form (what we are discussing) (which if filled in and returned we have to store adn show to HMRC as required) to anyone who has yet to sign up, but who has not said 'no I won't'
I take your point that there may have been better ways to structure this info.

 

[cheekybuddha]

Not really the three states as you say.
GA is encouraged as soon as they join.
Some ignore/think about it/procrastinate etc; some sign up, and some say 'no I won't'.
We send a GA Declaration form (what we are discussing) (which if filled in and returned we have to store adn show to HMRC as required) to anyone who has yet to sign up, but who has not said 'no I won't'
I take your point that there may have been better ways to structure this info.

So what do you want to do with the procrastinators? Send form anyway?

If so, you only need test for those who said "No", and not send the info to them

 

[John O'Malvern]

So what do you want to do with the procrastinators? Send form anyway?

If so, you only need test for those who said "No", and not send the info to them

Yes, send form anyway (I said).
Agreed, test for NOs first, before testing for 'if signed up'.
I'll work on some code.

 

[cheekybuddha]

Agreed, test for NOs first, before testing for 'if signed up'.

But no need to test for if signed up.

They are either "No" or "Yes"/procrastinating. (as long as they can only be "Yes" if they have signed up!)

 

[John O'Malvern]

But no need to test for if signed up.

They are either "No" or "Yes"/procrastinating. (as long as they can only be "Yes" if they have signed up!)

I'm testing for if signed up, as there's no point in sending a form to those already signed up. (OK, no terrible consequences if you do, but....)

 

[John O'Malvern]

Here's my first stab at the modified code.

the only bit I've added (or changed) is inserting
If rstClone![Declined] = 0 Then
(and its Else)

All I'm asking is:
have I inserted the extra IF, THEN, ELSE (referring to Declined) correctly?

The code before is dim, rst, etc and defining msg strings

Set rstClone = Me.RecordsetClone
    If rstClone.RecordCount = 0 Then
        MsgBox "No records for report!"
        Exit Sub
    Else
        Set OutApp = CreateObject("Outlook.Application")
        OutApp.Session.Logon

        rstClone.MoveFirst
        While Not rstClone.EOF
            strFileNameRenewal = ""
            strFileNameGiftAid = ""
            strFileNameSTO = ""
            strEmail = rstClone!Email    'To e-mail address
            strWhere = "ContactID = " & rstClone!ContactID    'Report WHERE

            'Renewal Report Included For All Cases
            strFileNameRenewal = "C:\Emails\" & Format(Date, "yyyy_mm_dd") & "- Renewals -" & rstClone!MemberNo & ".pdf"
            Call GeneratePDFofReport(sReportNameRenewal, strFileNameRenewal, strWhere)


          If rstClone![Declined] = 0 Then               'If Declined is false, we may need to generate a GA form.
                    If rstClone![Gift Aid] = 0 Then           'And If Gift Aid is also false, we need to generate a GA form.
                        strSubject = strSubjectGiftAid
                        strMsg = strMsgGiftAid

                        'Gift Aid Report Only Included If Required
                        strFileNameGiftAid = "C:\Emails\" & Format(Date, "yyyy_mm_dd") & "- Gift Aid -" & rstClone!MemberNo & ".pdf"
                        Call GeneratePDFofReport(sReportNameGiftAid, strFileNameGiftAid, strWhere)
                   Else   'John asks, should this 'else' be here?      
          Else
              strSubject = strSubjectRenewal
              strMsg = strMsgRenewal
           End If

           If rstClone![Standiing Order] = 0 Then     'Yes, the field name was misspelled
                    strSubject = strSubjectSTO
                    strMsg = strMsgSTO

                    'Standing Order Report Only Included If Selected
                    strFileNameSTO = "C:\Emails\" & Format(Date, "yyyy_mm_dd") & "- Standing order -" & rstClone!MemberNo & ".pdf"
                    Call GeneratePDFofReport(sReportNameSTO, strFileNameSTO, strWhere)
            Else
                    strSubject = strSubjectRenewal
                    strMsg = strMsgRenewal
            End If

            '   OUTLOOK Sending Bits
            Set OutMail = OutApp.CreateItem(olMailItem)
            On Error Resume Next
            With OutMail
                .To = strEmail
                .Subject = strSubject
                .BodyFormat = olFormatHTML
                .HTMLBody = strMsg
                .ReadReceiptRequested = False
                If strFileNameRenewal <> "" Then .Attachments.Add strFileNameRenewal
                If strFileNameGiftAid <> "" Then .Attachments.Add strFileNameGiftAid
                If strFileNameSTO <> "" Then .Attachments.Add strFileNameSTO
                .Display   'or use .Send to immediately send. As this runs with .Display it will save the emails in your drafts.
            End With

            On Error GoTo Error_Handler
            Set OutMail = Nothing
            rstClone.MoveNext
    Wend
    On Error GoTo 0
    End If

after this there is error handling.

 

[Mark_]

Try it and see if it does what you want. First step in learning how to debug.

 

[Gasman]

Try walking your code and seeing what you get.
You are overwriting the email subject for a start.

 

[John O'Malvern]

My request for you to check the code was a mark of my insecurity, and lack of experience in debugging.
However, after sorting a couple of glitches (it complained of a Wend without a While, which was there, confusing me greatly, and the actual problem was a missing EndIf (!) , and me taking the Label Declined as the field name, which was something different of course, ) but it now all works.
Effin' fantastic, you wouldn't believe how pleased I am.
I can claim VERY little credit, but thank you all for your help and encouragement, even if it felt more like a sharp stick than a carrot!
But don't be too relieved it works, as no doubt, "I'll be back!"

 

[Gasman]

Effin' fantastic, you wouldn't believe how pleased I am.

Congratulations At least YOU have learnt something.
If commands have terminators like IF and End If, then they must match at the same level, else you will get errors like that.
No good ending a Select Case with a Wend, like someone did, That will never work.
You can claim credit, as you buckled down and attempted it yourself, did not go asking others to write it for you.

If we had used a carrot, we would still be working on it.

 

[Mark_]

My request for you to check the code was a mark of my insecurity, and lack of experience in debugging.
However, after sorting a couple of glitches (it complained of a Wend without a While, which was there, confusing me greatly, and the actual problem was a missing EndIf (!) , and me taking the Label Declined as the field name, which was something different of course, ) but it now all works.
Effin' fantastic, you wouldn't believe how pleased I am.
I can claim VERY little credit, but thank you all for your help and encouragement, even if it felt more like a sharp stick than a carrot!
But don't be too relieved it works, as no doubt, "I'll be back!"

Very glad to hear! Our FIRST goal is to help teach you how to do things, then encourage you to try them. Next is to get your comfortable enough to help others, that is where the real learning happens!

 

[John O'Malvern]

Congratulations At least YOU have learnt something.
If commands have terminators like IF and End If, then they must match at the same level, else you will get errors like that.
No good ending a Select Case with a Wend, like someone did, That will never work.
You can claim credit, as you buckled down and attempted it yourself, did not go asking others to write it for you.

If we had used a carrot, we would still be working on it.

As you say, "Teach a man to fish..."
Thanks.

 

[John O'Malvern]

Very glad to hear! Our FIRST goal is to help teach you how to do things, then encourage you to try them. Next is to get your comfortable enough to help others, that is where the real learning happens!

" ...that is where the real learning happens!" I've learnt that elsewhere in life, that you learn a lot by teaching.