By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,998 Members | 2,815 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,998 IT Pros & Developers. It's quick & easy.

Command Button to Send E-mail to multiple people from report.

P: 9
Hello,

We have a database that keeps track of contractors. We use the DB to find contractors based on location and other information.

When you want to find contractors you enter information such as state and city in a search form click a button which runs a report that displays all contractors meeting the criteria you entered on the search form.

The report includes a bunch of information and one of them is the contractors e-mail addresses. Sometimes we want to send an e-mail to all those contractors listed on the report.

What we would like to do is have a command button on the report header that would simply allow us to click it and it open an new e-mail and populate the To box with all of their addresses automatically.

I wrote this simple little bit of code but when you click it, it opens outlook and creates a new e-mail but only includes the e-mail address of the first record displayed on the report. The field on the report that contains their e-mail address is called "Con_E-mail_Address"

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command35_Click()
  2.  
  3. Dim ToName As String
  4.  
  5. ToName = [Con_E-mail_Address] & ";"
  6.  
  7. DoCmd.SendObject acSendNoObject, , , ToName
  8.  
  9. End Sub
How do I get it to add the e-mail addresses of the other contractors displayed on the report?

[z{moderator's edit:= Merged second post with first}]

I might also need to note that the report is based off a query that looks at the fields on the open search form. The name of that query is "QryContractors_BySearch"
Jan 4 '14 #1
Share this Question
Share on Google+
17 Replies


ADezii
Expert 5K+
P: 8,638
This can be accomplished rather easily with Automation Code, as in:
  1. Create a Command Button on the Report Header as indicated.
  2. Set a Reference to the Microsoft Outlook XX.X Object Library.
  3. Place the following Code in the Click() Event of the Report Header. The Code will:
    1. Open a Recordset vased on QryContractors_BySearch, the Record Source of the Report.
    2. Loop thru all the Contractors E-Mail Addressess building a TO: String (assums a [Contractors] Field Name].
    3. Open Outlook via Automation and plug in the necessary Values including the TO: String.
    4. Wait for you to Click the Send Button.
    'Must set a Reference to the Microsoft Outlook X.XX Object Library
    Expand|Select|Wrap|Line Numbers
    1. Dim MyDB As DAO.Database
    2. Dim rst As DAO.Recordset
    3. Dim strBuild As String
    4. Dim oLook As Object
    5. Dim oMail As Object
    6. Dim olns As Outlook.NameSpace
    7.  
    8. Set MyDB = CurrentDb
    9. Set rst = MyDB.OpenRecordset("QryContractors_BySearch", dbOpenForwardOnly)
    10.  
    11. Set oLook = CreateObject("Outlook.Application")
    12. Set olns = oLook.GetNamespace("MAPI")
    13. Set oMail = oLook.CreateItem(0)
    14.  
    15. With rst
    16.   Do While Not .EOF
    17.     strBuild = strBuild & ![Contractor] & ";"
    18.       .MoveNext
    19.   Loop
    20. End With
    21.  
    22. With oMail
    23.   .To = Left$(strBuild, Len(strBuild) - 1)
    24.   .Body = "Standard Message"
    25.   .Subject = "Contractor Bidding Report"
    26.     .Display
    27. End With
    28.  
    29. Set oMail = Nothing
    30. Set oLook = Nothing
    31.  
    32. rst.Close
    33. Set rst = Nothing
  4. Any questions, feel free to ask.
Jan 4 '14 #2

zmbd
Expert Mod 5K+
P: 5,397
ADezii's method has greater flexibilty and avoids some limitations that the sendobject method has (one such that Memo Fields are truncated at 255 characters); however, for simple reports, it is a the sendobject method simple method and very easily used.

DoCmd.SendObject Method (Access/Office 2010) You would build a string that contains the email addresses of the members you needed to send the email.
You would add the report as an attachment. I would recommend that it be the PDF format provided you are using ACC2007-w/servicepak or ACC2010 or newer as the PDF format is native and retains the report formating.

mjtrike97: Respectfully, we do as that normally each thread handle a single question/topic; however, in this casem the two are so closely related the answer is the same. (^_^)
Jan 4 '14 #3

ADezii
Expert 5K+
P: 8,638
I thought that the idea was to Send an E-Mail to only those Contractors specified in the Report, and not the actual Report itself.
The report includes a bunch of information and one of them is the contractors e-mail addresses. Sometimes we want to send an e-mail to all those contractors listed on the report.
I figured that Contractors would not be interested in their opposition's E-Mail Addressess, but in hindsight, I could have misread the question. It wouldn't be the first time, and surely not the last! (LOL).
Jan 4 '14 #4

zmbd
Expert Mod 5K+
P: 5,397
ADezii:= I thought that the idea was to Send an E-Mail to only those Contractors specified in the Report, and not the actual Report itself.
Yikes, that could be bad!

Certainly I would hope that OP would have a query to handle this.

I know when I start out with emailing things I have a batch of trusted "in-house" company emails that I use instead of the correct ones so that I know how the code runs before I start sending "live." However, I have an execelent group of co-workers and management team that I can trust to help me with these alpha and beta tests!
Jan 4 '14 #5

P: 9
Hi, ty for the responses. No I do not want to email the report itself I want to email the people listed on he report. I will give this code a try. Thanks!
Jan 4 '14 #6

P: 9
When I plug in the code I get an error;

User-defined type not defined on line

Dim olns As Outlook.NameSpace

I am not sure if this is because I do not understand

step 2. Set a Reference to the Microsoft Outlook XX.X Object Library.

Also; for line

strBuild = strBuild & ![Contractor] & ";"

would the [Contractor] be the field on the report that actually contains the e-mail address of the contacts?

I know these are very noob questions and really appreciate the help.
Jan 4 '14 #7

ADezii
Expert 5K+
P: 8,638
Set a Reference to the Microsoft Outlook XX.X Object Library.
In any Code Window, select Tools ==> References ==> Scroll Down & Select the Microsoft Outlook XX.X Object Library ==> OK.
would the [Contractor] be the field on the report that actually contains the e-mail address of the contacts?
Yes, it is the Name of the Field in the Report.
Jan 4 '14 #8

P: 9
I believe I am getting there; but I am getting error

Run-time error '3061" Too few parameters. Expected 7 on line

Expand|Select|Wrap|Line Numbers
  1. Set rst = MyDB.OpenRecordset("QryContractors_BySearch", dbOpenForwardOnly)
I notice this line is calling the Query the report is based on, the query has 7 different criteria first and last name, city, state, zip, language and county. So it seems this error is related to that some how.

On those 7 fields in my query I have this listed in criteria

Expand|Select|Wrap|Line Numbers
  1. Like "*" & [Forms]![Reporting]![txtBox] & "*" Or Is Null
which looks at the data entered in text boxes on the search form (called reporting).
Jan 4 '14 #9

ADezii
Expert 5K+
P: 8,638
It appears as though one of you Parameters is not being resolved prior to the Report Opening. It appears as though the Report is being Opened from the Reporting Form, is this Form Active when the Report Opens?
Jan 5 '14 #10

P: 9
Hi, yes the search/report form is open when I click the command button on the generated report.

A user would open the search/reports form to search for a contractor. Enter what they wanted to search by, first,last,city,state,zip and language (the contractors provide interpretation hence the language option).

Then they click a search button which runs the report. The report is fed by the query which reads the text boxes on the search/reports form.

Now that they have a report displaying a list of contractors that meet their criteria I want them to be able to e-mail them a message so I have put this command button at the top of the report header with the hopes of creating a blank e-mail (nothing attached) with all the people in the report added to the To box.

It seems like the code is saying the report/search form is closed so the query cannot find the parameters but it is still open and the query still works if I click on it directly.
Jan 5 '14 #11

zmbd
Expert Mod 5K+
P: 5,397
you maybe out of scope, once the report opens, that's about it.
THe code in the report header command button, that is the code you posted in #1?
Jan 5 '14 #12

P: 9
In my first post that is what I had in there. I have since tried to replace it with what ADezii had provided. I used a report to generate the results because I didn't want them editing contractor records.
Jan 5 '14 #13

P: 9
Do you think if I put the command button to send the e-mail on the search/form itself instead of the report that might make a difference?
Jan 5 '14 #14

P: 9
hmm, still doesn't seem to make a difference even putting the button on the search form instead of the report. Do I have to specify in the code some how that for each of those parameter required by the query that it should be looking at the text boxes on the search form?

Seems like the query should already be looking there.

Update: Looking online it seems that because the query is referencing form data (which is fine when running the query directly or using it to populate a report)when calling the query from VB code however this is invalid.

I found this think dealing with something similar

http://bytes.com/topic/access/answer...s-expected-1-a

But I am not exactly sure how to implement their recommendation in this circumstance. Any ideas?
Jan 5 '14 #15

P: 9
Ok, after some more research I changed the code to this and it seems to be working now. This allowed me to specify the parameters the query was looking for.

I apologize for my lack of knowledge but I have zero coding education and this is my first DB. Does anyone see anything that might lead to a problem with the way I coded it?

Also ty very much ADezii for helping me with this.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command35_Click()
  2.  
  3. Dim MyDB As DAO.Database
  4. Dim rst As DAO.Recordset
  5. Dim strBuild As String
  6. Dim oLook As Object
  7. Dim oMail As Object
  8. Dim olns As Outlook.NameSpace
  9.  
  10. Set MyDB = CurrentDb
  11. Set qdf = MyDB.QueryDefs("QryContractors_BySearch")
  12. qdf(0) = Forms!Reporting!txtFirst
  13. qdf(1) = Forms!Reporting!txtLast
  14. qdf(2) = Forms!Reporting!txtCity
  15. qdf(3) = Forms!Reporting!txtState
  16. qdf(4) = Forms!Reporting!txtZip
  17. qdf(5) = Forms!Reporting!txtCounty
  18. qdf(6) = Forms!Reporting!txtLanguage
  19. Set rst = qdf.OpenRecordset(dbOpenSnapshot)
  20.  
  21.  
  22. Set oLook = CreateObject("Outlook.Application")
  23. Set olns = oLook.GetNamespace("MAPI")
  24. Set oMail = oLook.CreateItem(0)
  25.  
  26. With rst
  27.   Do While Not .EOF
  28.     strBuild = strBuild & ![Con_E-mail_Address] & ";"
  29.       .MoveNext
  30.   Loop
  31. End With
  32.  
  33. With oMail
  34.   .To = Left$(strBuild, Len(strBuild) - 1)
  35.   .Body = "Standard Message"
  36.   .Subject = "Contractor Bidding Report"
  37.     .Display
  38. End With
  39.  
  40. Set oMail = Nothing
  41. Set oLook = Nothing
  42.  
  43. rst.Close
  44. Set rst = Nothing
  45.  
  46. End Sub
Jan 5 '14 #16

zmbd
Expert Mod 5K+
P: 5,397
mjtrike97

Sorry, it is the weekend and family comes first

Looking at your code, the record set is opened as forward only. Once the report was ran, I suspect that set the pointers were already set to the end.

If you try opening the recordset as dbopendynamic then see if the same error happens.

As for your workaround, allow me to digest that for a little bit. Sundays are always a bit busy with four-kids and church (^_^)
Jan 5 '14 #17

ADezii
Expert 5K+
P: 8,638
If you are performing this operation within the context of your Form, you can set a Recordset Object Variable equal to the Form's Recordset, and loop through the appropriate Field building your TO: String. I would imagine that all of the Parameters for the underlying Record Source would have been resolved at this point, negating the need for qualifying the Parameters yourself. zmbd also had a good point in stating that dbOpenForwardOnly could possibly cause interference in this process. Let us know how you make out.
Expand|Select|Wrap|Line Numbers
  1. '*************** CODE INTENTIONALLY OMITTED ***************
  2. Dim rst As DAO.Recordset
  3. Dim strBuild As String
  4.  
  5. Set rst = Me.Recordset
  6.  
  7. With rst
  8.   Do While Not .EOF
  9.     strBuild = strBuild & ![Con_E-mail_Address] & ";"
  10.       .MoveNext
  11.   Loop
  12. End With
  13.  
  14. '*************** CODE INTENTIONALLY OMITTED ***************
Jan 5 '14 #18

Post your reply

Sign in to post your reply or Sign up for a free account.