Modules & VBA :: Send Emails To Defined Array Of Recipients Using CDO
Sep 18, 2013
I am trying to send emails using CDO to an array of recipients loaded from a query. I have made this work using Lotus Notes so i know the first part of my code is correct, and the current code I have works when I input an actual email address. But when I try to add the Array name into the 'To' field I get a data mismatch error.
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've got a form which emails out a report when a new record is added to my database but I'm stuck when it comes to making it send it to more than one recipient.
I came across this on the web... which suggests I should be using an array but I don't know anything about arrays.
For multiple email addresses you just set MailDoc.sendto to an array of variants each of which will receive the message. So
Code:
Dim recip(25) as variant recip(0) = "emailaddress1" recip(1) = "emailaddress2" e.t.c
maildoc.sendto = recip
My problem is I'm not sure how to implement it? My 'recipients' are being pulled from a query... how do I get that into the array of variants?
I currently have a code set up which sends an attachment from a drive on my computer to all the email addresses in a certain query. I would like to change the code so that I can add a button that will allow me to send an email with a specific attachment depending on their individual code in a table, e.g each person will receive a specific attachment. At the moment, the attachments are saved with the same file name as the person's specific code.The code I am currently using is:
Private Sub Command9_Click() Dim MyDb As DAO.Database Dim rsEmail As DAO.Recordset Dim qdf As DAO.QueryDef Dim prm As DAO.Parameter Dim strEmail As String Dim strMsg As String Dim oLook As Object
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 created a database that generates a report with customer debts. I have a lot of customers and in a report each page is a different customer with individual debts info. What i would like to do is to create a button and distribute individual report page as pdf to individual customer by email. I'm using Lotus notes. I have searched the forums i found something but it was not what i was looking for.
In a form I have a button which creates (in preview) an outlook message with a pdf report. It opens and displays just as I want it to, but if I chose to save that message in outlook and either send or cancel (i.e. allowing user to save to drafts and send later) then MS Access crashes.To ensure this wasn't to do with any compile / corruption within my full application I mirrored the process in a very simple 1 table / 1 form / 1 report db and still the same - access crashes after saving the outlook message and either sending the message or cancelling out the message.
Form code is as follows;Private Sub Command0_Click() On Error Resume Next DoCmd.SendObject acSendReport, "Table1", acFormatPDF, "someemaddress", , , , "REQUEST FOR INFORMATION" DoEvents On Error GoTo 0 End Sub
In my main application this process is embedded within a loop to send various filters of the report to different recipients, which works fine if 'save' is not used within Outlook..
I am creating a recruitment db for 6 members of staff, I have created a form (add new vacancy). Within the form is a various drop down boxes where you can select each employee and drop down box to pick which company want a vacancy filling. What I wanted to do is when a new vacancy is put on the system, and the administration team select save record is it possible to email the employee to let them know a new vacancy has been added, and also email another administration department to send the employee a suitable candidate list with the information provided on the form. Or am I asking far to much from access. The two fields that are most important is who the vacancy belongs and which company want the vacancy. The db is available if you require a look to have a better insight. Thanks for all your help.
I am trying to send out request-for-quote sheets to vendors and i need to send the image of the product as well. when i send emails for data collection, the attachment is not included. how can i attach images?
I copied some VBA from one database to another. I didn't change anything and I am able to run it fine in the first database. But in the DB I pasted it to, I am received a Compile Error message with the XlApp As Excel.Application area highlighted.
Function OpenAutoCount() Dim xlApp As Excel.Application Dim xlWB As Excel.Workbook
I would like to send an email from Gmail server automatically at the end of every month. I'm currently sending email on button click. But is it possible to do this without user intervention.
I have a contact form on our website which sends an email to the business dev team saying that the user has requested to be sent product sheets. The email contains the users name, email, phone number, and a few other fields. Is there a way I can send the details of these emails to my access database automatically?
I have a query that may have 6 - 7 different records for example each record contains a different email address, i need to get the first email then send and email to that address then go to the next record get that email address send an email to that email address until its gone through all the records...
I use this code to send an email, dont know how to loop through records and get the email...
Code: Dim appOutLook As Outlook.Application Dim MailOutLook As Outlook.MailItem Dim strPath As String Dim strFileName As String Set appOutLook = CreateObject("Outlook.Application")
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.
How do I add multiple recipients to the '.To' line in the code below?
Dim appOutLook As Outlook.Application Dim MailOutLook As Outlook.MailItem Set appOutLook = CreateObject("Outlook.Application") Set MailOutLook = appOutLook.CreateItem(olMailItem) With MailOutLook .BodyFormat = olFormatRichText If Me.txtDepartment = "IT" Then .To = "my email" End If .Subject = "hi" .HTMLBody = "hi" .send End With
This is giving me the error 'User defined type not defined'. I know I have to set something in a list somewhere but have forgotten how to do that. Where to go, and what to set?
On Compiling my assecc database VB code I get the following error message "User defined type not defined". I understand it is beecause I have not declared the Variable Type, but have no idea to exactly which part of the code the error is referring to.
How do I find out WHICH User defined type is not defined, especially when I have not got any (or do not want to use any) user defined types?
Thank you in advanced programming wizards. Kind regards, Adam.
Hello, I have the following code and i don`t know what's wrong Private Sub cmdCautare_Click() Dim strSQL As String, strOrder As String, strWhere As String 'Dim dbNm As Database 'Dim qryDef As QueryDef 'Set dbNm = CurrentDb() strSQL = "SELECT DOSARE.DosarID,DOSARE.DenumireDosar,DOSARE.CodDosa r,DOSARE.DataDosar,DOSARE.Denumire,DOSARE.Data,DOS ARE.Stadiu FROM DOSARE" strWhere = "WHERE" strOrder = "ORDER BY DOSARE.DosarID " If Not IsNull(Me.txtDenumire) Then strWhere = strWhere & "(DOSARE.DenumireDosar) Like '*" & Me.txtDenumire & "*' AND" ' " End If If Not IsNull(Me.cmbStadiu) Then strWhere = strWhere & " (DOSARE.Stadiu) Like '*" & Me.cmbStadiu & "*'" End If DoCmd.Close acForm, "frmPrincipal" DoCmd.OpenForm "frmRezultateCautare", acNormal Forms!frmRezultateCautare.RowSource = strSQL & " " & strWhere & "" & strOrder End Sub Here: Forms!frmRezultateCautare.RowSource = strSQL & " " & strWhere & "" & strOrder The error is the following "Application-defined or object-defined error"
There are so many threads on here for emailing a PDF version of a report. I've put together a nearly complete bit of VBA that will do what I want - except for one thing.
Basically I have a personnel table with an email field. I Also have a field that details if a person is available or not ("Home" means they are available)
I need to create a string that is comprised of all the email addresses of the personnel who are "Home". In another thread I read that this can be done using a SQL statement.
So this is the VBA that I have so far - I commented the SQL for now because I don't quite know how to use it here.
Also, I have created the string, called "emailsList"
Code: Private Sub Email_Button_Click() 'SELECT Personnel_Table.Email FROM Personnel_Table WHERE (((Personnel_Table.Status)="Home")); Dim emailsList As String DoCmd.SendObject acReport, "AWACT_Report", acFormatPDF, emailsList, , , "Training Update", "Attached is the newest Training Report.", True End Sub
I have some code that successfully gathers some items and adds these items to a listbox, one item at a time using .AddItem.And it works ok.The problem is, when there are a bunch of items to add, everytime the .AddItem runs, the form redraws.there are a bunch and this leads to a lot of flicking while the form redraws however many times .AddItem adds an item.
I've had the idea of building an array and then assigning the array to the listbox.I can build the arrray no problem but i have not been able to assign the array to the listbox all at once.how to do is add items to the listbox from the array one specific item ListArray(i) at a time.
Obviously this would work, but gains me nothing in terms of the visual effect in the form becuase it is still adding to the listbox one item at a time.Is there some way to assign an array to a list box all at once, without doing it one item at a time?
How do I delete values in an array? I want to reuse that array but first I need to delete its values so I would start with Arr(0) rather than where I stopped off with the last loop at Arr(10) [assuming the last calculation inserted until Arr(9)]
Here is my code:
Set rst = CurrentDb.OpenRecordset( _ "Select * from dbo_ProductStructure where ChildProductNbr Like '*" & txtPartNumber & "*'") 'search associated fields with user input While rst.EOF = False ReDim Preserve Arr(i) Arr(i) = rst.Fields("ParentProductNbr") i = i + 1 rst.MoveNext