help with docmd.sendobject and multiple windows

I'm hoping there is a way to solve my problem with out backtracking to another method (did I hit a wall?).

I've gone down the road of using docmd.sendobject for sending out reports from an access database because I don't have to save the attachment to media that way, I can just create and send. My problem is I want the user to be able to generate a batch of emails, edit them and the send them. The docmd.sendobject is only letting me bring up the emails for editing one at a time. The user either has to send the current email or save it in order to bring up the next email to be sent. I also noticed outlook and access are not operable until the user deals with the email pop up window. This is super annoying, how can i have docmd bring up an email to edit in outlook like "normal" where its just another window on the users screen and so that multiple emails windows can be generated simultaneously? I'm having trouble googling this.

	Dim r As DAO.Recordset
Set r = CurrentDb.OpenRecordset("SELECT invoiceno, scontact from invoice where please_email = True")
If Not (r.EOF And r.BOF) Then
    Do Until r.EOF = True
        DoCmd.SendObject acSendReport, "Invoice_Email", acFormatPDF, r!SContact, , , "BAM!", "double bam", -1
End If

Post your answer or comment

comments powered by Disqus
How to add second criteria to OpenForm function?
Basically, I need help with double click VBA code event in a ListBox.
I two related forms, in the primary form contain a listbox, when user double click on a specific record, the second form will open and should filter record based on 2 criteria (LoanInfoID and RequestDate).

Private Sub RequestList_DblClick(Cancel As Integer)
DoCmd.OpenForm "F_LoanDocuments", , , "LoanInfoID=" & LoanInfoID AND SECOND CRITERIA IN LIST BOX(RequestDate)
DoCmd.GoToRecord , , acNewRec
End Sub

ListBox Row Source:
SELECT T_Request.LoanInfoID, T_Request.RequestDate, T_TransactionType.TransactionType
FROM T_Request INNER JOIN T_TransactionType ON T_Request.TransactionTypeID = T_TransactionType.TransactionTypeID
GROUP BY T_Request.LoanInfoID, T_Request.RequestDate, T_TransactionType.TransactionType
ORDER BY T_Request.RequestDate;

Thank you in advance

I need help with DoCmd.SendObject to Email, I cannot get a carrige return in the body of the form, HELP please!

DoCmd.SendObject acSendForm, "Current_ICS", acFormatXLS, MyValue, , , "Shipping form for ICS# [ " & ICS_NO & " ] ", "1 " & "2 " & "3 " & "4 ", -1

The Email form looks like this:
| TO:
| Subject: Shipping form for ICS# [ 607883 ]
| 1 2 3 4
| attachment Current_ICS.xls

What I want is this:
| TO:
| Subject: Shipping form for ICS# [ 607883 ]
| 1
| 2
| 3
| 4
| attachment Current_ICS.xls

Hi Guys. First time poster. Long time access user.

I recently made an addition to a database i designed.
It is a form, that uses the DoCmd.SendObject command, to send an email with the forms contents.

	DoCmd.SendObject _
    acSendForm, _
    "hireRequest", _
    acFormatTXT, _
    "", _
    , _
    , _
    "Request Form", _
    "[Message]", _

Now, i designed it in access 2003, and tested in 2003 and it works flawlessly. To save buying licenses for multiple computers, we use the 2007 runtime.
However, when it tries to send it... runtime error, the program must now close.

Are there any work arounds?

I appreciate any help.

Hi everyone,

I need a little help with a Access 2007 DB I am working on. The first issue is:

I am trying to prevent any printing capabilities in my DB(re: ctrl+P, print screen, etc...).

Second issue:

Since Access 2007 has no Group or User level security, I created a table with user level security(admin,developer,user) and I have assigned them to users. What I am trying to do is when a user opens the DB, have the DB automatically determine the user who is logged into the network(getuser) and have the DB determine there access to forms based on the security levels. The users are using Windows XP Pro. I hope I explained this right.

I have been doing alot of research on this trying to find a way of doing this, but have not quite figured it out yet.

Any help with this would be greatly appreciated.


