Modules & VBA :: Email Only Going To First Recipient When There Are Multiple
Dec 12, 2013
I have created a database that auto emails and works well, my problem now is that though there are multiple emails to send to it is only sending to the first one. The way I have set this up is I have created a qry to combine all emails using Allen Brownes concatrelated in below
Code:
SELECT DISTINCT Left([Copy Of tbl_Depot]![Depot],3) AS Port, ConcatRelated("[Copy Of tbl_Depot]![ContactEmail]","[Copy Of tbl_Depot]","[Copy Of tbl_Depot]![Port]=""" & Left([Copy Of tbl_Depot]![Depot],3) & """") AS Email
FROM [Copy Of tbl_Depot];
I then link this to an append qry which becomes my data source
Code:
INSERT INTO tbl_dptem ( [Empty Return Location], [Container], ContactEmail, DepotEmSent )
SELECT qry_emdepot1.[Empty Return Location], ConcatRelated("[qry_emdepot1]![Container No]+chr(13)+chr(10)","qry_emdepot1","[qry_emdepot1]![Empty Return Location]= """ & [qry_emdepot1]![Empty Return Location] & """") AS [Container], qry_Portem.Email, qry_emdepot1.DepotEmSent
FROM qry_Portem RIGHT JOIN qry_emdepot1 ON qry_Portem.Port = qry_emdepot1.[Empty Return Location]
GROUP BY qry_emdepot1.[Empty Return Location], qry_Portem.Email, qry_emdepot1.DepotEmSent;
Below is the code I use to auto send emails and when I check it shows all email addresses in sent email but it is only going to the first recipient.. not sure what I need to do so all of them get it.
Code:
Sub EmDepot()
Dim Notes As Object
Dim Maildb As Object
Dim MailDoc As Object
Dim objNotesDocument As Object
Dim objNotesField As Object
I have been able to create code to send email for my ticket system but now have a need to add an additional recipient. I would prefer this recipient to be add in the CC field. This email address will come from its own table. I have tried to figure this out myself but I can't get anything to work for me . I am fairly new to VBA and all of my experience is just through trial and error (mostly error). I have attached my original code below ....
Private Sub cmdMailTicket_Click() On Error GoTo Err_cmdMailTicket_Click Dim stWhere As String '-- Criteria for DLookup Dim varTo As Variant '-- Address for SendObject Dim stText As String '-- E-mail text
I have the following code (which everyone uses) but I have a challenge.
I ONLY want to use two (maybe three) fields from the database! (Subject & Body... with the POTENTIAL of an attached document.
I want to fill in the "to" section AFTER the email is open. This will allow me to make changes if I need to before it is sent.
Here is the code I am trying to work with. (It should look familiar) but I can't get it to keep outlook open!
Code: Private Sub SecondEmail_Click() 'open Outlook, attach zip folder or file, send e-mail Dim appOutLook As Outlook.Application Dim MailOutLook As Outlook.MailItem
I have made a query with the name "Confirmation" and it is setup like this:
Name trainee Email Training John John@mail.com Tr one Mary Mary@mail.com Tr two
I also made a button in a report with the title "Send Mail" now is my goal that if i press that button automatically multiple e-mail message's will be generated with data from people in that query. So if click on that "Send Email" button i want two different mails messages generated that will be send to John@mail.com and Mary@mail.com with in the mail body their data.
I am trying to send tasks to other people using access vba. I had used before in my database but was using early binding and now through version issues of office I am trying to recreate the code using late binding, however I am getting various problems with certain lines of code.
Primarily .datedue line I am getting run time error 438 object does not support
Below is my code:
Public Sub ASB5PostponedTask() Dim olApp As Object Dim olTask As Object Dim olDateEnd As Date Dim ToContact As Object Dim olTaskOwner As String olTaskOwner = "e.grainger"
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
I am trying to populate multiple email addresses based on a table and a yes/no check box. If the checkbox is checked, they receive an email. I have a table with Employee/email combobox with the second column being the email address, and a checkbox.
I am currently in the process of creating a form that will allow you to enter details for an email sent, select a number of documents from a list box and then open the mail message pre-written with attachments. The attachments reside in a list box currently, and I am attempting to use a "For Each" with item selected.column(3) as the file path, as column(3) contains the filepath from the table.
Currently my code is:
Private Sub OutlookBut_Click() Dim olApp As Object Dim objMail As Object Dim varItm As Variant On Error Resume Next Set olApp = GetObject(, "Outlook.Application")
[Code] ....
When this code runs, outlook will open as a process when it is closed, but then freeze and not allow me to see it or access it in anyway. If Outlook is opened Microsoft office usually gets angry at me and decides to throw error messages at me.
Body (pre filled with "Dear ([Contact]if possible) Please find enclosed your documentation for your recent telephone order bla bla bla and our logo.jpg if possible)
then i would like to attach to the email the cover letter/invoice(report), book page(report) and the book (pdf) from the hard drive (r.eports changed to pdf)
if i can press a button on the form to send it that would be good.
if i could link it to a query to send a batch of them that would excellent.
The Database is Named: b4s.mdb The table is called: Main Table The input form is called: Client The letter/invoice report is called: b4sletinv The Book page report: b4sbook The pdf book is located at c:Documents and SettingsAdministrator4sfinal.pdf The query for the batch is called: coverlet&proforma
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 form which has a button to email the data out in a standard email message.
Private Sub Command60_Click() Dim MyDb As dao.Database Dim rsEmail As dao.Recordset Dim sToName As String Dim sSubject As String Dim sMessageBody As String
[code]...
This works well enough, however, FIELDS 11 through to 16 contain the venue address. This is all we ll and good if every field of the venue address is populated. here are times when not all of the fields are populated, for instance, the address might only be 5 lines.I know I can do this using IIf statements on a report, but how can i achieve the same thing for the email.
I am currently using vb code to send an email in Access on the click of a button. I want the database user to be able to enter the recipient in a text box [ToEmail] which is on form [GroupStockProfiler]. However, I'm unsure how to put this into my code. I currently have the following which doesn't work (unless I put a specific email after 'To'):
I have an automated email system (Lotus Notes) in a Access database I have created and am looking for the code needed to add up to 3 c.c addresses into an email prior to sending automatically.
The code I have to send the email to the To: individual is all working but what code I need to add and where for the c.c addresses of which there could be 1, 2 or 3. The control for To address is EmailTo = CustCopyEmail The controls for the 3 c.c emails are cc_EmailAddress, cc_EmailAddress1 and cc_EmailAddress2.
Here's the code I have:
Private Sub SEND_CC_Click() ' Set up the objects required for Automation into Lotus Notes. Dim Maildb As Object 'The mail database. Dim Session As Object 'The Lotus Notes session. Dim MailDoc As Object 'The mail document itself. Dim UserName As String 'The current users Lotus Notes name.
I am trying to send an MS Access 'report' as an attachment to a mail recipient in rtf format without success. Everything seems to work fine except the 'send' button. Click away and nothing happens.... no error messages, no nothing. Any suggestions or tips on how to make this work?
I ran into this error message with the SendObject method. I'm getting the email address from the "username" environment variable. Our company generally uses the logon ID as the first part of everyone's email address, but there are some users that get this error: "Unknown message recipient; the message was not sent" Not only that, if two sendobjects are used one after the other, sometimes it sends to the first person and then the second one is set up to send a copy back to the sending user, it doesn't recognize the recipient that's logged on and is actually trying to send the email. That's the one that comes from the environment variable. But I've tried hard-coding the recipient and bypassing the variable altogether and it still generates the same error.
I have a query in Access and a word document that opens up the query. Currently the query contains every record in the database. If I alter the query in Access to select a certain set of records (based on dates) when Word opens the recipient list is empty. Checking for errors it says there were no records or no data records matched query options.
How can I get Word to open and use the modified query?
I think this was covered a while back and I did a search and couldn't find anything on it; Is there a way to attach and send multiple report .snp's to one email. I've been using SendObject.
Can someone help me try and figure out how I can get my code to not just pull the first email based on a dlookup from a table...
I am looking to enter information in a form and have outlook populated with to specific email addresses based on a groupid in the table. Right now this code works fine but only returns 1 email address not all with the same GroupID.
Private Sub Command27_Click() On Error GoTo Err_Command27_Click
Dim stWhere As String '-- Criteria for DLookup Dim varTo As Variant '-- Address for SendObject Dim stText As String '-- E-mail text Dim RecDate As Variant '-- Rec date for e-mail text Dim stSubject As String '-- Subject line of e-mail Dim stWho As String '-- Reference to tblUsers Dim stEvalEmp As String '-- Person who assigned ticket Dim stDelBidPrice As String '-- Delivery Bid Price Dim stOverBidPrice As String '-- Override Bid Price Dim stOverMeanPrice As String '-- Override Mean Price Dim stOverOfferPrice As String '-- Override Offer Price
'-- Combo of names to assign price change to stWho = Me.GroupID stWhere = "ClientServices_tbl.GroupID = " & "'" & stWho & "'"
'-- Looks up email address from ClientServices_tbl varTo = DLookup("[ClientEMAIL]", "ClientServices_tbl", stWhere)
Im using the code below to send information by email. BUT:
I want to send the same information to more than one recipient. can i write more than one address in the SendObject?
What can i do if i do not know prior to sending how many addresses i want to include. (this means that once i might have tosend it to 1, then later to 3, etc...)?
The code is:
Private Sub cmdMailTicket_Click() On Error GoTo Err_cmdMailTicket_Click
Dim stWhere As String '-- Criteria for DLookup Dim varTo As Variant '-- Address for SendObject Dim stText As String '-- E-mail text Dim RecDate As Variant '-- Rec date for e-mail text Dim stSubject As String '-- Subject line of e-mail Dim stTicketID As String '-- The ticket ID from form Dim stWho As String '-- Reference to tblUsers Dim stHelpDesk As String '-- Person who assigned ticket Dim strSQL As String '-- Create SQL update statement Dim errLoop As Error
'-- Combo of names to assign ticket to stWho = Me.cboAssignee stWhere = "tblUsers.strUserID = " & "'" & stWho & "'" '-- Looks up email address from TblUsers varTo = DLookup("[strEMail]", "tblUsers", stWhere)
stText = "You have been assigned a new ticket." & Chr$(13) & Chr$(13) & _ "Ticket number: " & stTicketID & Chr$(13) & _ "This ticket has been assigned to you by: " & strHelpDesk & Chr$(13) & _ "Received Date: " & RecDate & Chr$(13) & Chr$(13) & _ "This is an automated message. Please do not respond to this e-mail."
'Write the e-mail content for sending to assignee DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, -1
'Set the update statement to disable command button 'once e-mail is sent strSQL = "UPDATE tblHelpDeskTickets SET tblHelpDeskTickets.ysnTicketAssigned = -1 " & _ "Where tblHelpDeskTickets.lngTicketID = " & Me.txtTicketID & ";"
On Error GoTo Err_Execute CurrentDb.Execute strSQL, dbFailOnError On Error GoTo 0
'Requery checkbox to show checked 'after update statement has ran 'and disable send mail command button Me.chkTicketAssigned.Requery Me.chkTicketAssigned.SetFocus Me.cmdMailTicket.Enabled = False
Exit Sub
Err_Execute:
' Notify user of any errors that result from ' executing the query. If DBEngine.Errors.Count > 0 Then For Each errLoop In DBEngine.Errors MsgBox "Error number: " & errLoop.Number & vbCr & _ errLoop.Description Next errLoop End If
hello once again Scenario: I currently have a form that the users can browse through listings of items for sale. While going through the records, they can mark with a checkbox of which items to include in a report to send in an email. The check box is currently part of the listings table, and i then run a query to pull items with the box checked, then put those in to a report, then they can email.
Problem: about 5 users use this database, so if one user is selecting items that they want it's going to cause a problem for another user to select only the items he wants since it is all updating to the same table. Ex, if I select items 2 and 3 and another person selects items 4 and 5, when I click to show the report, its going to show items 2,3,4,5. Make sense?
Solution: Since there are only 5 users, I made them all aware of this, and also created a reset selected email checkbox button, that sets the checkbox back to unchecked so the next person can select their own items and email. There has to be a better way to fault proof this, I just can't think of one. Any ideas would be greatly appreciated!
I'm not sure which Form topic this post should go under ...
I have an Access Database which contains contact information including email addresses in a number of tables. Each table is a reference group of individuals interested in a particular topic and has three fields (contactID, name and Email). All tables relate back to a master address list.
This allows me to have different (or multiple) people from within the same master address as contact persons for different reference groups. When I am doing labels, or printed reports for labelling I simply combine the address from the master list with the conact from the reference group list.
I would like to be able to replicate this approach for e-mail messages.
Ideally the databse would create distribution lists and export these to outlook global address list where they would be available to all users.
Alternately, I would write a module that automatically generates a new email message with all the Email Address entries from a user selected table in the To: field. The user would then edit the email message and press send.
It seems to me that eitehr way I will need to somehow concatenate all emails addresses in a table into a list separated by semi-colons.
Have found some code that originated from this forum, and have modified it to suit my needs. There's one form with all the information, that has multiple buttons and subsequently multiple functions to generate emails, with each button/email to generate a different format and content. The first use of a button works fine, but trying to use the next button right after does nothing. If you exit the form and re-enter, you can then use the next email button, but that's a pain and the users won't like that. Can anyone help? I've pasted a sample of the code that is being used below, it's duplicated and changed slightly for the other buttons. I currently have the button linked to a macro that does other things like saving, and setting dates and then running the function.
Thanks, Don ----------------------------------------------------------------- Public Function AckEmailNew() On Error GoTo Err_cmdMailTicket_Click
Dim varTo As String '-- Address for SendObject Dim stText As String '-- E-mail text Dim stSubject As String '-- Subject line of e-mail Dim stTicketID As String '-- The ticket ID from form Dim strSQL As String '-- Create SQL update statement Dim errLoop As Error
varTo = Me.ClientEmail stTicketID = Me.STSITicket
stSubject = "Ticket/numéro de référence: " & stTicketID
stText = <<My text goes here>>
'Write the e-mail content for sending to assignee DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, -1
Hello to all the Users! I am new at Access and new to programming. I have created an Access form that has four labeled control buttons each one with an individuals name. I have the corresponding code:
Private Sub Email_PersonOne_Click() DoCmd.SendObject acSendNoObject, , , "personone@yahoo.com", , , "Work Request", "A new work request has been submitted." End Sub
When I have one button and one code the email function works. I want the users to be able to select one out of four people who they want to notify upon completion of the form. So I have four buttons and changed the email address and the names on the buttons but now none of the buttons work. The code is on the same page separated with just a line between each section.
Does this make sense? Any input would be greatly appreciated. Thanks in advance for your time and help. Sincerely, DebraF
