none
Sending email automatically from access 2013

    Question

  • I am not an experienced programmer but I need to automate an email sending action from access, is it possible to achieve it without going into VBA?

    How can I do it?

    Tuesday, April 29, 2014 9:59 PM

Answers

  • I suppose you could do it with just one query but I would use two: one for all the persons who need to receive the emails and the other for all the equipment assigned to each person. Then, the code might work something like this:

    1. Loop through the records of email addresses

    2. For each email address, open the query of equipment for this particular person

    3. Loop through the records in the equipment query to construct the email

    4. Send out the email and repeat Step 1 until all the records in the first query have been processed

    For an example of what the actual code might look like, check out UtterAccess.

    • Marked as answer by agustinizq Sunday, May 4, 2014 3:29 AM
    Wednesday, April 30, 2014 3:11 AM
  • Thanks, I hope it Works, one more question, the posts on UtterAccess are ald, are they still valid for Access 2013?
    • Marked as answer by agustinizq Sunday, May 4, 2014 3:29 AM
    Wednesday, April 30, 2014 4:41 AM
  • Thanks, I hope it Works, one more question, the posts on UtterAccess are ald, are they still valid for Access 2013?

    Yes, they should be. If you run into any problems, just let us know. Good luck!
    • Marked as answer by agustinizq Sunday, May 4, 2014 3:28 AM
    Wednesday, April 30, 2014 2:23 PM
  • I think this will help.

    https://www.youtube.com/watch?v=8jarq3EgvEA

    Towards the end the guy shows his code with some IF statements, which I think you can easily adapt to your situation.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Marked as answer by agustinizq Sunday, May 4, 2014 3:28 AM
    Saturday, May 3, 2014 2:35 PM
  • Thank you It showed my the way, I had an issue on warnings that I solved basically asking the users and all of them have outlook so it became very easy.

    The query as suggested by the DB guy was a two nested loops way

    • Marked as answer by agustinizq Sunday, May 4, 2014 3:28 AM
    Sunday, May 4, 2014 3:28 AM
  • Than you all guys, Finally I got it ok!, let me summarize the challenges:

    1.- It is a web app (Web database) placed in sharepoint online (Office 365) so no VBA allowed and very limited macro actions.

    2.- I took advantage of the "Report database" which is a synched file with the 365 application, I managed to re create the queries needed to start with.

    3.- I could not run my VBA code using a query as a source as I had different tables and did not know how to join them so I created an action macro that converts the data into a table.

    4.- Once on the table I used the Outlook library to create and send the email once I knew that all the users have MS outlook as their email application

    5.- I created different forms to perform either the macro I needed (To create the table) or the VBA to create the email items and send them.

    6.- All the forms were hidden and one of them used as the form opened by access when started (could not use the autoexec macro because I obtained some warnings when creating the table I mentioned previously)

    So it ended as an icon on the user desk that performs all the actions and closes access returning to the desktop with a single message box alerting the user that the process was succesful

    Thank you all, glad to be a part of this community.

    Agustin

    • Marked as answer by agustinizq Sunday, May 4, 2014 3:39 AM
    Sunday, May 4, 2014 3:39 AM