Hi Guys, I'm having some trouble piecing together some code. Below is my purpose:

We send monthly reports to each sales rep. These reports need to be in an excel spreadsheet, and the data specific to each sales rep. I want to be able to send this automatically out of access to each sales rep.

I'm running into two roadblocks with docmd.sendobject.
1. It doesn't allow for a way to filter the query before sending (to show only each sales rep)

2. It doesn't allow for an sql query.

the SQL query looks promising, but I can't seem to find a way to send it without saving it as a file first (can do if necessary, but It has no value)

Please see code below:

	Private Sub CmdSendExcelTable_Click()

On Error GoTo Error_Handler

   Dim db As DAO.Database
   Dim rs As DAO.Recordset  'to hold the Sales Rep ID's
   Dim strSQL As String     'holds new query definition
   Dim strContactID As String 'same as above
   Dim strTo As String
   Dim strCC As String
   Dim strBody As String
   Dim strSubject As String
   Dim qdf As DAO.QueryDef

'set email string information
    strCC = "EMAIL"
    strBody = "Please Look into the attached Quotes. Clarify their status on the Excel document and send back.  Thanks!"
    strSubject = "Aging Quotes"
   Set db = CurrentDb
   Set rs = db.OpenRecordset("qrySalesReps")
   'set query def
   Set qdf = db.QueryDefs("qrySalesDataReport")
        With rs
            'Perform Function
            Do Until .EOF
                'set Value to use in Query
                strContactID = .Fields("SalesRepID").Value
                strSQL = qdf.SQL
                Debug.Print strSQL
                strSQL = Replace(strSQL, "ReplaceSalesRep", strContactID)
                Debug.Print strSQL
                'set parameter in query
                'qdf.Parameters("SalesRepContactID").Value = strContactID
                'set Send To
                strTo = DLookup("Email", "tblSalesRepInfo", "SalesRepID = '" & strContactID & "'")
                'send stuff
                On Error Resume Next
                DoCmd.SendObject acSendQuery, , acFormatXLS, strTo, strCC, , strSubject, strBody
        End With

    Set rs = Nothing
    Set db = Nothing
    On Error Resume Next
    Exit Sub

    DisplayErr Err.Number, Err.Description, "frmRunReport", "GetSalesRepID()"
    Resume Exit_Procedure
End Sub

Thanks for any insight!

hi, i have a huge prob, wat needs doin some how im not fussy in how it works as long as it does the job.... i have a txt box called whotonotify, that is invisible for front end users, to make it user friendly i want tick boxes for the users - so there can b multiple selection, etc, how would i go about doing this? on the tick of a user e.g. "peter" it automaticaly places that name in whotonotify txt box. any help or sugetions on other ways wud be appreciated thanx i found out this is a pain to do so......

i have thought of another
method but im not v gud at makin my own code at all, wat
i have done is.... kept my who to notify txt box, and
created a combo box with all th users, cos multiple
selection is a pain to display in a txt field i would
like some help with makin some code for an add button i
have created- wat this button needs to do is..... on
click add the name thats in the user nema combo box, it
needs to be able to have more than one user added, so
the code needs to automaticaly but a semy colon or
something, plz help

I wrote a query that select all like items and add the quantity of like items from a user input. Now I want to use a from with a combo box to select the user input. How do I get the info from the combo box to the query. The form has a command button using the DoCmd.OpenQuery and a combo box.

Hi Guys

I am pretty new to programming with access but i am trying to create a database system with history logs and i am stuck on how to structure it.

I'll explain a little more, i am trying to setup an application so that if somebody brings an item into myself to purchase i can book it into the system so that it records the customer that it came in with and also the item details. I will then need to resell this product onto another customer, the problem i am having is that once the item has been purchased and sold on i will then need to keep a record of this so that if the customer comes back with the same item he has purchased to resell back to us we can keep all the details in history so that each product that is purchased and re-sold has all its past history from every customer that has purchased it and sold it back to me.

Any help would be much appreciated.


