Local group [club] membership renewal by email, not post? (1 Viewer)

[John O'Malvern]

You would use .Attachment.Add for each attachment you need to add.

Is this not just attaching multiple files to any or all emails? I don't see any selection based on Boolean fields (in the Member table)?

 

[Gasman]

Well you need some way to identify what you need to send.
You could just have one report with a subreport for each option and send all 4, as you said in your original post that would not matter.

That post was from someone who does not have much of a clue about code and was asking for help, so you will need to just look at the posts from Daniel.

If I was trying to do this, I would probably set a flag for each report being produced, then use those flags to know what needs to be added as an attachment, and possibly delete the files after being sent.

 

[John O'Malvern]

Well you need some way to identify what you need to send.
You could just have one report with a subreport for each option and send all 4, as you said in your original post that would not matter.

That post was from someone who does not have much of a clue about code and was asking for help, so you will need to just look at the posts from Daniel.

If I was trying to do this, I would probably set a flag for each report being produced, then use those flags to know what needs to be added as an attachment, and possibly delete the files after being sent.

I see you were contributing to the thread on UtterAccess, the question was posed by Mike60Smart, and is actually my problem, which Mike was kind enough to take on and (with a heap of code from Daniel Pineault) return with an almost total solution for me.
It only remains for the code to be extended to include file C.
As well as sending Attachment A to everyone, it is set to optionally attach a second report and then as and when required (like attachment ) attach a third report [C], both based on a boolean field in each member's record).
I think I've 'over-explained'!
So there are two independent conditions. Is that a case of 'If... ElseIf... Else... EndIf' ?

 

[John O'Malvern]

Can anyone suggest why I'm not getting a response to registration from UtterAccess?
Does it usually take a while to get a 'registered' confirmation email from them?
I registered 2-3 hrs ago, and no sign yet.

 

[Gasman]

No. I would use separate If-End If constructs, as they can be one or all.

 

[Gasman]

Can anyone suggest why I'm not getting a response to registration from UtterAccess?
Does it usually take a while to get a 'registered' confirmation email from them?
I registered 2-3 hrs ago, and no sign yet.

The site has it's flaws.
Ask Mike to find out on your behalf.

Thanks for the heads up on the cross posting.

 

[John O'Malvern]

Thanks.

 

[cheekybuddha]

Can anyone suggest why I'm not getting a response to registration from UtterAccess?
Does it usually take a while to get a 'registered' confirmation email from them?
I registered 2-3 hrs ago, and no sign yet.

Not usually, but it will take a long time at the moment since UA's ability to send emails has been borked for a while.

I'll pass on your problem to see if you can be registered without having to get the email.

 

[cheekybuddha]

Ask Mike to find out on your behalf.

Who's Mike?

 

[John O'Malvern]

Who's Mike?

Mike60Smart who was sorting out my problem for me, and getting advice on UtterAccess.
(I'm typing some info for you, msg coming soon here.)

 

[John O'Malvern]

in the block of code_ (at utteraccess.com/topics/2066496 )
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

rstClone.MoveFirst
While Not rstClone.EOF

down to

Wend
On Error GoTo 0
There is a IF, Else, End If about Gift Aid Report.
If I repeat that below the existing one with the name of another report (Standing Order) (etc) substituted as needed,
and I add another 'strFileNameStandingOrder' at the beginning with the others, should that work?
I'll be doing my best to spot any other edits or substitutions that are required.

 

[Mark_]

In the same section where you are setting the Email TO, Email Subject, and Email body, you will also do the attachments.
The sample code in the message I linked to is:

With oMail
.To = pvTo
.Subject = pvSubj
.Body = pvBody

.Attachments.Add "c:\FMTS\Attachments\New_Curriculum.pdf", olByValue, 1
.Attachments.Add "c:\FMTS\Attachments\New_Curriculum.doc", olByValue, 1
.Attachments.Add "c:\FMTS\Attachments\New_Curriculum.xls", olByValue, 1

.Display True
.Send
End With

For your code, you would warp 2 of the attachment.add's in if statements to check your table's "Send this attachment" booleans.

 

[John O'Malvern]

In the same section where you are setting the Email TO, Email Subject, and Email body, you will also do the attachments.
The sample code in the message I linked to is:

With oMail
.To = pvTo
.Subject = pvSubj
.Body = pvBody

.Attachments.Add "c:\FMTS\Attachments\New_Curriculum.pdf", olByValue, 1
.Attachments.Add "c:\FMTS\Attachments\New_Curriculum.doc", olByValue, 1
.Attachments.Add "c:\FMTS\Attachments\New_Curriculum.xls", olByValue, 1

.Display True
.Send
End With

For your code, you would warp 2 of the attachment.add's in if statements to check your table's "Send this attachment" booleans.

Thanks, from what I can see, the code I have (which is working well, just needs extending) has tackled the problem in a very different way to the code you are showing me. (If you're interested it is in the thread referenced above in my msg 31.)
But thank you.

 

[Mark_]

I thought you were going with the code over on UtterAccess that you had posted about. As such I found a similar example that I posted and referenced.

 

[Gasman]

in the block of code_ (at utteraccess.com/topics/2066496 )
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

rstClone.MoveFirst
While Not rstClone.EOF

down to

Wend
On Error GoTo 0
There is a IF, Else, End If about Gift Aid Report.
If I repeat that below the existing one with the name of another report (Standing Order) (etc) substituted as needed,
and I add another 'strFileNameStandingOrder' at the beginning with the others, should that work?
I'll be doing my best to spot any other edits or substitutions that are required.

As I have already stated. YOU need some way of identifying what you need to send. If you had a record for each report that a member needs, then you could get that from the recordset field? So 4 attachments for 4 records.
However if you just had 4 flags on the member record, then you would check for those.

I used to do something similar when I volunteered with SSAFA, but I was inserting the data direct into the email, not using attachments.
However I still needed to determine whether I had to cc someone in. etc.

The simplest way would be one email one report, but not very professional.
I sorted my recordset by client and transaction date, so I could put as many transactions as processed in the email, be it 1 or 2,3 or 4.
You should get the picture.

The processing does depend on how your data is structured.

 

[John O'Malvern]

I thought you were going with the code over on UtterAccess that you had posted about. As such I found a similar example that I posted and referenced.

Yes, I am using the code on UA from Daniel Pineault. It's my problem that I can't see that the code you posted is simillar!
EDIT: I see now where the similarity lies. Apologies.

 

[Gasman]

@Mark_ is just showing you how to add extra attachments. You just repeat the .Add method, but with different filenames.
Over here we generally show you 'how' to do something. Over in UA they will often write the code for you, as Daniel did for Mike. However then you do not learn anything, as was evidenced in that thread.

 

[John O'Malvern]

As I have already stated. YOU need some way of identifying what you need to send. If you had a record for each report that a member needs, then you could get that from the recordset field? So 4 attachments for 4 records.
However if you just had 4 flags on the member record, then you would check for those.

I used to do something similar when I volunteered with SSAFA, but I was inserting the data direct into the email, not using attachments.
However I still needed to determine whether I had to cc someone in. etc.

The simplest way would be one email one report, but not very professional.
I sorted my recordset by client and transaction date, so I could put as many transactions as processed in the email, be it 1 or 2,3 or 4.
You should get the picture.

The processing does depend on how your data is structured.

Yes, I have flags in each member's record to control whether to include the B and/or C reports in each email.
This all works with A and B, and it remains for me to sort out the code to additionally attach C when needed. Which, presumably, is simply a question of repeating the appropriate parts of the existing code, along with additional 'dim's etc.
When i posted the code earlier in this thread, I cut out the middle (for brevity) which was probably a bad idea., I'll post the relevant part complete, soon hopefully, with another question.
For clarity, I meant
Renewal report when I referenced A
Gift Aid report when I referenced B
Standing Order report when I referenced C

 

[Gasman]

Well your flags need to determine if you should create the pdf in the first place?
Then in the Outlook part, they should also determine if they need to be added. You cannot add a file if it does not exist.

All done with a simple If and End If. I would probably use those same flags to determine what I need to delete after sending the email, or you could give them all unique names with perhaps the member ID, then delete them all from that folder right at the end of the procedure.

Daniel was not aware of the other options, so a fair bit needs to be amended to handle all three reports, subject etc?

As he stated

You really should have defined your needs initially, it would have saved a lot of time doing work for nothing.

 

[John O'Malvern]

Is it OK to post long blocks of code here? Hopefully.
This is Daniel's code [below] which works well sending attachments A and B in a single email. (A very big thank-you to him!)

My first question is where/how to add the additional code to check for attachment C ( the Standing Order) ?
Can I simply repeat this " If Else End If " (with changed references and content, of course.) ? And drop it in before the
' OUTLOOK Sending Bits ?

-------------------------------------

If rstClone![Gift Aid] = 0 Then

                strSubject = strSubjectGiftAid

                strMsg = strMsgGiftAid


                'Gift Aid Report Only Included If Selected

                strFileNameGiftAid = "C:\Emails\" & Format(Date, "mmddyyyy") & "- Gift Aid -" & rstClone!MemberNo & ".pdf"

                Call GeneratePDFofReport(sReportNameGiftAid, strFileNameGiftAid, strWhere)

            Else

                strSubject = strSubjectRenewal

                strMsg = strMsgRenewal

            End If

-------------------------------------

And I'll need to insert another one of these (and probably other references) for C

Const sReportNameGiftAid  As String = "rptGiftAid"

    Dim strFileNameGiftAid    As String

i.e.

Const sReportNameStandingOrder  As String = "rptStandingOrder"

    Dim strFileNameStandingOrder    As String

Here's Daniel's code complete:
----------------------------------------------

Private Sub cmdEMailReports_Click()

    On Error GoTo Error_Handler

    If Me.Dirty Then Me.Dirty = False

    Dim OutApp                As Object

    Dim OutMail               As Object

    Dim rstClone              As DAO.Recordset

    Dim strWhere              As String

    Dim strEmail              As String

    Dim strMsg                As String

    Dim strSubject            As String

    'Renewal

    Const strSubjectRenewal   As String = "Renewal Attached"

    Dim strMsgRenewal         As String

    Const sReportNameRenewal  As String = "Renewal Details Gift Aid & SO"

    Dim strFileNameRenewal    As String

    'Gift Aid

    Const strSubjectGiftAid   As String = "Gift Aid and Renewal Attached"

    Dim strMsgGiftAid         As String

    Const sReportNameGiftAid  As String = "rptGiftAid"

    Dim strFileNameGiftAid    As String

    'General constants

    Const olMailItem = 0

    Const olFormatHTML = 2


    strMsgRenewal = "<!DOCTYPE html>" & _

                    "<html lang='en'>" & _

                    "<head>" & _

                    "<meta charset='UTF-8'>" & _

                    "<style>" & _

                    "p {font-size: 11pt; font-family: Calibri;}" & _

                    "</style>" & _

                    "</head>" & _

                    "<body>" & _

                    "<p>Attached are your Renewal details.</p>" & _

                    "<p>Any queries please let me know.</p>" & _

                    "<p>Thank You</p>" & _

                    "</body>" & _

                    "</html>"


    strMsgGiftAid = "<!DOCTYPE html>" & _

                    "<html lang='en'>" & _

                    "<head>" & _

                    "<meta charset='UTF-8'>" & _

                    "<style>" & _

                    "p {font-size: 11pt; font-family: Calibri;}" & _

                    "</style>" & _

                    "</head>" & _

                    "<body>" & _

                    "<p>Attached are your Renewal and Gift Aid Forms.</p>" & _

                    "<p>Any queries please let me know.</p>" & _

                    "<p>Thank You</p>" & _

                    "</body>" & _

                    "</html>"


    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 = ""

            strEmail = rstClone!Email    'To e-mail address

            strWhere = "ContactID = " & rstClone!ContactID    'Report WHERE


            'Renewal Report Included For All Cases

            strFileNameRenewal = "C:\Emails\" & Format(Date, "mmddyyyy") & "- Renewals -" & rstClone!MemberNo & ".pdf"

            Call GeneratePDFofReport(sReportNameRenewal, strFileNameRenewal, strWhere)


            If rstClone![Gift Aid] = 0 Then

                strSubject = strSubjectGiftAid

                strMsg = strMsgGiftAid


                'Gift Aid Report Only Included If Selected

                strFileNameGiftAid = "C:\Emails\" & Format(Date, "mmddyyyy") & "- Gift Aid -" & rstClone!MemberNo & ".pdf"

                Call GeneratePDFofReport(sReportNameGiftAid, strFileNameGiftAid, 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

                .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


Error_Handler_Exit:

    On Error Resume Next

    Set OutMail = Nothing

    Set OutApp = Nothing

    Set rstClone = Nothing

    Exit Sub


Error_Handler:

    If Err.Number <> 2501 Then

        MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _

               "Error Source: cmdEMailReports_Click" & vbCrLf & _

               "Error Number: " & Err.Number & vbCrLf & _

               "Error Description: " & Err.Description & _

               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _

               , vbOKOnly + vbCritical, "An Error has Occurred!"

        Resume Error_Handler_Exit

    End If

End Sub


Private Sub GeneratePDFofReport(ByVal sReportName As String, _

                                ByVal sPDFFileName As String, _

                                Optional ByVal sReportWHERE As String)

    On Error GoTo Error_Handler


    DoCmd.OpenReport sReportName, acViewPreview, , sReportWHERE, acHidden ' acWindowNormal '- No need to make it display to the user!

    DoCmd.OutputTo acOutputReport, sReportName, acFormatPDF, sPDFFileName, , , , acExportQualityPrint

    DoEvents

    DoCmd.Close acReport, sReportName

 

Error_Handler_Exit:

    On Error Resume Next

    Exit Sub


Error_Handler:

    If Err.Number <> 2501 Then

        MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _

               "Error Source: GeneratePDFofReport" & vbCrLf & _

               "Error Number: " & Err.Number & vbCrLf & _

               "Error Description: " & Err.Description & _

               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _

               , vbOKOnly + vbCritical, "An Error has Occurred!"

        Resume Error_Handler_Exit

    End If

End Sub

--------------------------------------------------------