Issues sendin e-mail from Access

[mari_hitz]

Hi!
Hope everyone is great. I am here because I am in the middle of creating a pretty complicated database and I am do not have much knowledge of VBA code.
Specifically what I am trying to do is send an e-mail from access trough Outlook, which I have found that can be done easily. However I would like to send the e-mails to a specific amount of people that are going to be gotten by a Query that I have made in my database.
The purpose of my database is to compare information between several tables which contains people of whom I would like to know if they have been compliance with the policies of my company or not; and to those who have not send an e-mail to ask for the information I am needing from their side. So the list of e-mails should result from the query that I have created to compare the tables.
What I have tried so far is creating a macro with "Send Object" to send the e-mail, however in the "To" field I can not edit to tell the macro it should choose from the "Enterprise ID" field that results from the query.
Am I doing right this? Could I built a code to do this? I do not know much about VBA and I could not find in the web a code that can do such thing. How could I do to send the e-mail to the people I want?

Please find attached an example of the database I had created.

Thanks!

 

[pbaldy]

This should get you started:

http://www.granite.ab.ca/access/email/recordsetloop.htm

 

[mari_hitz]

Thanks Pbaldy for your link. As I have said before I do not have knowldge of VBA, so I could not understand the code what was posted in your link. I have tried however to read that code and I could not find the place on where should I put the reference so the code can identify the field "Enterprise ID" in the Query and send the e-mail to that adresses.
As far as I could see in this forum, it should be more simple if I create a form and I indicate the code to search the mails from that txt box field. Am I right?

Sorry I could not understand the code, however thanks for posting it!
​

 

[pbaldy]

If you're going to send to a single address, a form reference would probably be simpler for you. If you want to send to a list, this type of thing is probably necessary.

 

[mari_hitz]

Thanks again Pbaldy!
In the example database it only appears one person in the query, however there will be more because I only put a few records in the table. I forgot to mention that I would like the e-mail to be sent with BCC, I know that it should be a to so I decided to put my own to make the code work.
I am still viewing the link trying to understand and altough it is rather complicated for me because I do not know much about code building I making an effort. So here are my questions:
1)where should I indicate the code that the e-mails should be obtained from the field "Entprise ID" of the query? Should it go here?

            strTo = RS!cEmailAddress

If so, based on what I have written before, I should put in there my mail and create a new line: srtBCC= ?
2) I have read in a few places that for codes to send e.mail can work I should go to tools-references and add Microsoft Office Outlook right?
3) How this code will run? I should click on the macro? I do not want the navgation pannel to be seen after the database is completed, so I should add some command to the code to run itslef once the Query is runned?

Thanks for your help and I know it must be a pain to try to explain people who is totally ignorant of VBA certain things, nevertheless I would kindly appreciate if you could elaborate a little bit more so I could understand better and start working instead of asking so much. Your help will be highly appreciated!

 

[pbaldy]

1) Yes, that's the spot.
2) that is one way among many. That would be for using Outlook automation with early binding. At the bottom of this link a number of methods are described:

http://www.granite.ab.ca/access/email.htm

3) depends on your situation, but commonly behind a button:

http://www.baldyweb.com/FirstVBA.htm

 

[mari_hitz]

Thanks for your help Pbaldy. I am still struggling with this. I have copied a code that I have found in the Microsoft page, however when I am debuggin it returns me error in the following line:

Dim objOutlook As Outlook.Application

I have gone to "Tools-References" and I have added the Microsoft Outlook library.

The entire code is the following, I have customized it to my needs. I cannot see if it works or it has other errors because it does not let me continue. Any toughts?

Option Compare Database

Private Sub Command12_Click()

  Dim MyDB As Database
  Dim MyRS As Recordset
  Dim objOutlook As Outlook.Application
  Dim objOutlookMsg As Outlook.MailItem
  Dim objOutlookRecip As Outlook.Recipient
  Dim objOutlookAttach As Outlook.Attachment
  Dim TheAddress As String

  Set MyDB = CurrentDb
  Set MyRS = MyDB.OpenRecordset("Query1")
  MyRS.MoveFirst

  ' Create the Outlook session.
  Set objOutlook = CreateObject("Outlook.Application")
  
  Do Until MyRS.EOF
  ' Create the e-mail message.
  Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
  TheAddress = MyRS![EntpriseID]

     With objOutlookMsg
        ' Add the To recipients to the e-mail message.
        Set objOutlookRecip = .Recipients.Add(TheAddress)
        objOutlookRecip.Type = olTo

        ' Add the Cc recipients to the e-mail message.
        If (IsNull(Forms!Query1!EnterpriseID)) Then
        Else
           Set objOutlookRecip = .Recipients.Add(Forms!Query1!EnterpriseID)
           objOutlookRecip.Type = olCC
        End If
   
        ' Set the Subject, the Body, and the Importance of the e-mail message.
        .Subject = "Action Required"
        .Body = "Test"
        .Importance = olImportanceHigh  'High importance
        
     
        ' Resolve the name of each Recipient.
        For Each objOutlookRecip In .Recipients
           objOutlookRecip.Resolve
           If Not objOutlookRecip.Resolve Then
             objOutlookMsg.Display
           End If
        Next
        .Send
      End With
      MyRS.MoveNext
   Loop
   Set objOutlookMsg = Nothing
   Set objOutlook = Nothing
End Sub

 

[pbaldy]

What is the error? If you've checked the version of Outlook you have in Tools/References, that line should not error.

 

[mari_hitz]

I am sorry for not posting the error, I forgot. The error that gives me is "Compile Error: User-defined type not defined" and it highlights the line

Dim objOutlook As Outlook.Application

only after Dim, this firt word it is not highlighted.

Thanks!

 

[mari_hitz]

Hi! I am sorry but I am running the code in my job PC's and the code now returns me error (Item not found in the collection) in the following line:

TheAddress = MyRS![EntpriseID]

One question, does MyRS! means something? I mean, I have tried to search in the web the meaning of that variable and I could not find it, it is weird since I have got the code from a Microsoft page. Maybe it is something that it is setled in that particular database only?
If so, in that code, for what I can understand, the mails are obtained from a table that it is called tblMail. Since I am not going to have always the same e-mails because they come from reports we pull from our database I have indicated the code to search the mails from the Query1 form. Maybe MyRS! it is only for tables, I do not know. Could you please give a hand?

Thanks!

 

[pbaldy]

MyRS is simply the name someone decided to call the recordset variable. I tend to use more descriptive names, like rsVehicles. Basing the recordset on a query is common, letting the records returned vary based on some criteria.

The error you're getting implies that isn't the name of the field in your table/query. It needs to be spelled exactly as it is in the table/query. If there's a space as you had in it earlier, it has to be included (and bracketed):

TheAddress = MyRS![Entprise ID]