Email based on Department selection in form

[c1bluemini]

I am trying to generate an email that will send a copy of the last record entered from my table "Main" to a departement email, based on the department field entry on my form.

I know that I can send emails both thru outlook as well as direct. Would prefer to sent the direct (without opening outlook).

Here is what I'm working with:

Table with record informaiton called "Main"

Form called "Action Entry" which contains the information that I would like to send- part of which is a feild called "Assigned To" which is a list of departemnts linked to a secondary table called "departments"

Table called " departments" which contains the following Fields: ID, Departments, Email.

What I would like to happen is when I hit the exit button on my form, an email goes out to the "Assigned Department" email address associated to the select departmet, that shows the information in the form (which i expect will be a report saved as PDF). I understand some of the VB code to create an email, but don't knwo how to tell it to select the correct email based on the Department selected on the form.

Any help would be appreciated that can get me going in the right direction.

 

[AOB]

Is there a control on the form which holds the e-mail address to which the report should be sent? If so, you could just refer to its value when generating the e-mail using the .SendObject method :

DoCmd.SendObject acSendReport, "rptMyReport", acFormatPDF, [COLOR=red]Me.txtEMailAddress.Value[/COLOR]

Or, if not, you could use a domain aggregate function to pull the related email address from the table based on some other value which is available (e.g. the primary key, or ID, associated with it) :

DoCmd.SendObject acSendReport, "rptMyReport", acFormatPDF, [COLOR=red]DLookup("[EMail]", "[departments]", "[ID] = " & Me.txtDepartmentID.Value)[/COLOR]

(Personal opinion - I don't like having a field with the same name as the table in which it resides - á la "departments" here - it will get very confusing and may even cause conflicts elsewhere - a good naming convention would be that a field should be singular whereas a table should be plural. In other words, you have a table of "Departments" within which there is a "Department" field...)

When you say you know how to send emails thru Outlook as well as "direct", what exactly do you mean? What email agent, other than Outlook, are you using? Or do you just mean not referring to the Outlook application object? Because I strongly suspect that you will be sending via Outlook regardless. And that means Outlook will have to be open, or opened, at some point. And also means that Outlook's security settings will take precedence (i.e. the user will be prompted to allow the e-mail to be sent on their behalf - you cannot override this as it is inherent to Outlook, not Access. It can be done but only by playing around with the security settings in Outlook)

Personally, I would make use of the Outlook object as this gives you more freedom to customise the e-mail, add multiple attachments etc. You can write a separate function to compile your e-mail addresses, your email body etc. And you can do it in such a way that it all gets done in the background, and the Outlook message only appears when it's ready. I've just posted some code on another thread to help somebody do something similar, it may help you?

Link to thread

 

[c1bluemini]

AOB....thanks for the help! Was trying to over think the process and missed the obvious.

 

[pbaldy]

When you say you know how to send emails thru Outlook as well as "direct", what exactly do you mean? What email agent, other than Outlook, are you using? Or do you just mean not referring to the Outlook application object? Because I strongly suspect that you will be sending via Outlook regardless. And that means Outlook will have to be open, or opened, at some point. And also means that Outlook's security settings will take precedence (i.e. the user will be prompted to allow the e-mail to be sent on their behalf - you cannot override this as it is inherent to Outlook, not Access. It can be done but only by playing around with the security settings in Outlook)

FYI, CDO can be used and bypasses Outlook and its security settings.

 

[AOB]

FYI, CDO can be used and bypasses Outlook and its security settings.

Tell me more??...

 

[pbaldy]

Google hits include:

http://www.paulsadowski.com/wsh/cdo.htm

I've only used the "remote" method where you specify the server, credentials, etc. Typically that's been an Exchange server for me, but it doesn't need to be. One downside is that since you're bypassing the email client, the email doesn't show up in Sent Items. I typically bcc myself or the appropriate person.