Page 7 of 7 FirstFirst 1234567
Results 91 to 101 of 101

Need help with VB to send multiple emails

  1. #91
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,863
    Check if the following looks like a list that will work. There should be instances where one partner has more than one email address. I suspect this should be OK. In other words, create multiple reports and multiple emails for a partner that has more than one email address.

    By adding, [Prefix] & " " & [Full Name (Exc Prefix)] AS Partner, I suspect you will have a name to associate with an Email address. This could be included in the Body of the Email, e.g. "Dear " & rs![Partner] & ":"




    Code:
    SELECT DISTINCT [Prefix] & " " & [Full Name (Exc Prefix)] AS Partner, Missions.[E-mail address], Missions.Code
    FROM [Non-Cheque Payments] INNER JOIN Missions ON [Non-Cheque Payments].Code = Missions.Code
    WHERE ([Non-Cheque Payments].Code NOT IN ('CONEX', 'UNALL', 'PAYPA', 'FLOAT', 'WEC'))
    ORDER BY [Prefix] & " " & [Full Name (Exc Prefix)];

  2. #92
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    Yes. The code you have just posted produces the 32 Partners' details. But the [Prefix] & " " & [Full Name (Exc Prefix)] just produces the full name for the Partner rather than the name of the owner of the individual E-mail Address. So we are content for the body of the email just to refer to the Partner's name.

    How do you propose we create multiple copies of the appropriate report for those Partners which have 2 E-mail addresses. There is nothing anywhere in any of the Tables in the DB that registers this fact - it is only in the E-mail Address field where the 2 emails are actually recorded. But emailing messages to all the Partners (including those with 2 E-mail Addresses) has worked OK in other situations - the content of the E-mail Address field fed into the 'To' field in Outlook and the emails were sent without a cough! I don't know why it didn't work in this situation.

  3. #93
    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
    ...How do you propose we create multiple copies of the appropriate report for those Partners which have 2 E-mail addresses...
    How many Email addresses are there, total? 32? I suspect there are not 32 partners but 32 unique emails. The idea of the query we are working on is to create a list of email addresses. If you need 40 emails, then there should be 40 Email addresses retrieved by the query. Each email address will be associated with a Code and a Partner.

  4. #94
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    Unfortunately the DB when it was created originally did not anticipate that there would be some Partners with 2 E-mail Addresses. So the only way that we could cope with it when the situation arose was to insert both addresses into the E-mail Address field with a semicolon between them - and that has worked OK up to now.

    There are actually 32 Partners who received payments this year (out of perhaps a total of 50 or 60) and an unpredictable number of these have got 2 E-mail Addresses. So without a fairly major reworking of the DB I don't think we can distinguish them in order to create a query that would have a line for each E-mail Address as you suggest. Is there some reason why the format '1234@here.org ; 4567@here.org' won't result in the sending of a single email with the same attachment to these 2 addresses at the same time?

  5. #95
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,863
    It seems we arguing over nothing, here. I do not believe it can be disputed that you need a list of email addresses. These addresses have to be somewhere. I can certainly go into all of the nuances of how to send emails using Access. We can send one email to multiple addressees. We can place all of the addresses in To or we can place your Email address in To and place several addresses in CC or some in To, some in CC, and others in BCC. Bottom line, there has to be a list of Email addresses.

    It is highly unlikely that I would not be able to build an application that automates the process, if the process was accomplished manually, previously. For instance, a User typed Email addresses into their Email client and never bothered to store them anywhere, like an address book. It is likely I would be able to compile a list of emails. However, that does not guarantee I can explain how to do it over a help forum. This is where you come in, you need to create a list of email addresses.

  6. #96
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    I am reaching bedtime in this part of the world! But before I sign off can I make a couple of comments -
    1. I'm still baffled as to why the format '1234@here.org ; 4567@here.org' (sorry the hyperlink format has jumped in again!) has worked before, but doesn't work now!
    2. I will explore how a list of all appropriate E-mail Addresses can be extracted from the DB in order to facilitate what you have in mind.

    It will be later tomorrow before I get looking at this as I am at a Conference during the first half of tomorrow. Thanks again for your patience.

  7. #97
    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
    ...
    1. I'm still baffled as to why the format '1234@here.org ; 4567@here.org' (sorry the hyperlink format has jumped in again!) has worked before, but doesn't work now!
    ...
    I should have time to work with you tomorrow. However the email is stored in your table(s) there is a way to get it to work. If you have three emails, one email, two emails , or twenty stored in a single field, there is a way to add it to the outbound email.

    And yes, there is no special formatting needed if there are multiple emails separated by a semicolon in a single field. The code will work just fine, as is.

    The following will work
    Code:
    strEmailAddress = rs![E-mail address]
    even if
    rs![E-mail address]
    translates to
    1234@here.org; 4567@here.org
    or translates to
    1234@here.org

    IIRC, there is no space between the email address and the subsequent semicolon
    emailone; emailtwo; emailthree

    So you might want to look at that when you study the records retrieved by the query. Just make a note of any anomalies. Often times, anomalies can be fixed using code.

  8. #98
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    Just back after a long day! And I think my brain is frazzled and needs a rest! I'm going to leave finalising things until Monday.

    But I did retest the situation where there were 2 different email addresses for a Partner in the E-mail Address field. I got it to work OK - the email arrived in my 2 different inboxes. So I think that that is no longer an issue.

    I just need to make sure that I get the right references for the Partners that actually received money into the live DB (and not all the Partners in the Missions table). But I think you have given me all the guidance that I need for that. I will do the final testing on Monday and will let you know the final outcome.

  9. #99
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,863
    OK, I think you have all of the parts needed. The exception would be if you want to do further testing before going live. For instance, using some Debug techniques. Also, you will need to translate the SQL statement that can be pasted into SQL View of the query designer into a literal string for VBA.

  10. #100
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    Back & refreshed!

    I am satisfied that everything is OK now. The sending of the report to a Partner where there are two email addresses in the E-mail address field is working correctly. And Having tested with further examples where there were no payments made or where there happened to be no email address and both scenarios work as expected.

    While there should never be a case where there is no email address for a partner I have put another button on the Menu to be used before using the button to send all the emails and this will identify any Partner where there is no email address and will give the operator the opportunity to get a valid one or enter a dummy one so that the sending of all the emails is not interrupted.

    Many thanks again for your advice and patience.

  11. #101
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,863
    Glad to hear you got things working.

Page 7 of 7 FirstFirst 1234567
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