Modules & VBA :: Sending Emails To Multiple People Based On Same Query And Report
Jul 15, 2013
At present I have a tblData, a tblPeople, a long union query and a report that displays its results. On a form I select a person from tblPeople (another field on that table contains their email address), run the query and report in that person's respect and email the results to them.
What I want to do is add a button which runs some VBA code which cycles through tblPeople, runs the query for each person, works out if the query has returned any records from tblData for that person, and if so - sends them the report.
Using Access 2010; I have code that generates multiple emails to send using outlook. It works fine except that all emails go to my draft folder and I have to go the outlook and send them individually. This is fine when I have under 10 emails; but sometime it can get up to 50 or even 100 emails. What I have to do to send them from my draft all at once. The emails go to draft to inspect for errors.
I have the following code in a module that sends out various email updates to me when I'm off site. On my PC it works perfectly whether I have Outlook open or not. I've installed the database on several other machines, but I'm getting a Run Rime Error 287 on the .Send line if the user has Outlook closed. Any setting somewhere in Outlook or Access that needs changing? Or is there something that can be changed in the code to ensure the email sends whether Outlook is open or not?
Code: Public Sub SendEmailNotification(strSubject as String, strMessage as String, strAddress as String) Dim olEmailApp As Object Dim olMsg As Object Dim strEmailBody As String Set olEmailApp = CreateObject("Outlook.Application") Set olMsg = olEmailApp.CreateItem(0)
In an Access 2007 module, is there a way of sending an email to a list of people stored as a list in a table in the database, rather than having to put all the names into the function?
I have a function I'm using to generate and send out an email to certain people, but the list is constantly growing so I'm looking for a better way to manage it! The main issue is having to kick users out of the database every time I need to update the recipients list... because it's stored in the code.
If I create a simple table containing all the names, how could I then ask it to use that instead?
This is the function I'm using currently - found on here and adapted to my own purposes
Syntax to use for the function: SendNotesMail "recipients", "Body Message", "Additional Text", "Subject"
Code: Public Function SendNotesMail(strSendTo As String, strBody As String, strExtraText As String, strSubject As String) 'This public sub will send a mail and attachment if neccessary to the recipient including the body text and additional comments from the Active record DoCmd.OutputTo acOutputReport, "REP09emailnotification", acFormatRTF, "x: endersgroup tendering databaseTenderUpdate.rtf", False Dim Subject As String Dim Attachment As String
[Code] ....
So I guess my question is: how do I get my recipients from a table into the strsendto?
I am trying to send a report to a list of employees. I have a query that generates the report into the layout I want and includes the e-mail address of who the recipient should be. The report looks something like this:
I need to send, for each row in the query, this report based on the results of that particular row. How would I go about making a e-mail template that would be used, or at least sending the report as a PDF attachment? I've seen the sendObject method.
I need to enter workload counts for 10 people, and it is done on a monthly basis. So I have a table of Months (Jan-Dec), a table of names, and a joined table with the months, names and a field for the workload counts.
I would like to make a form where I could select the month and all the names show up so I could go and enter the counts for everyone at the same time. I've attached a diagram to show what it would look like
I have created a database system to book appointments between tutors and students in a school.
In the form, I want tutors to be able to send appointment details to students via email. I have created tables consisting of these details, however I don't know how to set it up for emails to be sent.
I am finding this very difficult especially with the visual basic codes. :confused:
I am using a form to send an email but part way in the process a message box pops up with 2 buttons labeled: Allow and Deny with some other information. I am forced to select allow in order for the send process to be completed.
Is there a way to prevent this popup so that the process completes smoothly without interruptions?
Recently created a database with a form that the user provides problematic information. The form has a close button that has a macro attached that initated an emails and send the form information in a report to the end user . My problem is the function works great when the sender has a Outlook account. When the sender has a gmail account - the command will not work. I read that VBA can be written but I am not able to write SQL.My level with access is using built in functions and commands.
ID Date Person Title Yes/No Action 001 20/4 Mr A Consultant 001 20/4 Mr B Supervisor 001 20/4 Mr C Consultant 004 20/4 Mr D Consultant 010 20/4 Mr E Consultant 010 20/4 Mr F Supervisor
What I'm looking for is coding that would start with the first ID number in CONTACTS and see if there is the corresponding ID in DATA. If there is, then I want it to create an Outlook email and have:
1.To field populated with the email address in the CONTACTS table
2.From and CC fields to be my email address
3.The subject line needs to read [ID] "Request" and then today's date backward (e.g. 20150420).
4.The body I need to put some generic wording and then a table with the records from the DATA table (first 5 fields plus the headings).
5. The format of the table etc isn't important as long as it is a table format
If it's created the email I want it to go back to the DATA table and put "Email Created" in the Action column for those IDs.
I don't want the email to automatically send; I only want it to create a draft (as this data needs to be manually confirmed before sending).
If for example it gets to checking in DATA table for ID 002 (and as seen there's none there), I want it to skip creating the email and move on to the next ID.
In the end I would expect to see 3 draft emails open in Outlook ready to be sent, looking something like the below (just 001 below)?
From... my email To... Email1 CC... my email Subject: 001 Request 20150420
action the below:
ID Date Person Title Yes/No 001 20/4 Mr A Consultant 001 20/4 Mr B Supervisor 001 20/4 Mr C Consultant
And the DATA table look like this (if only 001 email was created):
ID Date Person Title Yes/No Action 001 20/4 Mr A Consultant Email created 001 20/4 Mr B Supervisor Email created 001 20/4 Mr C Consultant Email created 004 20/4 Mr D Consultant 010 20/4 Mr E Consultant 010 20/4 Mr F Supervisor
I will need to add more data to the CONTACTS table over time, also the DATA table will be different each day so I need the coding to accomodate for both of these possibilities.
I'm using CDOSYS and SMTP in a vb function to send emails via Ms Access 2003.
My code loops through a recordset an sends an email to the email address in the table.
However, all works fine until an invalid email address is encountered, at which point I get an 'Invalid address' error message and the code halts (obviously). BUT, if use 'On Error Resume Next' I get no error message but all consequtive emails are NOT sent.
How can I get the code to ignore the invalid email and carry on sending emails throughout the recordset?
I have some code that takes from a query, an email address and sends out an email.Due the security warning that happens for each individual email, i want to create ONE email for all email address in the query and populate them to the BCC section of the sendobject.Here is the code that I currently use
Code: Private Sub cmdSendEmail_Click() Dim MyDB As DAO.Database Dim rsEmail As DAO.Recordset Dim sToName As String Dim sSubject As String Dim sMessageBody As String
[code]...
I want to populate the BCC field with multiple emails addresses seperated by ":" and send just one email.
I have a function which generates a report as a RTF file, then sends it out to a list of users via Lotus Notes. The list of users is generated within the function using a query. The code worked fine while I was testing with just me in the recipients... but as soon as I add more people to the list, I get Runtime error 7294: Unable to send mail, no match found in name and address book(s) If I copy and paste the generated list into a new mail message in Lotus Notes, it accepts them all....
I THINK this is because when it's sending from Access, it's trying to use my personal address book, rather than the one that's held on the mail server... Does that sound plausible / feasible? If it does, how to change this?
I tried changing the recipients list to have the 'actual' email addresses in it rather than their names - when I did that I got no errors... but it didn't send the email, either!
I've setup a selection form that returns a specific list of email addresses in a query.
What I can't work out is how to press a button and have the email addresses populate the To: field in a blank Outlook email - I don't need any bells or whistles.
in Access i have made a report with data and now my goal is that if i click on a button the data will be exported to a existing Excel file under the other existing data so in row A6. Is this possible true a VBA code?
My report produces multiple copies of the same record. I know why, but don't know how to fix it.
EmployeeTable.
With a one to many relationship with TrainingTable (via employee PK as FK in trainingtable).
Training table has a one to many relationship with a table called Range.
Report is based on a query that picks up the Employee/Training/Range (range just describes the training unit).
However, If I have more than one range expressed organized a training unit, the report spits out several copies of the Employee record to display all the ranges.
I have got a form (name: SearchForm) that displays results of a query (name: AircraftSearch). It is a continuous form displaying multiple results of a search done by the query. I need to be able to send an e-mail to multiple recipients chosen from results displayed on the form.
One of the form's field (a text box called: EmailToOperator) is containing e-mail address to an aircraft operator selected by the underlying query. I need to be able to place a check box button (or something similar) that is going to select the e-mail address. The tricky part is to have multiple check boxes allocated for each record displayed on the form and have them working independently.
The second task is sending a one message (via Microsoft Outlook 2010) to chosen multiple recipients (with no attachments) having the recipients' addresses not visible to each other.
I have built a form which I can add multiple people to one project but when I look in the table only the person who the relationship was linked to gets the subtable.
Then I researched a Many-Many relationship and this solved that issue, now I can put multiple people on a project and multiple projects on people. PersonProjectTBL PersonProjectID *Autonumber* PersonID ProjectID
However how do I now make a form where I select a project and associate more then one person with this project? I think I need to somehow increment the PersonProjectTBL and add to it as I go.
The layout I would like to have is:
Combobox 1: Selects the Project (After Select use a (queary ??)) to load in Combobox 2 and 3 with people. Combobox 2: Select a person to load into project. Combobox 3: Select a second person to load into project.
I dont expect a full answer a link or multiple links is very appriciated, even a search topic of what to look for.
I have a database which has a contact table and a flight table joined in a one to many relationship. Each contact may more than 1 flight and I want to be able to report on people that have exactly the same flight pattern. For example if 2 contacts have flight records FlightID 1, 4, & 10 I want them to be grouped together.
I have tried a number of things including a cross tab query to end up with the contactID and then a string of their flight IDs but as there are over 200 possible flights, the string appears as 1,,,,,,,,3,,,,,,,,,,,,,5,,,,,,,,,,,,6,,,,, etc and when I try to group on this it doesn't seem to work. This seems a very messy way of doing it and I'm sure there must be a much easier way!
In the following code, I debug a problem with the CurrentDb.OpenRecordset
Code: ' --- ' ENVOYER UN MAIL DEPUIS ACCESS ' --- ' Entree : strEmail <- Adresse e-mail du destinataire ' strObj <- Objet du courrier ' strMsg <- Corps du message
