How do I - Send email from Access 2010 with selected data

[PurrSent]

Hi
I have a Membership Db, listing name, address, email etc and also Membership No, Date of Expiry of Membership

I would like to send Reminder emails prior to their Membership Expiry date which include their name and Membership Number on each individual email.

I hope that's sufficient info for a basic understanding of what I would like to achieve.

I've had a search on internet and I think this might be beyond my abilities but I'm hoping there is a way of doing this without causing me too much grief achieving this. I'm not any good with code and would likely need simple step-by-step instructions

How achievable is this, for a 'moderately' au fait user, please? Would you be prepared to help on this?

Many thanks

J

 

[Micron]

For me, you have to be a bit more specific and complete.
Do you have the query or queries you need - complete with email addresses?
Do you want to send only text or some kind of report?
Are you going run this manually for one or two members by clicking on a form button or running a macro? Or is your goal to loop through a query recordset and send each email without any other interaction?
Is this a one member/one email task because of specific expiry, name, membership number, etc. requirements or one email/multiple recipients?
Are you going to use an email template or create the email on the fly?
It's not a super difficult task, but it's not a 30 minute job either (as you might gather from my questions) unless someone has something already done that just happens to fit your needs. So by "help" do you mean create for free or just give you high level guidance?
It would simplify things if each recipient could be added to the BCC field, but they would all receive the same text.

 

[PurrSent]

Thanks very much for your response. I do appreciate it.

It's not easy to know how much information to put in the initial post. Obviously, it needs a certain amount so that readers have some idea of what is being asked. However, I didn't want to bog it all down by giving unnecessary information.

I have found your questions useful and so I have worked through them as best I can. I'm happy to provide what information I can, as required.

Q1 - Do you have the query or queries you need - complete with email addresses?

I haven't set the queries up for this as I'm not sure what it needs. However, I am able to create queries

The email addresses are recorded in the database in their own field
Not every member has provided an email address so those without, I have to post out to. I have a separate query for those with and those without email addresses. The email address field can easily be included in the query, if required

Q2 - Do you want to send only text or some kind of report?

I will need to send text and form which is currently in both Word and as a template in my email client, Thunderbird. I don't have it as a Report in Access. I assume I will need to set up the required account in Outlook

Q3 - Are you going run this manually for one or two members by clicking on a form button or running a macro? Or is your goal to loop through a query recordset and send each email without any other interaction?

I will have from 1 to >20 members to send out reminders to each month. All memberships expire at the end of the relevant month (12 month membership) so it is my intention to send out a 'batch' each month. So, those whose membership expires end Aug, I'd like to send out mid July; those expire Sept, send out mid- August etc.

I currently have a query, which I adjust the month to what's required, ie in criteria under field re Date Expire, I have >=#01/07/2015# And <#01/08/2015# which, of course, will return those that expire at end of July

As to how some sort of manual or 'automated' set up will work, I don't know until I can appreciate how it will work, hence my asking advice.

Q4 - Is this a one member/one email task because of specific expiry, name, membership number, etc. requirements or one email/multiple recipients?

One member/one Membership No.
Because of confidentiality (DPA) only the relevant email address, name and membership number must be visible to that member.

I've just sent out a batch of emails, using template and BCC, with pdf attachment but these were without being addressed by name and didn't include their Membership No. Having their name and specific Membership No would be advantageous, hence my original post asking if/how it could be achieved.

Q4 - Are you going to use an email template or create the email on the fly?

I have a template for the message and a pdf attachment (a membership form for the Member to print off, complete, and scan to return to me if paying on line or to return by post if paying by cheque)

Q5 - So by "help" do you mean create for free or just give you high level guidance?

I'm hoping for guidance, please. I don't expect anyone to set it all up for me - neither would that help me learn. I enjoy learning how to do this but although studying Access and databases in some depth when I studied Business Information Technology Degree (and achieved 2:1 Hons) it was over 10 yrs ago and I've had little opportunity to use Access to this level since.

Hence, I'm keen to learn but definitely need help in this, please.

Q6 - It would simplify things if each recipient could be added to the BCC field, but they would all receive the same text.

Yes, I appreciate this but I'm wanting to have each email addressing the Member by name and also to include the Membership No so, although the main body of the message would be the same text, it would need this individuality too. That's my question - is this possible, without too much complication.

