Page 2 of 7 FirstFirst 1234567 LastLast
Results 16 to 30 of 101

Need help with VB to send multiple emails

  1. #16
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136

    I have had a browse at the various web pages that Andy has identified. However they are all pretty complicated and would require a lot of work to make them usable.

    I don't think it would be time well spent to try and go down that road, especially since the coding in Access does, at the press of a button, precisely what I want. So my only difficulty is being able to adapt the red parts of the code in my first post to draw the information from the Query that I outlined in my last post. As you will appreciate I am not skilled in VB coding, so I do need a little help to edit the coding that already works!

  2. #17
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,863
    Quote Originally Posted by Baldeagle View Post
    The code that I quoted in my first post was given to me for another exercise. How can I adapt it to draw the information from my Query called 'PartnerDonations' which has 4 fields - Partner, Date, Amount & Email...
    I do not see any issue with the code you provided. I have the impression that you have edited the code in an attempt to make it suitable for this new objective. So I am imagining you want a user to select specific recipients from a list box control. With that, base your report on the query 'PartnerDonations'.


    Quote Originally Posted by Baldeagle View Post
    My report as presently designed has about 30 pages with all the information that I need for all 30 Partners. I need to get the code to send each email with one of those pages attached.
    The code is using the Filter property of the report to limit the number of pages included. Is it not filtering properly?

    Quote Originally Posted by Baldeagle View Post
    I trust that is clear, if not, just ask for further clarification.
    I still do not understand what, exactly, is not working correctly. Is the report sending too many pages? Are the emails failing? what?

    Perhaps it would be simpler to start at the beginning by stating what you want to do. For instance...

    I have a report that works correctly. I need to email this report to multiple people. The people I need to send the report to are listed in a table, along with their email addresses. Every individual with a valid email address should get a copy of the report. The big issue for me is that each recipient should only receive the part of the report that is relevant to them. In other words, nobody should receive the entire report, only the part that is specific to them.

    OR

    I have a report that works correctly. I need to email this report to multiple people. The people I need to send the report to will be selected by the User via a multi-select List Box Control. Every individual selected by the User should get a copy of the report. The big issue for me is that each recipient should only receive the part of the report that is relevant to them. In other words, nobody should receive the entire report, only the part that is specific to them.

  3. #18
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    Thanks for getting back to me - sorry I wasn't sufficiently clear before.

    I have a report that works correctly. I need to email this report to multiple people. The people I need to send the report to are listed in a table, along with their email addresses. Every individual with a valid email address should get a copy of the report. The big issue for me is that each recipient should only receive the part of the report that is relevant to them. In other words, nobody should receive the entire report, only the part that is specific to them.
    This is basically what I need. But there is probably one other complication - the query output on which my report is based contains the following fields - Partner, Email, Date and Amount. But there are a variable number of lines depending on the number of payments made (and each of the lines contains the Partner and Email details). So for Partner 1 there could be 4 lines and for Partner 2 there could only be one line....

    After the common body of the report it has a Partner Header in which the name of the Partner is displayed and then the Detail section which displays as many lines as there are related to each Partner.

    So I want, when the relevant Button is clicked by the operator, for the code to select each of the pages relevant to each Partner and email that page (only) to the Partner - there is no need for selection by the operator as the relevant single page from the report is to go to all Partners included in the output from the Query.

    When I click on the button that I have created on the Menu I get the following message 'Compile Error - Method or Data Member not found'. And 'With Me.lstCategory' at the early part of the code is highlighted. As I explained at the beginning I am not really familiar with VB - I can edit code to change references to fields and tables but beyond that I am lost!! The full code that I had tried to adapt for the present purpose worked fine for the original purpose - it required the operator to select any number of entries from a list (which only included discreet email addresses - so there was no question of variable lines) and it sent off all the emails.

    I trust this more detailed explanation will enable you to help me - if you need any further clarification please let me know. Thanks in anticipation.

  4. #19
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,863
    When I click on the button that I have created on the Menu I get the following message 'Compile Error - Method or Data Member not found'. And 'With Me.lstCategory' at the early part of the code is highlighted.
    This is happening because there is not a control named, "lstCategory", on your form. As you already know, the VBA code has to be specific. So this is the problem you are experiencing here. It is good to look at existing code and use examples to help solve new problems. We all do this. Now, we need to create new code for the new problem.

    Before we worry about the VBA code. It seems there is not a working Report object and Query Object. Perhaps these are working fine. But, we need to test these objects and make sure all is well. How I normally go about this is to hard code values in the query. I will hard code criteria for a WHERE clause using the Query Designer.

    In your case, I would make sure that my Report is bound to the query named 'PartnerDonations' by checking the Report's RecordSource. Then I would go to design view of 'PartnerDonations' and hard code criteria. I would use a known and valid unique value from one of my tables as a value for the criteria. The idea is to open the query in datasheet view and only see data that is relevant to a single partner. The data you see should be something that you could email to that partner. After you save your design changes to your Query, take a look at your report and make sure it, too, looks like something you could email to that partner.

    After you determine your query object and report object are working correctly with the criteria you hardcoded, you can take a look at your query in SQL view and copy your WHERE clause to a temporary text file. After you save the SQL WHERE statement to notepad, you can remove the criteria from your Query via the Query Designer, switch back to Design View. Save your query without the criteria.

    Now that your query does not have any criteria, you can start building code that will add the criteria back to the query or apply it to the Report's Filter property.

  5. #20
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    Thanks for your detailed response. Unfortunately I am just about to go out so I won't get back to it until Monday.

    Just one comment. My Report woks fine as designed and its data source is my query "All Payments to Partners". Is there any way that this query name can simply be inserted into the existing code in some way or am I misunderstanding what the code is actually doing? Or would it be much of a job to to rehash it to meet my objective? I must say that your step by step suggestions are taking me into an area that I am not familiar with as I design my queries etc by using the Access grid and not the underlying coding.

  6. #21
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,863
    Continue using the Query Designer to make design changes. For instance, use the criteria row to hardcode stuff. When it comes time to use the name of your saved query object, you will also want to reference the Criteria you created in Design View and saved to notepad. There is not any need to make design changes to your Query Object via SQL view. Use SQL view to copy code and understand the syntax.

    You will not know if your report woks fine until you go through the steps I described previously. Yes, an experienced developer could probably knock out some VBA and test your objects using code, but why? That same experienced developer will test as they develop.

    As for the existing code, it will need to be modified. All it offers is a concept how to do something.

  7. #22
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,303
    FWIW, I suggest you have a query that returns data for the recipients. I don't know if you'll need a new report to accommodate the fact that there will be only one recipient's data on it when this is finished. In code, you'd need to loop through a recordset of that query. Either open the report and apply a filter using the recordset field that contains the recipient, or construct a new sql statement using said recipient and open the report with the new statement as its record source. Then email it and move to the next record and repeat. If your recordset contains the email address, subject, etc. you have easy access to those values. I haven't used DoCmd.SendObject for a while as I recall. I learnded CDO because of the security prompt for sending email via other methods, and my applications that use this send email in the middle of the night so the process is unattended.

  8. #23
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    As I indicated earlier, I will be following trough with Alan's suggestions tomorrow.

    However this is just a quick response as I am also interested in what Micron posted yesterday. I think that before I posted my first question I had thought that code which scrolled through the output from my query would select the appropriate page from the report and send each email in turn. That seems to be what Micron is suggesting. However could I just ask for a little bit more clarification? Is it possible to adapt my code in such a way that it would pick up each unique partner's email address and select the appropriate page from my report to send? Or because there are a variable number of lines in my query's output for each Partner would it be necessary to have a separate query with only the unique partners' email addresses that would in some way select the appropriate page from the report? Or would there need to be a filter added to the report which would just result in a one page report as each scroll is completed? If so how would I do that?

    Thanks again for your willingness to assist.

  9. #24
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,199
    If you do fix it, let us know mate. I'm interested.

  10. #25
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136

    Some Progress!

    This morning I have tried to take bits out of the advice that each of you has provided and I have arrived at the following situation -
    1. I created a new query (let's call it Q1 for convenience) which selects the unique Partner details (ie Name, Email Address and the Partner code which is up to 5 digits and is a unique identifier for each Partner). I then, as suggested, entered the code for the first Partner ('AIM') into the criteria. This results in a table with only the one line for AIM.
    2. I then linked this query to the original query (All Payments to Partners) which feeds the report that I need to email a page of to each Partner. Because of my new Q1 the result is a single page report with all the information that I want to email to AIM!
    3. I also have created another query (let's call it Q2) which contains the same information as for Q1 but without the criteria 'AIM' specified. This has a list of all 32 Partners with their name, Email Address and Partner code.

    Obviously my problem now is to find coding which will scroll through the output from Q2 and which will replace the criteria in Q1 with each Partner's code in turn, and which will go on to email the generated report (single page) to each Partner before selecting the next Partner's code and repeating the process for each Partner. Is that something that someone could now provide me with? I am beginning to see light at the end of the tunnel!!

  11. #26
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,863
    1. What is the name of your Q2?
    2. What is the WHERE criteria from Q1? Post it here or post all of the SQL from Q1 if you cannot discern the WHERE statement.
    3. What is the name of your Report?

  12. #27
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    1. Q2 is called 'All Payments to Partners'
    2. The WHERE criteria from Q1 (which I call 'All Payments to Partners (Single)' is WHERE (((Missions.Code)="AIM"))
    3. The Report is also called 'All Payments to Partners' and draws its information from Q2 - 'All Payments to Partners'.

    Trust this helps.

  13. #28
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,303
    I had thought that code which scrolled through the output from my query would select the appropriate page from the report and send each email in turn. That seems to be what Micron is suggesting
    Sorry, no. You might end up where you want to be, but in a kind of drunken stroll fashion!
    My favoured suggestion was to
    - use your original query to create a vba recordset of that (the one that has all recipient names)
    - get the name of the first recipient
    - open the report and apply the name as a filter
    - email the report then close the report
    - move to the next recordset record and start over until no more records.
    One query, one report. Otherwise you have more objects than what you need, and it's no easier to figure out which query to run than it would be to pass parameters to a single query via vba.

  14. #29
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,863
    @Baldeagle

    I have created some code in an effort to continue walking you through this process. Let me know if you would like me to step you through. There is more than one approach. For instance, you can use the Filter property of the report. I have mentioned this before. I am willing to share an example and walk you through another approach.

    To start, you will need to make references to the following.
    Microsoft Outlook XX.0 Object Library
    and
    Miocrosoft Scripting Runtime

    To make a reference, open the VBA editor and go to Tools > References. Then select the two references I outlined above. Click OK to close the References dialog box.

    Let me know if you would like me to assist you further.

  15. #30
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    Thanks for the assistance from both Micron & ItsMe - I'm not sure which of you will get me to the desired outcome first! But I appreciate your input.

    As far as Micron's step by step suggestion is concerned, my problem is that I have no expertise in VB and so in order to follow your suggestions I would need the coding to be set out for me.

    In response to ItsMe's post, I am familiar with references and I had already ticked the Outlook box and have now ticked the Scripting Runtime one. You mention that you you have created some code and are willing to help me through the process - I would really appreciate that and look forward to hearing from you further.

Page 2 of 7 FirstFirst 1234567 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Send Emails from outlook
    By WhiskyLima in forum Access
    Replies: 8
    Last Post: 08-11-2014, 11:02 AM
  2. Loop Through Query, Send Multiple Emails
    By tdoolittle in forum Reports
    Replies: 6
    Last Post: 05-12-2014, 09:33 PM
  3. Programming Access to Send Emails?
    By BMW150 in forum Access
    Replies: 8
    Last Post: 09-17-2013, 06:14 PM
  4. Send Emails Automatically
    By cbrsix in forum Programming
    Replies: 10
    Last Post: 10-19-2012, 10:52 AM
  5. Replies: 1
    Last Post: 06-26-2012, 09:37 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums