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

[John O'Malvern]

Until now the database generates a report of one A4 sheet per member which is ('printed' to pdf, and then) sent to someone else to print on paper. Some members require more than one different forms. (If the email process will work, then it won't matter that some members get unneccessary forms, everyone can get everything, and ignore what they don't need!)
The 2-4 sheets of paper have then been sent out by mail, so postage costs.
I'm hoping to do it all by email in December. I see there is help to generate emails with (as I understand it) individual pages from Access reports (e.g.) attached, e.g. to individuals (codekabinett.com/rdumps.php?Lang=2&targetDoc=send-email-access-vba-cdo and related webpages )
I would also like to send an image of a membership card, which presumably can be made as an Access report (so including the individual's Memb No and details, etc, as needed) my intention being that it can be downloaded onto a smart phone, and stored on the home page, to be clicked/tapped on to appear for showing when neccessary.
My problem (so far!) is i'm not sure how to go about this. Will the emailing techniques referred to send individual pages of a report to the appropriate recipients (I assume that is what it is there for) but does it need to go through a print to pdf stage before mailing out?
I would really appreciate being given a stage by stage overview of the process of setting up my system to do this. (I do have the full Outlook on my pc, so that's good, if needed.)
NB I'm not a programmer. I could copy and paste some code, obviously, and probably change some labels or variables for mine successfully. Otherwise I'm back to clicking my way through generating a macro.
Any help greatly appreciated.
John

 

[The_Doc_Man]

E-mail sent using VBA to select a single page from a multi-page report might be trickier and more tedious than generating multiple one-page reports and sending them individually. It is more logical in the sense that it approaches the problem with a "divide and conquer" method.

Build a loop that goes through the intended recipients one person at a time.

For each person, open the report using a filter that limits it to the data for a single person.

You have a choice here:

- If the format of the Access report is acceptable, you could use the direct .SendTo option to send the mail.
- Your choice of e-mail client software may figure into this choice, since the SendTo action has to know how to diddle with the e-mail client. You can do this direct e-mail easily with Outlook as your client. Not sure about other clients but I know the ability is not uniform. We have had a few other threads where manual message creation was required because .SendTo couldn't send what it could capture.
- If you are dead-set on sending a PDF then you first have to save the report to a file in PDF format after which you can send the file as a minimum explanatory message plus an attachment of the PDF document.

Whichever way you decide to go, just test it before committing to it.

 

[John O'Malvern]

Sorry for the delay in responding, and now of course, I'm short of time!
I'm not good at programming, but see there is the EmailDatabaseObject macro. Is it reasonable to use another macro to cycle through the output of a query, or better, perhaps, to use a macro to cycle the query through that selected set of records, to generate separate emails to each club member?
(Probably not useful, but the appropriate email address is an item of data appearing in the report. )

 

[Gasman]

Most here do not use macros.
However yes is the short answer. I myself used Outlook automation to send emails.

DoCmd.RunMacro method (Access)

Office VBA reference topic

learn.microsoft.com

I have no idea though, as to how one would pass the relevant parameters to the email macro.

 

[GPGeorge]

Sorry for the delay in responding, and now of course, I'm short of time!
I'm not good at programming, but see there is the EmailDatabaseObject macro. Is it reasonable to use another macro to cycle through the output of a query, or better, perhaps, to use a macro to cycle the query through that selected set of records, to generate separate emails to each club member?
(Probably not useful, but the appropriate email address is an item of data appearing in the report. )

It might not make much difference in the current requirement, but keep in mind that your future use of automation for Outlook from Access is going to be curtailed.

Breaking Changes: New Outlook Auto-Migration Begins January 2025

Microsoft announces timeline for breaking your Access applications: automatic migration to New Outlook begins January 2025. Here's why you shouldn't rush to rewrite everything (yet).

nolongerset.com

 

[Gasman]

Another reason not to be at the forefront of technology. I have not seen hide or hare of this New Outlook.

 

[GPGeorge]

You should see this in the upper right-hand corner in Outlook....

 

[Mark_]

For better responses, I would suggest breaking down exactly what you are trying to do, step by step, then getting guidance on each piece. For example,

1. Have you been able to generate the card for each individual?
2. Have you been able to loop through the members to generate a card for each?
3. Have you been able to save each card individually so you can attach them?
4. Have you been able to generate an Email to each member?
5. Have you been able to generate an Email to each member that includes an attachment?

If this is what you are trying to do, please let us know what you can already do and what you need help with.

 

[Gasman]

You should see this in the upper right-hand corner in Outlook....
View attachment 117467

Nope. FWIW, I only just upgraded to 2019 a few months back, purely to be able to view later Access Dbs. 2007 was fine for me otherwise.

 

[GPGeorge]

Nope. FWIW, I only just upgraded to 2019 a few months back, purely to be able to view later Access Dbs. 2007 was fine for me otherwise.
View attachment 117470

Oh, my apologies. I should have noted that this New Outlook is for M365 versions.

Eventually, when perpetual license versions go out of support and newer versions are released, I am speculating that we probably can anticipate a similar ultimatum in licensed versions.

 

[Gasman]

Well, I will probably stop using Access or be dead before then.
I am 70 now, and have never been an Access developer, just someone in an office, who created dbs to make work more efficient/easier. Like a lot of people who come here I believe.

 

[John O'Malvern]

For better responses, I would suggest breaking down exactly what you are trying to do, step by step, then getting guidance on each piece. For example,

1. Have you been able to generate the card for each individual?
2. Have you been able to loop through the members to generate a card for each?
3. Have you been able to save each card individually so you can attach them?
4. Have you been able to generate an Email to each member?
5. Have you been able to generate an Email to each member that includes an attachment?

If this is what you are trying to do, please let us know what you can already do and what you need help with.

Thanks - very sensible.
The image of the membership card is not important, but renewal paperwork, currently consisting of pdfs printed and posted, is what I need to replace with emailing if possible. Previously differing members have had slightly different data and in some cases additional pages, but again that is not important this time. Everyone can get everything, if that brings a solution!
1 yes, each individual gets their own page of a multipage access report. Basically each record goes to one page. Here lies the first difficulty as the paper sheets can be sent separately, a multipage pdf, not so!
2. not me, Access loops through the records to generate the pages of the report. (I suppose)
3,4,5 not tried yet.

There's always the temptation to reuse existing queries and report designs [so save time - I;m lazy and prone to mistakes!] but I suspect starting from scratch will be better in the long run.
So I need to have Access generate a report for each record [line of a query] --- or should that be:- the query generates one person's data to populate a report -> pdf -> email (using the email address in htat record, so that we know the right data is sent to the right person [GDPR/personal data issues] ...and then the query is looped to the next record. (about 100 members need to be emailed out of several hundred records).

 

[Mark_]

For step 1, I'd suggest making the report that prints the SINGLE membership card you need OR the membership card and associated documents. This would then be called by DoCmd.OutPutTo so you can save it. If you save as a PDF, you can give pass the filename as part of DoCmd.OutPutTo so you can attach it later.

Once you can make the card we should be able to walk you through a loop to call this once for each card you need to make.
Later, we can do the same for creating the Emails and attaching the cards. Trick is to use data in your member table to name things, that way you'll use the same name for the attachment.

 

[John O'Malvern]

" Trick is to use data in your member table to name things, that way you'll use the same name for the attachment. "
Could you spell that out for me, please? with an example, perhaps.

 

[Mark_]

If you have a button that calls DoCmd.OutPutTo, the syntax may look like
DoCmd.OutPutTo rptMembers, , acFormatPDF, "D:\MemberCards\" & tblMember.MemberNumber

rptMembers would be replaced with the name of your report.
You would replace the parent path to the directory of where you want to save the cards.
tblMember.MemberNumber would be replaced with what ever makes sense to have a unique name for the saved file.

Eventually, when you create the Emails to go out, you will use the same path and field to attach the card for each member.

 

[John O'Malvern]

So it's not about making it easier for us to know one thing from another, and all about uniquely identifying the right file to attach to the right email. Am I right? (Which [did I say earlier?] is very important, as I must follow GDPR rules, of course.)

 

[Mark_]

Correct. Step one would be to create what goes to EACH member. As you've found, trying to make a big PDF and sending just a page isn't something that is easy to do. Instead create the attachment for EACH member first, then later you attach it to the Email you send.

 

[Gasman]

I thought if you want to send a pdf as an attachment, I believed you would need to use Outlook automation.
If you just send the report via email then the SendObject command would work.
However just trying it, you can choose an output as pdf. I do get the 'a program is trying to send an email ' warning doing it the latter way. However I still get the pdf.
The latter method would be simpler for you, you just need to be able to filter the report for a particular member each time.
I would likely use a TempVar for that.

docmd.sendobject email - Google Search

 

[John O'Malvern]

Does it usually take a while to get a 'registered' confirmation email from UtterAccess?
I registered 30 mins ago, and no sign yet.
I'm hoping to ask for some detail in this thread:
at utteraccess.com/topics/2066496/...
The code is working great as it is, but I need optionally to add another attachment.
Everyone gets attachment A; but only some get attachment B, and only some get attachment C. (and some get A, B & C)

 

[Mark_]

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

multiple attachments to Outlook email by ACCESS VBA

Hi I intend to attach a word file, a pdf file to an Oultook with the following ACCESS VBA, I list partial scripts as follows and i don't know how I can attach more than more file to the Outlook mail. Set outMail = outApp.CreateItem(olMailItem) outMail.Attachments.Add _...

www.access-programmers.co.uk