Access Email Question

[Playbus]

The purpose of my new database is customer queries. This is for our Customer Support rep, who when she recieves a query from a customer, passes it on to the relevant department, via email, to deal with it.

I have a module which has the code to create a new Email. This works, I can get Access to make Outlook start a new mail.

The database has all the possible recipient addresses in a table, and I have a subform within my "QueryEntry" form which automatically works out what email address the query should be sent to, and displays said address in a field on the subform. So, the recipient address is displayed in an open form at the moment of email-sending.

What I need is for the automated email to pick up this recipient from the form, and enter it automatically into the "To" field on the email.

In the code, I have these lines (amongst many others):

With objMsg
.To = "Recipient"
.Subject = "New Query"
.Body = "QueryText"
.Importance = olImportanceHigh

What would I need to replace "Recipient" and "QueryText" with to make these items get picked up from an open form on the screen?

Can it be done this way?

I hope that makes sense. I'm still an Access Novice so please be gentle.

Thanks.

 

[pbaldy]

Try:

.To = Forms!FormName.ControlName

Same for the other one.

 

[Playbus]

Thankyou!

I will try that today and report back with my results.



/edit

No, that doesn't seem to work. Normally, if I have "Anything in quotation marks like this" after .To and .Body, then I get a new email appearing on my screen, with these typed into the To box and the message body as you'd expect.

However when I put the link to the field on the form as you suggested, I get nothing at all.

Help!

 

[Playbus]

Nevermind. I now have it all working. Thanks anyway pbaldy

 

[pbaldy]

Well, I tested that and it worked perfectly, but I'm glad you found a solution. Can you post it here so someone with a similar problem can find it later?

 

[Playbus]

Well I went about it a whole different way, using a macro (I can feel the glares of disgust from the experienced Access people here already) and an extra hidden form. The method I used in the end is probably not very efficient, but it works and is definately good enough for my purpose at hand.

Basically I got a hidden form to appear (as odd as that sounds) when a new record is entered and pull the info I need for sending the email from the original form, using SetValue. Then the macro closes the original form (to save the newly entered record) and runs the report to be emailed, which pulls it's required information from both the query attached to the report, and the hidden form I mentioned earlier. I used SendObject to send the email, with the .To parameter set to pull the email address from the hidden form.

I am quite aware of how insane that may sound but I am not a very good explainer of things I am afraid. Suffice to say, my problem is fixed and my boss is happy........and it's bought me some time to learn how to do it properly next time.

 

[BukHix]

Lets say that you have three textbox fields on your form. The fields are named txtEmailTo, txtSubject, and txtMessage. You can fill your SendObject command with values from the form fields with the following code.

Dim strToField as String
Dim strSubject as String
Dim strMessage As String

' use the Me.[FieldName] to get the value from any field.

strToField = Me.txtEmailTo
strSubject = Me.txtSubject
strMessage = Me.txtMessage

DoCmd.SendObject , , , strToField, , , strSubject, strMessage, False

All you need to do is put the code behind a command button on your form and change the txtbox field names to match your own. Post back if you have more questions about this. One more note about this is that you can view the email before they are sent by changing the False to True at the end of the SendObject statement.

 

[Playbus]

Thanks BukHix. That will come in handy.