VBA and form/query help sought

J

Jay

I have a form based on a query, with the text box txtEmailAddress
displaying the field EmailAddress from the source query. This textbox
(along with others) is then used to create an email in Outlook using:

Private Sub cmdOpenOutlook_Click()
On Error GoTo Error_Handler

Dim objOutlook As Outlook.MailItem

Set objOutlook = CreateObject("Outlook.Application")
Set objEmail = objOutlook.CreateItem(olMailItem)

With objEmail
.BodyFormat = olFormatRichText
.To = Me.txtEmailAddress
.CC = Me.txtCCEmailAddress
.Subject = Met.txtEmailSubject
.HTML = txt.MessageText
.Display
End With


That's not the whole procedure but you can see how the text boxes are
used to populate the relevant parts of the email.

My question is - How can I add a second email address to the .To ?
Also, the second email address I want to add is from a different query
to the one on which the form is based. I need to add
[qrySecondaryEmail].SecondaryEmailAddress. I can't figure out how to
display this field on the form or how to add it to the To.

ANY help greatly appreciated . . . . thanks, Jason
 
G

Graham Mandeno

Hi Jay

The recipient list properties (.To, .CC and .BCC) can, I think, be a
semicolon delimited list. So you could say:
.To = strEmail1 & ";" & strEmail2

A more "correct" way of doing it is to add members to the Recipients
collection:

Dim objRecip as Outlook.Recipient
Set objRecip = objEmail.Recipients.Add(strEmail1)
objRecip.Type = olTo
Set objRecip = objEmail.Recipients.Add(strEmail2)
objRecip.Type = olTo
Set objRecip = objEmail.Recipients.Add(strEmail3)
objRecip.Type = olCC
objEmail.Recipients.ResolveAll
 
J

Jay

Graham said:
Hi Jay

The recipient list properties (.To, .CC and .BCC) can, I think, be a
semicolon delimited list. So you could say:
.To = strEmail1 & ";" & strEmail2

A more "correct" way of doing it is to add members to the Recipients
collection:

Dim objRecip as Outlook.Recipient
Set objRecip = objEmail.Recipients.Add(strEmail1)
objRecip.Type = olTo
Set objRecip = objEmail.Recipients.Add(strEmail2)
objRecip.Type = olTo
Set objRecip = objEmail.Recipients.Add(strEmail3)
objRecip.Type = olCC
objEmail.Recipients.ResolveAll
Click to expand...
Many thanks Graham, I'll give it a go and let you know. I'm a real VBA
novice so may possiblygo wrong. Can you explain the use of strEMail
when my original code is txtEMail? (I'm not even sure what str
represents so have some googling to do).

Cheers,

Jason
 
G

Graham Mandeno

Hi Jay

I was just referring to an arbitrary string variable. I guess your txtEmail
is the name of a textbox on your form. Both will work (provided of course
that the contents are a valid email address!)
 
J

Jay

Graham said:
Hi Jay

I was just referring to an arbitrary string variable. I guess your txtEmail
is the name of a textbox on your form. Both will work (provided of course
that the contents are a valid email address!)
Click to expand...

Thanks Graham,

I'll be trying your suggestion out at work tomorrow.


Regards
 
J

Jay

Hi Graham,

I'm wanting to try your delimited suggestion, so am wanting to add another
text box to my form to display the second email address. However, my form
is based on a different query to the one containing the email adress field I
need. So it isn't in the field list.

How do I display a field from a different query in my text box? I've tried
just adding the box's control source, then selecting the relevant query &
field via expression builder but this returns a NAME error.

Any help greatly appreciated.

Regards

Jason



Graham Mandeno said:
Hi Jay

I was just referring to an arbitrary string variable. I guess your
txtEmail is the name of a textbox on your form. Both will work (provided
of course that the contents are a valid email address!)
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Jay said:
Many thanks Graham, I'll give it a go and let you know. I'm a real VBA
novice so may possiblygo wrong. Can you explain the use of strEMail when
my original code is txtEMail? (I'm not even sure what str represents so
have some googling to do).

Cheers,

Jason
Click to expand...
Click to expand...
 
G

Graham Mandeno

Hi Jay

I need to know a bit more about your table structures and relationships to
know how to properly advise you.

Are you saying that the primary email address is in one table (bound to your
form) but the secondary address is in another? If so, what is the link
between the two tables? You should be able to create a query that contains
both tables (joined) and base your query on that.

Of course, to do the job properly you need a correctly normalised second
table with a one-to-many relationship. Who is to say that you won't want to
store a third email address in the future? Or a 4th or 5th one?

Then your form could have a small scrollable subform of related email
addresses.

Let me know which way you decide to jump.
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Jay said:
Hi Graham,

I'm wanting to try your delimited suggestion, so am wanting to add another
text box to my form to display the second email address. However, my form
is based on a different query to the one containing the email adress field
I need. So it isn't in the field list.

How do I display a field from a different query in my text box? I've
tried just adding the box's control source, then selecting the relevant
query & field via expression builder but this returns a NAME error.

Any help greatly appreciated.

Regards

Jason



Graham Mandeno said:
Hi Jay

I was just referring to an arbitrary string variable. I guess your
txtEmail is the name of a textbox on your form. Both will work (provided
of course that the contents are a valid email address!)
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Jay said:
Graham Mandeno wrote:
Hi Jay

The recipient list properties (.To, .CC and .BCC) can, I think, be a
semicolon delimited list. So you could say:
.To = strEmail1 & ";" & strEmail2

A more "correct" way of doing it is to add members to the Recipients
collection:

Dim objRecip as Outlook.Recipient
Set objRecip = objEmail.Recipients.Add(strEmail1)
objRecip.Type = olTo
Set objRecip = objEmail.Recipients.Add(strEmail2)
objRecip.Type = olTo
Set objRecip = objEmail.Recipients.Add(strEmail3)
objRecip.Type = olCC
objEmail.Recipients.ResolveAll

Many thanks Graham, I'll give it a go and let you know. I'm a real VBA
novice so may possiblygo wrong. Can you explain the use of strEMail
when my original code is txtEMail? (I'm not even sure what str
represents so have some googling to do).

Cheers,

Jason
Click to expand...
Click to expand...
Click to expand...
 
J

Jay

Hi Graham,

Your first sentence gave me the key to (partly) solve this. I simply
revisited my original query (the one on which the form is based) and
managed to get the secondary address into this.

SO now I've got both email addresses in the one query so can easily add
the address to the form & try your delimited auggestion tomorrow and try
get this to dump into the Outlook 'To' address. I can't try at home as
don't use Outlook.

Many thanks for the help. I'll let you know how I get on.

Regards

Jason
 
Top