Combo Box event based on selection

[orange]

Are you saying there is a different email template for every City State combination?

We can only respond/advise based on what you tell us. Do you have any more info.

eg,


When I select Atlanta, Ga I want the XXX template to open???

How do you relate the template to be opened to the value in the combo?

[tobydobo]

well yes..thats what I want to happen. I dont have more information other than what I would "like" for it to do. your last 2 questions are exactly what I need to find out how to do.

[orange]

I would suggest a lookup table and depending on how you determine which template goes with a location (City/State combo).

tblTemplateAssignment
id autonumber PK
CityStateId
TemplateNumber

where CityStateId is an identifier to your City State name in that Table
and TemplateNumber is the identifier of the TemplateRequired from your Template table.

[June7]

Because of your earlier question about possibly using FollowHyperlink to open template, I presume you do not yet have code to send the email? I don't think FollowHyperlink will work for email. See http://visualbasics.wordpress.com/20...utlook-emails/ The example is setup for Excel but adaptable to Access.

Why are you using templates anyway? It is possible to send email tailored to the recipient without template. Here is simple example http://forums.aspfree.com/microsoft-...ro-447084.html

Do you have a table of email addresses? Did you know you can link to Outlook addressbook folder and work with it like a table?

And another example
http://www.blueclaw-db.com/access_em...nd_outlook.htm

[Stingaway]

I dont think the links are going to help in this situation. I understand combo boxes can be multi column, but the problem is that I have yet to find a way to specify specific action per selection within a drop down. Each selection will have a completely unique email template that will launch.

Can each selection's unique email template be defined, or will each selection have a case by case basis - for example, if I select the first one, will the email template always be the same for that selection, or will it change depending on another set of circumstances?

If the answer is that there is a 1 to 1 correlation between the selection and the email action / template being used, then you can set up table driven values for the selection and the action and then code it either with case statements in VBA or set up macros to run based on the selection - with the macros handling the email event.

[orange]

I think we're seeing that the original post was a little scant on details. As we hear more ( or make more guesses) , options arise. I think tobydobo will have to fill in the missing info if we are to be of real assistance.

I'd like to see a model of his tables and relationships.

[tobydobo]

Hi June. I am currently using the followHyperlink method for email templates on another form I have, but it is based on a command button. I can use that commmand because the template is stored on a sharepoint, which has a direct URL. So when I click the command button, it autolaunches the URL which opens the template. I like your idea of not using templates though and have everything generated from db using a table of email addresses, just wanst sure how to do that. Im still a novice....most of the tricks ive learned have been on this forum, so I will definitely look at the links you provided.

Orange,
I didnt really know how or what to ask at the outset so having you guys provide feedback is definitely helping, I will look into your suggestion as well.

Stingaway,
Your suggestion also seems like it may work....can you provide more details as to go about it?

[June7]

Review this recent thread on email for ideas https://www.accessforums.net/import-...ook-18221.html

[tobydobo]

June7
I absolutely love the email form sample on Blueclaw's website! I downloaded the demo and studied the code and I think I can totally make that work for my needs. I now just need to create a table, import my email addresses from Outlook, add the text for the body of the emails into the table as well ?? I guess. Also I assume I will have to use HTML code to format the text correct?

[June7]

I don't know about HTML code in the email. My efforts in this never got that complicated. I send only one email to one address and the body is a one liner of about 5 words, and a zipped accdb as attachment. Maybe this will help answer http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

[tobydobo]

Well our template we use has some text bold, some text red, some text underlined, as well as a hyperlink in the email. I wanted to make sure the same style and font carries over when we use the form in the db.

[June7]

Take baby steps. Do a test. Code an email and send it to yourself.

Google: vba Access send email html body

You might get more hits.

[tobydobo]

yea already testing....so far so good. So now I can use the combo box within the actual form to auto populate the email address, to, cc, subject and body fields....based on the data pulled from my table.

[orange]

Glad to hear things are progressing... sounds good.

[tobydobo]

Hey guys,

Got the email form up and running for the most part. Added a drop down which allows me to select the location which autopopulates the to, cc, and subject. However, for the body, there are certain elements of the email that will be different depending on the selection from the drop down. I tried editing the body field of my table with the entire email, but it says there were too many characters. So I had considered adding additional text fields that will contain sections of the body...parts that will always be the same. Is there a way to incorporate multiple text fields into the body? Or maybe a way to pull the text for the email from a text document?

I added a screen shot of my rough draft form (EDIT: Screenshot included in next message. Left it off by mistake). 'Text63, Text65, Text67, IMAC Code, Location, and Body are all things I want included in the body of the email...in the same order as they appear in the form. Text63 will will always be the same, so I just edited the default value of the field. Text 65 and 67 will partially be edited, so I set the default value of the field with the constant, then I add the rest at the end, specific to the email. The location text field will autopopulate based on the selection at the top of the form. The message body will contain the rest of the email but will have a default value as it will not have to be edited.

Here is the code from the demo that works with just one field for the body.

Private Sub Command20_Click()
Dim mess_body As String
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
.BodyFormat = olFormatRichText
.To = Me.To_Address
.CC = Me.CC_Address
.Subject = Me.Mess_Subject
.HTMLBody = Me.Mess_Text
If Left(Me.Mail_Attachment_Path, 1) <> "<" Then
.Attachments.Add (Me.Mail_Attachment_Path)
End If
'.DeleteAfterSubmit = True 'This would let Outlook send th note without storing it in your sent bin
.Send
End With
'MsgBox MailOutLook.Body
Exit Sub

...I tried adding the lines in bold to allow for the additional text fields to be included in the body. In theory it seems like this should work, but when I click send on the form all I get is a blank email. If I take the items in bold out, It works fine.


Private Sub Command20_Click()
Dim mess_body As String
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
.BodyFormat = olFormatRichText
.To = Me.To_Address
.CC = Me.CC_Address
.Subject = Me.Mess_Subject
.HTMLBody = Me.Text63
.HTMLBody = Me.Text65
.HTMLBody = Me.Text67
.HTMLBody = Me.Combo71
.HTMLBody = Me.Mess_Text
If Left(Me.Mail_Attachment_Path, 1) <> "<" Then
.Attachments.Add (Me.Mail_Attachment_Path)
End If
'.DeleteAfterSubmit = True 'This would let Outlook send th note without storing it in your sent bin
.Send
End With
'MsgBox MailOutLook.Body
Exit Sub