All replies

  • Is macro okay? If so, you can try using the EmailDatabaseObject action.
    Tuesday, April 29, 2014 10:04 PM
  • Macro would be my preferred way to do it, but I am pretty new to this and I have not been able to call this actions using parameters so I can créate a "massive mailing" scenario. The situation is:

    I have a list of equipments due to calibration, it could be any number of equipmnets per contact so the query I have states the contact and the equipments that are due, the email I am looking to send would something like this:

    To: [Usermailaddr]

    Subject: Equipment due for calibration

    Dear ;Name:

    The following equipment is due for calibration next month

    [ID]    [Description] [Serial number] [Due date]        (* this repeats for as many equipments are due*)

    Let us know when should we collect the equipment......

    I hope this explains better my situation

    Tuesday, April 29, 2014 10:50 PM
  • Hi,

    What you want might probably be possible entirely using just macros but I think VBA would do a better job at it. One important thing to remember when sending out emails using Access is that you could run into security issues. For example, Outlook usually blocks other programs from sending out an email without some confirmation from the user or a setting that identifies that program as a "trusted" application. It's their way of preventing virus programs from sending out spam using some unsuspecting user's account.

    Okay, to use VBA for your request, you could create a query that has all the data you want to email and then loop through the records using a Recordset.

    How many emails are you trying to send out?

    Wednesday, April 30, 2014 1:21 AM
  • Hi!,

    The query is already done, it is the mailing part my issue, as of today worst case scenario is a 100 emails once a month, will grow but at a very small pace.the query is in a way that on each row i have the email of the person I try to send the email and then the data to send which mainly is the name of a device and the due date, it varies with each person the amount of lines to include in the body of the mail.

    thanks for your help

    Wednesday, April 30, 2014 2:21 AM
  • I suppose you could do it with just one query but I would use two: one for all the persons who need to receive the emails and the other for all the equipment assigned to each person. Then, the code might work something like this:

    1. Loop through the records of email addresses

    2. For each email address, open the query of equipment for this particular person

    3. Loop through the records in the equipment query to construct the email

    4. Send out the email and repeat Step 1 until all the records in the first query have been processed

    For an example of what the actual code might look like, check out UtterAccess.

    • Marked as answer by agustinizq Sunday, May 4, 2014 3:29 AM
    Wednesday, April 30, 2014 3:11 AM
  • Thanks, I hope it Works, one more question, the posts on UtterAccess are ald, are they still valid for Access 2013?
    • Marked as answer by agustinizq Sunday, May 4, 2014 3:29 AM
    Wednesday, April 30, 2014 4:41 AM
  • Thanks, I hope it Works, one more question, the posts on UtterAccess are ald, are they still valid for Access 2013?

    Yes, they should be. If you run into any problems, just let us know. Good luck!
    • Marked as answer by agustinizq Sunday, May 4, 2014 3:28 AM
    Wednesday, April 30, 2014 2:23 PM
  • I think this will help.

    https://www.youtube.com/watch?v=8jarq3EgvEA

    Towards the end the guy shows his code with some IF statements, which I think you can easily adapt to your situation.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Marked as answer by agustinizq Sunday, May 4, 2014 3:28 AM
    Saturday, May 3, 2014 2:35 PM
  • Thank you It showed my the way, I had an issue on warnings that I solved basically asking the users and all of them have outlook so it became very easy.

    The query as suggested by the DB guy was a two nested loops way

    • Marked as answer by agustinizq Sunday, May 4, 2014 3:28 AM
    Sunday, May 4, 2014 3:28 AM
  • Than you all guys, Finally I got it ok!, let me summarize the challenges:

    1.- It is a web app (Web database) placed in sharepoint online (Office 365) so no VBA allowed and very limited macro actions.

    2.- I took advantage of the "Report database" which is a synched file with the 365 application, I managed to re create the queries needed to start with.

    3.- I could not run my VBA code using a query as a source as I had different tables and did not know how to join them so I created an action macro that converts the data into a table.

    4.- Once on the table I used the Outlook library to create and send the email once I knew that all the users have MS outlook as their email application

    5.- I created different forms to perform either the macro I needed (To create the table) or the VBA to create the email items and send them.

    6.- All the forms were hidden and one of them used as the form opened by access when started (could not use the autoexec macro because I obtained some warnings when creating the table I mentioned previously)

    So it ended as an icon on the user desk that performs all the actions and closes access returning to the desktop with a single message box alerting the user that the process was succesful

    Thank you all, glad to be a part of this community.

    Agustin

    • Marked as answer by agustinizq Sunday, May 4, 2014 3:39 AM
    Sunday, May 4, 2014 3:39 AM
  • Hi Agustin,

    You're welcome. Glad to hear you got it sorted out. We were all happy to assist. 

    Good luck with your project.

    Sunday, May 4, 2014 2:33 PM