Emailing a report for a person based on position in company

[Rinoaerith]

Hi all, me again, I have looked all day on how to accomplish this without success also. I was able to create a VBA code to send email based on a combobox that when updated it produces a email for the person that is being choosen. So, i tried to re-work this same code to try to accomplish what I will describe below:

tblEmployees:
Employee name (PK)


e-mail
job position
team

I have a form in which the user input data and click a button to update the DB.
I have also a query that returns the records that was included in that day and a report based on this query called ReportSupervisor
I wish that when the user click the button, an email with this report is sent to the email registered in tblEmployees if the job position is = to Supervisor

I tried the following code:

Private Sub Comando87_Click()
    Dim sSubject As String
    Dim stMail As String
    Dim strBody As String
   
    'message subject
    sSubject = "New information included for your evaluation"
    
    'sends email to whoever is described as supervisor in tblEmployees
    stMail = DLookup("[e-mail]", "tblEmployees", "[job position]= Supervisor")
    
    'email body
    strBody = "See email attached for additional information"
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.SendObject acSendReport, ReportSupervisor, acFormatPDF, , , , sSubject, strBody, True
End Sub

When I tried this code I get an error message:

Error in execution time '3075'
Sintaxe error (missing operator) in de query expression '[job position]= Supervisor'

and it highlights the line: stMail = DLookup("[e-mail]", "Employees", "[job position]= Supervisor")

I dont understand almost nothing (barely nothing) on VBA codes, so please forgive me if I am asking something that is basic or making basic mistakes that I am not being able to see .

If possible also, I would like to send two reports on this, so could I repeat the DoCmd.SendObject line changing the name of the report for the other one that I want?

Is there any other code that could do this task easily?

I have two of my questions that I was able to accomplish my goal thanks to this forum, so I am always backing here for be save!!! Thank you in advance!!

[June7]

Text fields require apostrophe delimiters for the parameter. Date fields would require # delimiters.

DLookup("[e-mail]", "Employees", "[job position]= 'Supervisor'")

However, that will return the e-mail for first Supervisor record encountered in the table.

[Rinoaerith]

Hi June7, you are a life saver! Sorry for asking such silly question but I am trying to learn the most I can... In fact, I gave only examples of nomes for you to understand my tables/forms... since I am from Brazil, my code is all described in portuguese. I have only one supervisor possible but I had included like "New products supervisor" in his job position, so it could not be mixed with the other two supervisors that we have (that is consolidated product supervisor and documents supervisor), thank you for the tip!

I was also able to send both reports by outlook, but keep having that nasty warning message from Outlook for what I see here there is normal and I cannot do anything (unless wraparounds that I am not authorized in the network as I have already checked with my IT department... ).

I have other problem that is related to the use of navigation form in access 2010 with a variation of this same code above that run perfectely when I had the form opened, but gives me the error:

run-time error '2465': Microsoft Access cant find the field 'FormName' referred to in your expression (being the form name correct described as my form name, since the code is working when using it in form not attached to navigation form). I am learning that when I include the form in the navigation form, in fact the name of the form has to be changed. it is correct? Other info point me to include an OpenForm event on VBA code before my code lines to silent open the form but this is also not working... if I cannot find a solution for this I will probably contact the forum again... as I said you is a life saver.

And sorry for my english, my primary language (and most the only one that I use) is portuguese.

Again, thank you very much!

[June7]

The code has to be changed to reference the navigation form container that holds the form.

Review: https://www.accessforums.net/forms/t...orm-32053.html

E-mail using CDO (Collaboration Data Object) might be possible. But your IT staff might block that as well. https://www.accessforums.net/program...ens-43311.html