hi i am trying to copy emails from a ms access document for work. (there are about 6000 of them) when i go into the data base...under the emails column i see ALL this

its just lines of that!
if i cant copy all the emails this way, i will have to do 1 at a time with a program and it will take me literally 10 hours

please help
i dont wanna get fired lol

how can i copy the emails. In the program, the emails are all normal (you but in the access document, its like that


hi i am trying to copy emails from a ms access document for work. (there are about 6000 of them) when i go into the data base...under the emails column i see ALL this

its just lines of that!
if i cant copy all the emails this way, i will have to do 1 at a time with a program and it will take me literally 10 hours

please help
i dont wanna get fired lol

how can i copy the emails. In the program, the emails are all normal (you but in the access document, its like that


Simple query for the right person!!!

Hi Guys,

Im in need of some help with a query that i am trying to set up. I have a table with data shown roughly below. And need to extract the average [call length] for each heading of [Call type] on a given date.

Ie. The query will be run and the date can be inputted. It would the display each category only once and the average of that category.

Call type |Call Length (Shown in Minutes)|Submitdate
Split Billing | 4 |27/10/07
Split Billing | 8 |27/10/07
Split Billing | 9 |27/10/07
Split Billing | 1 |27/10/07
Split Billing | 4 |27/10/07
Tariff changes | 2 |27/10/07
Tariff changes | 3 |27/10/07
Tariff changes | 8 |27/10/07
Tariff changes | 5 |27/10/07
Tariff changes | 5 |27/10/07
Billing Cycle | 1 |26/10/07
Billing Cycle | 2 |26/10/07
Billing Cycle | 3 |28/10/07
Billing Cycle | 20 |26/10/07
Billing Cycle | 15 |27/10/07

Hi! I'm trying to create a query using option groups, i have two options the first one is STOCK and the other is PD.
If i select stock the the query i want to create to introduce it on a form is:
I have 5 fields
If i select stock
then the query is
If (reserva +almacen)


As I'm new to this forum I will briefly describe my experience, I have extensive Excel and Excel VBA knowledge and have been working with ORACLE products for the past 3 years.

I need some help with a combo box (It maybe a design fault from my part) but here goes

I got two tables

store_name (text)

store_id (number)

The two tables are linked in a one to many relationship with the tbl_sales store_id setup as a lookup

On the form I have a combox which is populated by a query i have that only shows active stores. This works great when adding records but if you editting and you edit a store that was closed after it was add the combobox simply defaults to the first availble value rather than have the old store plus the current active stores. Where should I start?

Also I want to be able to "merge" stores together so would it be better to do the "merge" in my reports and only merge when a date after the merge?

Many Thanks for your help.

I'm currently using the following code to automatically send e:Mail messages from Access (mdb db using Access2007). I want to add an additional static bcc to the message (ie; I've tried adding the name separated with a ";", but I'm getting a compile error (expected end of statement). Any suggestions?

This is how I modified the code:
DoCmd.SendObject , , _
acFormatRTF, _
strAssEmail, _
strMgrEmail, _
strBccEmail;"", _
strSubj, _
strMess, False, False

Here's the existing code:

Private Sub cmdSendRem1_Click()
Dim dbs As Database
Dim rs1 As Recordset
' Dim rs2 As Recordset
Dim RecCnt As Integer
Set dbs = CurrentDb()
Set rs1 = dbs.OpenRecordset("LogRem")
Set rs2 = dbs.OpenRecordset("qryTmpRem1")
RecCnt = 0
On Error GoTo Dberror

Do Until rs2.EOF
If rs2.Notify 0 Then
With rs1
!Week = rs2.Week
!LogonID = rs2.LogonID
!MessSent = Me.txtMessDate2
!MessNbr = rs2.MessNbr
!Period = rs2.Period
!Reason = rs2.Reason
!Associate = rs2.Associate
!AdmMgr = rs2.AdmMgr
!SchHrs = rs2.SchHrs
!ActHrs = rs2.ActHrs
!AdmLvl = rs2.AdmLvl
!Notify = rs2.Notify
!NOTcd = rs2.NOTcd
End With
Dim strMess As String
strAssEmail = rs2.eMail
strMgrEmail = rs2.MgrEmail
strBccEmail = ""
strGreeting = rs2.Greeting
strMessNbr = rs2.MessNbr
strPeriod = rs2.Period
strAssociate = rs2.Associate
strSchHrs = rs2.SchHrs
strActHrs = rs2.ActHrs
strSubj = "RMP" & strMessNbr & " Reminder to: " & strGreeting
strMess = "Associate Name: " & [strGreeting] & Chr$(13) & Chr$(13) _
& "Reporting Period: " & [strPeriod] & Chr$(13) & Chr$(13) _
& "Scheduled Hours: " & [strSchHrs] & Chr$(13) & Chr$(13) _
& "Hours Recorded: " & [strActHrs] & Chr$(13) & Chr$(13) _
& Chr$(13) & Chr$(13) _
& rs2.L01 & Chr$(13) _
& rs2.L02 & Chr$(13) _
& rs2.L03
DoCmd.SendObject , , _
acFormatRTF, _
strAssEmail, _
strMgrEmail, _
strBccEmail, _
strSubj, _
strMess, False, False

RecCnt = RecCnt + 1
End If


MsgBox RecCnt & " First Reminders Sent. ", vbInformation

' DoCmd.Close
' Forms.frmMainMenu.Visible = True

CurrentDb.Execute "DELETE * FROM tmpRem1;"
Exit Sub

If Err = 3021 Then
MsgBox "There are no messages to send. "
Forms.frmMainMenu.Visible = True

MsgBox "There was an error adding the record." _
& Err.Number & ", " & Err.Description
End If
End Sub

I got help with this question and it worked great, now I have another question(see below):

I'm trying to create a report that will show all cases in a database where the age of the case is 60 days or older.

I have a report which contains all the information that I want, but it lists all the cases in the database and is sorted from oldest to newest. (This report works fine)
The fields in this report are:
Date Received
Member #
Days Old

I want another report where I see only cases which are 60 or more days old.

SELECT YourTable.*
FROM YourTable
WHERE (((YourTable.[Date Received])


I have posted several questions regarding how to set up my database recently and i am now a little bit confused. I would appreciate anyones help on how many tables to use, relationships etc.

To briefly explain, my company has been awarded a contract to carry out safety checks on 6,000 main incoming meters for the UKs largest utilities provider.

My database needs to store the following info:

House No
Date job completed
Completed by (engineer name)

Engineer First Name
Engineer Last Name

Then there are about 10x fields for Y/N answers to job specific questions
Job aborted
Reasons for jobs aborted

I know how to create queries and would be pretty confident with that. The queries will be:

jobs oustanding
jobs completed
jobs completed by particular enigneer in a particular week
jobs aborted - reasons (text field)

I just need help with the tables and relationships so they all link in together. I want to get the d/b set up the best way i can from the start.

One other thing i would like to do is allocate jobs on the job table to certain employees, would i do that using a new filed on the job table and then a combo box?

apologies for all the questions at once. any help will be greatly appreciated.


I need help with a query and the control source of a combo box...I guess there might be several answers here, but here we go...

I am building a new (my first) database to keep track of certain data received monthly from several different countries. I was going to save all the data in tblReviews which has an individual ReviewID for each review (each monthly review from a particular country).

I want to give the user the option of looking at, for example, sales for December 2011 for 10 different countries. This is where I run into trouble...I want to have a combo box for the month and another one for the year. How would I set this up to then be able to utilize the user's selection in a query? Would it be easier to save the review dates in two individual fields?

Also, can a query take information from check boxes (where the user would pick which countries he or she wants to compare?) Thanks!

Hi guys,

I really need some help with my project and if any of you don't mind I would appreciate your input.

I'm basically creating an attendance record database where my "client" will need to enter whether the employee is present or not, but I am find it very difficult to know what I'm doing. Unfortunately I can't upload my database because it exceeds 500kb so if you don't mind giving me your email address so I can send it to you I would be grateful.

Thank you

Hello a question. Been hashing this out and cant get it to do what I want it to do. I have a Continuous Form that looks at a table and returns results based off a search box on a form. What I want to do is have a MsgBox pop up if no results are returned. Here is what I currently have:

Code: Private Sub search_button_Click() If Me.filter_ID = "" Then MsgBox "Please Enter Merchant (DBA or Legal) or an ID to Search for." Exit Sub Else Me.text_search.Visible = True Me.filter_ID = SuperReplace(Me.filter_ID, " ") Me.filter_ID = "*" & Me.filter_ID & "*" Me.RecordSource = "SELECT * FROM qryMERCHSearch WHERE DBASearch LIKE '" & Me.filter_ID & "'" Me.Requery Me.filter_ID = Replace(Me.filter_ID, "*", "") Me.ID.Visible = True Me.DBA.Visible = True Me.Merch.Visible = True Me.Email_Date.Visible = True Me.Line65.Visible = True Me.text_search.Visible = False End If End Sub

I know i need to do something like a If IsNull or IF ____ = True with the Recordsource and Requery function, but i cannot get it to work correctly...

any ideas???

Thanks ahead of time!!!!!

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.

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/numro de rfrence: " & stTicketID

stText =

'Write the e-mail content for sending to assignee
DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, -1

On Error GoTo Err_Execute

Exit Function


MsgBox Err.Description
Resume Exit_cmdMailTicket_Click

Exit Function

MsgBox Err.Description
Resume Exit_cmdMailTicket_Click

End Function

Hi All,

I have a Command Button on a Form that when executed will open my Outlook and add the email addresses of records assiciated with an ID Code field. This works great with multiple email addresses when the ID code if filtered. I posted the code behind the Command Button below.

Problem: If one of the filtered records does not have an email address I get the following message "Unknown message recipient(s); the message was not sent."

Is there something in the code I can change to ignore records without an email address?

Your help is much appreciated!!


Private Sub EMAIL_DRIVERS_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strEmailAddy As String
Set db = CurrentDb()
'following would be an SQL statement that retrieved the appropriate records. You could also use a query directly

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
Do While Not rs.EOF
strEmailAddy = strEmailAddy & rs![E-MAIL ADDRESS] & ";"
Set rs = Nothing
Set db = Nothing

On Error GoTo Err_cmdOpenEmail_Click
DoCmd.SendObject acSendNoObject, , , strEmailAddy, , , , , True

Exit Sub
If Err.Number = 2501 Then
Resume Next
MsgBox Err.Description
Resume Exit_cmdOpenEmail_Click
End If

End Sub

Hi Folks,

I could use a little help with writing values for code comparing current and previous values ...

The code is below:

Option Compare Database
Function runcompare()

Dim rst As Recordset
Dim dbs As Database
Dim querystr As String
Dim qryprev As String
Dim qrycurrent As String
Dim rstcnt As Integer
Set dbs = CurrentDb

querystr = "SELECT subno, c_proc from BaseNormalized;"

Set rst = dbs.OpenRecordset(querystr)

For rstcnt = 0 To rst.RecordCount - 1
qryprev = rst.Fields("subno").Value
qryprev = qryprev & rst.Fields("c_proc").Value
If rstcnt rst.RecordCount - 1 Then
qrycurrent = rst.Fields("subno").Value
qrycurrent = qryprev & rst.Fields("c_proc").Value
'If qryprev qrycurrent Then WRITE QRYPREV TO TABLE [DIFFERENCE]
End If
End If
Next rstcnt

End Function

What I am attempting to do is compare current values in terms of the field subno and the values c_proc to the previous values; and if different, to write that previous value (both subno and c_proc) into a table. I am not quite sure if the above logic is correct, I think it is? But what I know I need help with would be writing the elements to a table ... using the docmd.gotorecord I don't see how I would use that command to actually insert the values of qryprev into a table if qryprev qrycurrent. Any help would be greatly appreciated.

Thank you,


I have been learning so much on this forum over the past few months, but I am having some trouble bringing together all the information I have found in this forum regarding file directories and 'automated' reporting from Access. I think I have a rough idea of the different functions needed (Len, Dir, Mkdir) but I would love if someone could help me bring it all together, or perhaps provide a bit more explanation so I can tailor these procedures for my own needs.

The simple explanation: I would like to add functionality to my database that would allow me to manage and work with pdfs and word documents related to a particular record in the database.

1. User installs .mde database the first time it is opened a VBA code creates a "documentation" directory in the same folder as the actual database. (e.g. Database is located at C:User...DatabaseDatabase.mde and at first start, the database creates C:User...DatabaseDocumentation).

2. When a user is in the form for entering data in a record, they can click a button that allows them to add a file to the 'documentation' for that record. I would want that file to then be moved or copied to C:User...DatabaseDocumentation[Name of record]. I will need Access to create a folder for each record when the record is created.

3. Once I have the file management part squared away, there are three things I would like to be able to do with these files:
a. Print all (or selected) files as a full report w/documentation, along with a report I've created in Access.
b. Save all documentation and Access report as a PDF at a user-specified location.
c. Pass the full report with documentation to outlook to be sent as an email attachment.

I would welcome any reference materials or examples of a similar setup that anyone could recommend regarding any part of this query. Hope I haven't asked for too much; any help will be much appreciated!

P.S. - I am running an Access 2007 database in Access 2010 on Windows XP. This will be the case for most computers the program is installed on, but I need to know about any compatibility issues just in case.

I am in need of help with a loop problem.

I have a form that contains a control button to email reports. The code is below. It worked great as long as I used the DoCmd.SendObj that is commented out. I was forced to go to new code (not mine, but came from MS website) due to a known bug in Access that caused a GPF. After inserting the new code, the code will not loop. Instead it sends the same report to the same person each time. I need it to loop through each record and send the appropriate report to the appropriate email address (FirstSPEmail).

If anyone can give me guidance as to why the loop won't work now BUT did with the commented section, I would greatly appreciate.

Dim db As Database
Dim RstTmp As Recordset
Dim Loop1 As Long
Dim SqlStr As String
Dim txtSPID As Control
Dim clsSendObject As accSendObject

Set db = CurrentDb()

SqlStr = "SELECT DISTINCTROW First(qryMergedEmail.SPName) AS [First Of SPName], First(qryMergedEmail.SPID) AS [FirstOfSPID], First(qryMergedEmail.CustomerID) AS FirstOfCustomerID, First(qryMergedEmail.AcctID) AS FirstOfAcctID, First(qryMergedEmail.SPEmail) AS FirstSPEmail, First(qryMergedEmail.Customer) AS FirstOfCustomer, FROM qryMergedEmail GROUP BY qryMergedEmail.SPName;"
Set RstTmp = db.OpenRecordset(SqlStr, dbOpenSnapshot)

On Error Resume Next
On Error GoTo 0

If RstTmp.RecordCount = 0 Then
MsgBox "There are no events"
Exit Sub
End If

For Loop1 = 1 To RstTmp.RecordCount

Me!txtSPID = RstTmp![FirstOfSPID]

'Old SendObject that worked except for 3000 character GPF starts here
' DoCmd.SendObject acSendReport, "rptWorksheetRentalEmail", acFormatSNP, _
' RstTmp!FirstSPEmail, , , "Account Revenue Worksheet", "Please complete and fax to me", False
'Old SendObject that worked except for 3000 character GPF ends here

'New Begins here that doesn't loop properly
Set clsSendObject = New accSendObject
clsSendObject.SendObject acSendReport, "rptWorksheetRentalEmail", accOutputSNP, _
RstTmp!FirstSPEmail, , , "Account Revenue Worksheet", "Please complete and fax to me", False
Set clsSendObject = Nothing

Next Loop1

MsgBox (Loop1 - 1) & " Messages sent"


Exit Sub

MsgBox Err.Description
Resume Exit_cmdEmail_Click

End Sub

Not finding an answer? Try a Google search.