I can easily send BCC emails out to a 'batch' of members manually. My difficulty is how to get 'personalisation' (ie name and M'ship No) on each.

I appreciate any help to achieve this. Thanks very much
Julie

 

[ranman256]

I have form, frmRpts, and reports in a list box. lstRpt
And a list of users and their emails, lstUsers
The user picks a report in the list, then the button Send will email each person in the list.

Depending on the report,
some report queries will read the lstUsers name and configure the report JUST for that person's data.

'------------
Public Sub ScanAndEmail()
'------------
Dim vTo, vSubj, vBody, vRpt
Dim vFilePath
dim i as integer


vBody = txtBody
vSubj = txtSubj


     'scan the list box
For i = 0 To lstUsers.ListCount - 1
   lstUsers = lstUsers.itemdata(i)
   vUser = lstUsers
   vTo = lstUsers.Column(2)
   vSubj = lstRpts & "-" & date()
   vBody = "Dear " & vUser
  
   DoCmd.SendObject acSendReport, lstRpts, acFormatPDF, vTO, , , vSubj, vBody
Next
End Sub

 

[PurrSent]

Thanks, ranman, for responding

However, your reply is far beyond my understanding, I'm afraid.

I was hoping for a much simplified form of help. I did say I was only of 'moderate' ability, certainly not advanced. It's difficult to classify anyone's ability, in my opinion. I only know what I know, which would seem to be 'advanced' to some but 'beginner' to others. It's all relative.

Anyway, as I said, I don't understand your post. If you could explain in more simplified terms, it may help.

I do appreciate any help I'm given here, of course, but if I understood what to do, I wouldn't be asking. I'm still hopeful there is a reasonably straight forward solution to achieve what I'd like to achieve.

Many thanks
Julie

 

[Micron]

I don't want to speak for ranman and welcome his input as ranman's Access knowledge probably exceeds my own. I think when he explains, you will discover that his method requires your Word document to be an Access report as it is the only object I know of that can be sent which could resemble a Word document. I will leave you with some information and a few more questions that will hopefully assist you with a decision once ranman elaborates. You can weigh his SendObject approach against your need for a Word attachment.

If you must use Thunderbird, I believe you will have to use CDO (Collaborative Data Objects). AFAIK, TB does not support scripting (e.g. interaction with VBA). This is a more complicated method (you must know the protocols of your email provider), but is very powerful and flexible. You can send email without interaction. Combined with Windows Task Scheduler, you can even send them without being there. However, I could not commit to the level of free support you'd need.

I do not know if Outlook Express supports scripting or automation, so I could not commit to that either. If you can use Outlook, then yes.
This would mean your approach would be Automation. Relatively easy - a bit more complicated than SendObject, but I'm very certain it supports attachments such as Word, pdf, Excel, etc., and from what I've read, you can use a template (.oft). Easier if you don't, though.

To use a template, you would need to do the legwork in getting this to work: https://support.microsoft.com/en-us/kb/161088. You don't have to use the Northwind database; you can use your own. Or you can Google something like Outlook automation template as 3 keywords, not a phrase. After that, I'd support getting a list of recipients using your query and looping through the list to send emails. I have some experience with Automation and with CDO but I'm not an expert.

 

[PurrSent]

Yes, I'd like to know more about the SendObject approach by ranman but require more information as to what that involves. As I posted previously, I have difficulty understanding the post so would need further information and explanation, please, ranman, if you were able.

I do use Thunderbird daily but I have Outlook on my machine and am prepared to set up the email account in Outlook (not OE) if that is what is required. I don't see that to be an issue.

I didn't consider Thunderbird to be a client that would work so well with MS programs, hence I mentioned that I was prepared to set up Outlook

I don't necessarily need to use a template but I do need to use an already written letter, so it could be in Word, pdf or whatever is required. If it needs to be a Report in Access, so be it.

A Membership Renewal Form must also be sent so that it can be printed off, completed and returned to me

What I am trying to do is to send out reminders to Members. Their membership expires at the end of the month one year after joining, so I need to send out reminders monthly, to those whose membership expires the following month.

So far, it has shown that many members do not know where they have their membership number and so this can cause some difficulty to both parties. I need this renewal procedure to be as easy as possible for the member. I'd therefore like to provide their Membership No and their name in each individual email, if this is possible. In addition to a 'letter' in the body of the email, I need the Renewal Membership Form to be attached so it can be printed off, completed and returned to me

I'm more than happy to provide more information if required but I'm not sure what other information is required so please ask

I do appreciate any help that can be provided. Many thanks

Julie

 

[Micron]

Google "ms access send email using automation" and check out https://support.microsoft.com/en-us/kb/161088 and enlighten yourself while making a decision.
Also, post #1 in this thread How to send formatted email without outlook will show you code for using CDO. I have also posted there. The only iffy part for CDO, I think, is getting your sending server name. You can do this by sending yourself an email from/to yourself in Outlook and looking at the Internet headers (right click on message in list and choose properites). If you are using Hotmail or a similar service, I'd say forget it. These services are very protective against allowing this type of automation AFAIK. Regardless of automation or CDO, start with a query that gets all the info you need (names, dates, email address, membership info, etc.) which you will still need to use these methods or ranman's. If he does not reply, I'd say his method is to use a report as your attachment. The other methods will allow the use of your own Word, Excel, pdf, etc. attachment. PM me to keep the chatter down in this thread. It should be used mostly for solving a problem, not 'emailing'. Happy reading!

 

[PurrSent]

Micron

Thanks for giving me the details of what to search for.
I've made a start on reading up and trying to understand what I'm reading.
It may take a while but I'll stick with it.
Thanks for suggesting I may PM you.
I'll do that when I need to ask/provide further information

Again, thanks very much