I have setup a button with an event procedure behind it.  I need to
edit the vba code so it will ask me which email address to use if
there is more than one email field filled.  The email fields to pick
from would be: serviceremail or serviceremail2


Here is the code I currently have that works for the first email only:

Private Sub WorkOrderMissing_Click()
On Error GoTo ErrorHandler

   Dim objOutlook As Object 'Outlook.Application
   Dim objMailItem As Object 'Outlook.MailItem
   Dim WaitingFor As String



   Set objOutlook = CreateObject("Outlook.Application")
   Set objMailItem = objOutlook.CreateItem(0)

       DoCmd.RunCommand acCmdSaveRecord
       WaitingFor = Me!serviceremail
       With objMailItem

           .To = WaitingFor
           .Subject = "Missing Work Order SO# " & [WorkOrder#]
           .body = "We have not received the signed work order for the
call referenced at the bottom of this message.  Please make sure you
have done one of the following:" & Chr(13) & "Scan and email the work
order to: completed@serviceenvoy.com" & Chr(13) & "or" & Chr(13) &
"Fax the signed work order to: 888-858-2846 or 817-394-4236" & Chr(13)
& Chr(13) & "No other email addresses or fax numbers are valid closing
methods." & Chr(13) & "If you think you have already sent the work
order in via one of the above methods, please resend it.  I have just
looked in my database and it wasn't received. Please resend the work
order and feel free to call or email to make sure we receive it this
time." & Chr(13) & "Please remember, we must receive the signed work
order before we can close the call and pay you." & Chr(13) & Chr(13) &
[CallData] & Chr(13) & Chr(13) & "Michael Firey" & Chr(13) & "Service
Envoy, LLC" & Chr(13) & "918-398-4236" & Chr(13) &
"completed@serviceenvoy.com" & Chr(13) & "www.serviceenvoy.com"
           .display
       End With

Exit Sub
ErrorHandler:
MsgBox "Error Number: " & Err.Number & " " & Err.Description
End Sub

It all starts with the data.  We aren't there -- we can't see your data 
structure.

How is it that you can have "more than one email field filled"?  Does that 
mean your table has multiple (repeating) fields for the same kind of data 
(e.g., email address)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"ServiceEnvoy" <serviceenvoy@gmail.com> wrote in message 
news:1191012606.859863.72600@n39g2000hsh.googlegroups.com...
>I have setup a button with an event procedure behind it.  I need to
> edit the vba code so it will ask me which email address to use if
> there is more than one email field filled.  The email fields to pick
> from would be: serviceremail or serviceremail2
>
>
> Here is the code I currently have that works for the first email only:
>
> Private Sub WorkOrderMissing_Click()
> On Error GoTo ErrorHandler
>
>   Dim objOutlook As Object 'Outlook.Application
>   Dim objMailItem As Object 'Outlook.MailItem
>   Dim WaitingFor As String
>
>
>
>   Set objOutlook = CreateObject("Outlook.Application")
>   Set objMailItem = objOutlook.CreateItem(0)
>
>       DoCmd.RunCommand acCmdSaveRecord
>       WaitingFor = Me!serviceremail
>       With objMailItem
>
>           .To = WaitingFor
>           .Subject = "Missing Work Order SO# " & [WorkOrder#]
>           .body = "We have not received the signed work order for the
> call referenced at the bottom of this message.  Please make sure you
> have done one of the following:" & Chr(13) & "Scan and email the work
> order to: completed@serviceenvoy.com" & Chr(13) & "or" & Chr(13) &
> "Fax the signed work order to: 888-858-2846 or 817-394-4236" & Chr(13)
> & Chr(13) & "No other email addresses or fax numbers are valid closing
> methods." & Chr(13) & "If you think you have already sent the work
> order in via one of the above methods, please resend it.  I have just
> looked in my database and it wasn't received. Please resend the work
> order and feel free to call or email to make sure we receive it this
> time." & Chr(13) & "Please remember, we must receive the signed work
> order before we can close the call and pay you." & Chr(13) & Chr(13) &
> [CallData] & Chr(13) & Chr(13) & "Michael Firey" & Chr(13) & "Service
> Envoy, LLC" & Chr(13) & "918-398-4236" & Chr(13) &
> "completed@serviceenvoy.com" & Chr(13) & "www.serviceenvoy.com"
>           .display
>       End With
>
> Exit Sub
> ErrorHandler:
> MsgBox "Error Number: " & Err.Number & " " & Err.Description
> End Sub
> 

On Sep 28, 6:57 pm, "Jeff Boyce" <nonse...@nonsense.com> wrote:
> It all starts with the data.  We aren't there -- we can't see your data
> structure.
>
> How is it that you can have "more than one email field filled"?  Does that
> mean your table has multiple (repeating) fields for the same kind of data
> (e.g., email address)?
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
> "ServiceEnvoy" <serviceen...@gmail.com> wrote in message
>
> news:1191012606.859863.72600@n39g2000hsh.googlegroups.com...
>
> >I have setup a button with an event procedure behind it.  I need to
> > edit the vba code so it will ask me which email address to use if
> > there is more than one email field filled.  The email fields to pick
> > from would be: serviceremail or serviceremail2
>
> > Here is the code I currently have that works for the first email only:
>
> > Private Sub WorkOrderMissing_Click()
> > On Error GoTo ErrorHandler
>
> >   Dim objOutlook As Object 'Outlook.Application
> >   Dim objMailItem As Object 'Outlook.MailItem
> >   Dim WaitingFor As String
>
> >   Set objOutlook = CreateObject("Outlook.Application")
> >   Set objMailItem = objOutlook.CreateItem(0)
>
> >       DoCmd.RunCommand acCmdSaveRecord
> >       WaitingFor = Me!serviceremail
> >       With objMailItem
>
> >           .To = WaitingFor
> >           .Subject = "Missing Work Order SO# " & [WorkOrder#]
> >           .body = "We have not received the signed work order for the
> > call referenced at the bottom of this message.  Please make sure you
> > have done one of the following:" & Chr(13) & "Scan and email the work
> > order to: comple...@serviceenvoy.com" & Chr(13) & "or" & Chr(13) &
> > "Fax the signed work order to: 888-858-2846 or 817-394-4236" & Chr(13)
> > & Chr(13) & "No other email addresses or fax numbers are valid closing
> > methods." & Chr(13) & "If you think you have already sent the work
> > order in via one of the above methods, please resend it.  I have just
> > looked in my database and it wasn't received. Please resend the work
> > order and feel free to call or email to make sure we receive it this
> > time." & Chr(13) & "Please remember, we must receive the signed work
> > order before we can close the call and pay you." & Chr(13) & Chr(13) &
> > [CallData] & Chr(13) & Chr(13) & "Michael Firey" & Chr(13) & "Service
> > Envoy, LLC" & Chr(13) & "918-398-4236" & Chr(13) &
> > "comple...@serviceenvoy.com" & Chr(13) & "www.serviceenvoy.com"
> >           .display
> >       End With
>
> > Exit Sub
> > ErrorHandler:
> > MsgBox "Error Number: " & Err.Number & " " & Err.Description
> > End Sub

I have 3 different fields for "servicers" which can be assigned to a
ticket.  Each field is named progressively, servicer1, servicer2,
servicer3.  When we enter them, I have an after update option that
automatically loads their respective email address into the fields,
email1, email2, email3.

You will find that you (and Access) have to work much harder and come up 
with "work arounds" to deal with the data structure you've described.

A well-normalized relational database design would reflect the fact ("fact", 
in your situation) that there can be more than one email address associated 
with whatever entity.  Rather than pressing forward to develop a 
work-around, consider creating a table structure that makes better use of 
Access' relationally-oriented features and functions.  That is, create a 
"many" table that holds one record for each valid entity-email pair.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"ServiceEnvoy" <serviceenvoy@gmail.com> wrote in message 
news:1191250502.388699.115950@o80g2000hse.googlegroups.com...
> On Sep 28, 6:57 pm, "Jeff Boyce" <nonse...@nonsense.com> wrote:
>> It all starts with the data.  We aren't there -- we can't see your data
>> structure.
>>
>> How is it that you can have "more than one email field filled"?  Does 
>> that
>> mean your table has multiple (repeating) fields for the same kind of data
>> (e.g., email address)?
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>> "ServiceEnvoy" <serviceen...@gmail.com> wrote in message
>>
>> news:1191012606.859863.72600@n39g2000hsh.googlegroups.com...
>>
>> >I have setup a button with an event procedure behind it.  I need to
>> > edit the vba code so it will ask me which email address to use if
>> > there is more than one email field filled.  The email fields to pick
>> > from would be: serviceremail or serviceremail2
>>
>> > Here is the code I currently have that works for the first email only:
>>
>> > Private Sub WorkOrderMissing_Click()
>> > On Error GoTo ErrorHandler
>>
>> >   Dim objOutlook As Object 'Outlook.Application
>> >   Dim objMailItem As Object 'Outlook.MailItem
>> >   Dim WaitingFor As String
>>
>> >   Set objOutlook = CreateObject("Outlook.Application")
>> >   Set objMailItem = objOutlook.CreateItem(0)
>>
>> >       DoCmd.RunCommand acCmdSaveRecord
>> >       WaitingFor = Me!serviceremail
>> >       With objMailItem
>>
>> >           .To = WaitingFor
>> >           .Subject = "Missing Work Order SO# " & [WorkOrder#]
>> >           .body = "We have not received the signed work order for the
>> > call referenced at the bottom of this message.  Please make sure you
>> > have done one of the following:" & Chr(13) & "Scan and email the work
>> > order to: comple...@serviceenvoy.com" & Chr(13) & "or" & Chr(13) &
>> > "Fax the signed work order to: 888-858-2846 or 817-394-4236" & Chr(13)
>> > & Chr(13) & "No other email addresses or fax numbers are valid closing
>> > methods." & Chr(13) & "If you think you have already sent the work
>> > order in via one of the above methods, please resend it.  I have just
>> > looked in my database and it wasn't received. Please resend the work
>> > order and feel free to call or email to make sure we receive it this
>> > time." & Chr(13) & "Please remember, we must receive the signed work
>> > order before we can close the call and pay you." & Chr(13) & Chr(13) &
>> > [CallData] & Chr(13) & Chr(13) & "Michael Firey" & Chr(13) & "Service
>> > Envoy, LLC" & Chr(13) & "918-398-4236" & Chr(13) &
>> > "comple...@serviceenvoy.com" & Chr(13) & "www.serviceenvoy.com"
>> >           .display
>> >       End With
>>
>> > Exit Sub
>> > ErrorHandler:
>> > MsgBox "Error Number: " & Err.Number & " " & Err.Description
>> > End Sub
>
> I have 3 different fields for "servicers" which can be assigned to a
> ticket.  Each field is named progressively, servicer1, servicer2,
> servicer3.  When we enter them, I have an after update option that
> automatically loads their respective email address into the fields,
> email1, email2, email3.
> 

On Oct 1, 10:45 am, "Jeff Boyce" <nonse...@nonsense.com> wrote:
> You will find that you (and Access) have to work much harder and come up
> with "work arounds" to deal with the data structure you've described.
>
> A well-normalized relational database design would reflect the fact ("fact",
> in your situation) that there can be more than one email address associated
> with whatever entity.  Rather than pressing forward to develop a
> work-around, consider creating a table structure that makes better use of
> Access' relationally-oriented features and functions.  That is, create a
> "many" table that holds one record for each valid entity-email pair.
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
> "ServiceEnvoy" <serviceen...@gmail.com> wrote in message
>
> news:1191250502.388699.115950@o80g2000hse.googlegroups.com...
>
> > On Sep 28, 6:57 pm, "Jeff Boyce" <nonse...@nonsense.com> wrote:
> >> It all starts with the data.  We aren't there -- we can't see your data
> >> structure.
>
> >> How is it that you can have "more than one email field filled"?  Does
> >> that
> >> mean your table has multiple (repeating) fields for the same kind of data
> >> (e.g., email address)?
>
> >> Regards
>
> >> Jeff Boyce
> >> Microsoft Office/Access MVP
>
> >> "ServiceEnvoy" <serviceen...@gmail.com> wrote in message
>
> >>news:1191012606.859863.72600@n39g2000hsh.googlegroups.com...
>
> >> >I have setup a button with an event procedure behind it.  I need to
> >> > edit the vba code so it will ask me which email address to use if
> >> > there is more than one email field filled.  The email fields to pick
> >> > from would be: serviceremail or serviceremail2
>
> >> > Here is the code I currently have that works for the first email only:
>
> >> > Private Sub WorkOrderMissing_Click()
> >> > On Error GoTo ErrorHandler
>
> >> >   Dim objOutlook As Object 'Outlook.Application
> >> >   Dim objMailItem As Object 'Outlook.MailItem
> >> >   Dim WaitingFor As String
>
> >> >   Set objOutlook = CreateObject("Outlook.Application")
> >> >   Set objMailItem = objOutlook.CreateItem(0)
>
> >> >       DoCmd.RunCommand acCmdSaveRecord
> >> >       WaitingFor = Me!serviceremail
> >> >       With objMailItem
>
> >> >           .To = WaitingFor
> >> >           .Subject = "Missing Work Order SO# " & [WorkOrder#]
> >> >           .body = "We have not received the signed work order for the
> >> > call referenced at the bottom of this message.  Please make sure you
> >> > have done one of the following:" & Chr(13) & "Scan and email the work
> >> > order to: comple...@serviceenvoy.com" & Chr(13) & "or" & Chr(13) &
> >> > "Fax the signed work order to: 888-858-2846 or 817-394-4236" & Chr(13)
> >> > & Chr(13) & "No other email addresses or fax numbers are valid closing
> >> > methods." & Chr(13) & "If you think you have already sent the work
> >> > order in via one of the above methods, please resend it.  I have just
> >> > looked in my database and it wasn't received. Please resend the work
> >> > order and feel free to call or email to make sure we receive it this
> >> > time." & Chr(13) & "Please remember, we must receive the signed work
> >> > order before we can close the call and pay you." & Chr(13) & Chr(13) &
> >> > [CallData] & Chr(13) & Chr(13) & "Michael Firey" & Chr(13) & "Service
> >> > Envoy, LLC" & Chr(13) & "918-398-4236" & Chr(13) &
> >> > "comple...@serviceenvoy.com" & Chr(13) & "www.serviceenvoy.com"
> >> >           .display
> >> >       End With
>
> >> > Exit Sub
> >> > ErrorHandler:
> >> > MsgBox "Error Number: " & Err.Number & " " & Err.Description
> >> > End Sub
>
> > I have 3 different fields for "servicers" which can be assigned to a
> > ticket.  Each field is named progressively, servicer1, servicer2,
> > servicer3.  When we enter them, I have an after update option that
> > automatically loads their respective email address into the fields,
> > email1, email2, email3.

I've done my best to normalize in every place I can.  However, I don't
believe I can use a subform for this use.  If we were to use a
subform, we could not filter for tickets assigned to that servicer nor
could we setup double click and after update events to happen based on
those servicer and related email fields located in the subform.  Now,
if I'm ignorant of something here where these things can be done with
a subform then I'm a student ready to learn.  Please enlighten me.
Otherwise, I have to work with my data this way.

To make it as simple as possible, I set up the following related
fields:

servicer1ID servicer1name servicer1email
servicer2ID servicer2name servicer2email
servicer3id servicer3name servicer3email

This allows us to have up to 3 different servicer assigned to one
ticket and has served our needs to this point.  I have an auto update
that inserts the servicer name and email address based on the unique
id selected.  I have also setup the command button I was asking about
which will do what we want for servicer1.  I just didn't know if there
might be a way to make the button ask us which servicer email to apply
the command to.  If not, of course, I can make 3 sets of buttons for
the 3 servicers.

I'm willing to expand my knowledge and alter the way I'm doing things
but I'm a relative novice so whatever you suggest needs to come with
detailed explanations.

Oh, You can
The Parent Form is available through the Parent Property of the SubForm
ie

Private subEmail_Click()
  Me.Parent.Email.Value = Me.subEmail.Value
End Sub

HtH

Pieter

"ServiceEnvoy" <serviceenvoy@gmail.com> wrote in message 
news:1191299850.469075.166990@w3g2000hsg.googlegroups.com...
> On Oct 1, 10:45 am, "Jeff Boyce" <nonse...@nonsense.com> wrote:
>> You will find that you (and Access) have to work much harder and come up
>> with "work arounds" to deal with the data structure you've described.
>>
>> A well-normalized relational database design would reflect the fact 
>> ("fact",
>> in your situation) that there can be more than one email address 
>> associated
>> with whatever entity.  Rather than pressing forward to develop a
>> work-around, consider creating a table structure that makes better use of
>> Access' relationally-oriented features and functions.  That is, create a
>> "many" table that holds one record for each valid entity-email pair.
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>> "ServiceEnvoy" <serviceen...@gmail.com> wrote in message
>>
>> news:1191250502.388699.115950@o80g2000hse.googlegroups.com...
>>
>> > On Sep 28, 6:57 pm, "Jeff Boyce" <nonse...@nonsense.com> wrote:
>> >> It all starts with the data.  We aren't there -- we can't see your 
>> >> data
>> >> structure.
>>
>> >> How is it that you can have "more than one email field filled"?  Does
>> >> that
>> >> mean your table has multiple (repeating) fields for the same kind of 
>> >> data
>> >> (e.g., email address)?
>>
>> >> Regards
>>
>> >> Jeff Boyce
>> >> Microsoft Office/Access MVP
>>
>> >> "ServiceEnvoy" <serviceen...@gmail.com> wrote in message
>>
>> >>news:1191012606.859863.72600@n39g2000hsh.googlegroups.com...
>>
>> >> >I have setup a button with an event procedure behind it.  I need to
>> >> > edit the vba code so it will ask me which email address to use if
>> >> > there is more than one email field filled.  The email fields to pick
>> >> > from would be: serviceremail or serviceremail2
>>
>> >> > Here is the code I currently have that works for the first email 
>> >> > only:
>>
>> >> > Private Sub WorkOrderMissing_Click()
>> >> > On Error GoTo ErrorHandler
>>
>> >> >   Dim objOutlook As Object 'Outlook.Application
>> >> >   Dim objMailItem As Object 'Outlook.MailItem
>> >> >   Dim WaitingFor As String
>>
>> >> >   Set objOutlook = CreateObject("Outlook.Application")
>> >> >   Set objMailItem = objOutlook.CreateItem(0)
>>
>> >> >       DoCmd.RunCommand acCmdSaveRecord
>> >> >       WaitingFor = Me!serviceremail
>> >> >       With objMailItem
>>
>> >> >           .To = WaitingFor
>> >> >           .Subject = "Missing Work Order SO# " & [WorkOrder#]
>> >> >           .body = "We have not received the signed work order for 
>> >> > the
>> >> > call referenced at the bottom of this message.  Please make sure you
>> >> > have done one of the following:" & Chr(13) & "Scan and email the 
>> >> > work
>> >> > order to: comple...@serviceenvoy.com" & Chr(13) & "or" & Chr(13) &
>> >> > "Fax the signed work order to: 888-858-2846 or 817-394-4236" & 
>> >> > Chr(13)
>> >> > & Chr(13) & "No other email addresses or fax numbers are valid 
>> >> > closing
>> >> > methods." & Chr(13) & "If you think you have already sent the work
>> >> > order in via one of the above methods, please resend it.  I have 
>> >> > just
>> >> > looked in my database and it wasn't received. Please resend the work
>> >> > order and feel free to call or email to make sure we receive it this
>> >> > time." & Chr(13) & "Please remember, we must receive the signed work
>> >> > order before we can close the call and pay you." & Chr(13) & Chr(13) 
>> >> > &
>> >> > [CallData] & Chr(13) & Chr(13) & "Michael Firey" & Chr(13) & 
>> >> > "Service
>> >> > Envoy, LLC" & Chr(13) & "918-398-4236" & Chr(13) &
>> >> > "comple...@serviceenvoy.com" & Chr(13) & "www.serviceenvoy.com"
>> >> >           .display
>> >> >       End With
>>
>> >> > Exit Sub
>> >> > ErrorHandler:
>> >> > MsgBox "Error Number: " & Err.Number & " " & Err.Description
>> >> > End Sub
>>
>> > I have 3 different fields for "servicers" which can be assigned to a
>> > ticket.  Each field is named progressively, servicer1, servicer2,
>> > servicer3.  When we enter them, I have an after update option that
>> > automatically loads their respective email address into the fields,
>> > email1, email2, email3.
>
> I've done my best to normalize in every place I can.  However, I don't
> believe I can use a subform for this use.  If we were to use a
> subform, we could not filter for tickets assigned to that servicer nor
> could we setup double click and after update events to happen based on
> those servicer and related email fields located in the subform.  Now,
> if I'm ignorant of something here where these things can be done with
> a subform then I'm a student ready to learn.  Please enlighten me.
> Otherwise, I have to work with my data this way.
>
> To make it as simple as possible, I set up the following related
> fields:
>
> servicer1ID servicer1name servicer1email
> servicer2ID servicer2name servicer2email
> servicer3id servicer3name servicer3email
>
> This allows us to have up to 3 different servicer assigned to one
> ticket and has served our needs to this point.  I have an auto update
> that inserts the servicer name and email address based on the unique
> id selected.  I have also setup the command button I was asking about
> which will do what we want for servicer1.  I just didn't know if there
> might be a way to make the button ask us which servicer email to apply
> the command to.  If not, of course, I can make 3 sets of buttons for
> the 3 servicers.
>
> I'm willing to expand my knowledge and alter the way I'm doing things
> but I'm a relative novice so whatever you suggest needs to come with
> detailed explanations.
> 

On Oct 2, 3:46 am, "Pieter Wijnen"
<it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.ple...@online.replace.with.norway>
wrote:
> Oh, You can
> The Parent Form is available through the Parent Property of the SubForm
> ie
>
> Private subEmail_Click()
>   Me.Parent.Email.Value = Me.subEmail.Value
> End Sub
>
> HtH
>
> Pieter
>
> "ServiceEnvoy" <serviceen...@gmail.com> wrote in message
>
> news:1191299850.469075.166990@w3g2000hsg.googlegroups.com...
>
> > On Oct 1, 10:45 am, "Jeff Boyce" <nonse...@nonsense.com> wrote:
> >> You will find that you (and Access) have to work much harder and come up
> >> with "work arounds" to deal with the data structure you've described.
>
> >> A well-normalized relational database design would reflect the fact
> >> ("fact",
> >> in your situation) that there can be more than one email address
> >> associated
> >> with whatever entity.  Rather than pressing forward to develop a
> >> work-around, consider creating a table structure that makes better use of
> >> Access' relationally-oriented features and functions.  That is, create a
> >> "many" table that holds one record for each valid entity-email pair.
>
> >> Regards
>
> >> Jeff Boyce
> >> Microsoft Office/Access MVP
>
> >> "ServiceEnvoy" <serviceen...@gmail.com> wrote in message
>
> >>news:1191250502.388699.115950@o80g2000hse.googlegroups.com...
>
> >> > On Sep 28, 6:57 pm, "Jeff Boyce" <nonse...@nonsense.com> wrote:
> >> >> It all starts with the data.  We aren't there -- we can't see your
> >> >> data
> >> >> structure.
>
> >> >> How is it that you can have "more than one email field filled"?  Does
> >> >> that
> >> >> mean your table has multiple (repeating) fields for the same kind of
> >> >> data
> >> >> (e.g., email address)?
>
> >> >> Regards
>
> >> >> Jeff Boyce
> >> >> Microsoft Office/Access MVP
>
> >> >> "ServiceEnvoy" <serviceen...@gmail.com> wrote in message
>
> >> >>news:1191012606.859863.72600@n39g2000hsh.googlegroups.com...
>
> >> >> >I have setup a button with an event procedure behind it.  I need to
> >> >> > edit the vba code so it will ask me which email address to use if
> >> >> > there is more than one email field filled.  The email fields to pick
> >> >> > from would be: serviceremail or serviceremail2
>
> >> >> > Here is the code I currently have that works for the first email
> >> >> > only:
>
> >> >> > Private Sub WorkOrderMissing_Click()
> >> >> > On Error GoTo ErrorHandler
>
> >> >> >   Dim objOutlook As Object 'Outlook.Application
> >> >> >   Dim objMailItem As Object 'Outlook.MailItem
> >> >> >   Dim WaitingFor As String
>
> >> >> >   Set objOutlook = CreateObject("Outlook.Application")
> >> >> >   Set objMailItem = objOutlook.CreateItem(0)
>
> >> >> >       DoCmd.RunCommand acCmdSaveRecord
> >> >> >       WaitingFor = Me!serviceremail
> >> >> >       With objMailItem
>
> >> >> >           .To = WaitingFor
> >> >> >           .Subject = "Missing Work Order SO# " & [WorkOrder#]
> >> >> >           .body = "We have not received the signed work order for
> >> >> > the
> >> >> > call referenced at the bottom of this message.  Please make sure you
> >> >> > have done one of the following:" & Chr(13) & "Scan and email the
> >> >> > work
> >> >> > order to: comple...@serviceenvoy.com" & Chr(13) & "or" & Chr(13) &
> >> >> > "Fax the signed work order to: 888-858-2846 or 817-394-4236" &
> >> >> > Chr(13)
> >> >> > & Chr(13) & "No other email addresses or fax numbers are valid
> >> >> > closing
> >> >> > methods." & Chr(13) & "If you think you have already sent the work
> >> >> > order in via one of the above methods, please resend it.  I have
> >> >> > just
> >> >> > looked in my database and it wasn't received. Please resend the work
> >> >> > order and feel free to call or email to make sure we receive it this
> >> >> > time." & Chr(13) & "Please remember, we must receive the signed work
> >> >> > order before we can close the call and pay you." & Chr(13) & Chr(13)
> >> >> > &
> >> >> > [CallData] & Chr(13) & Chr(13) & "Michael Firey" & Chr(13) &
> >> >> > "Service
> >> >> > Envoy, LLC" & Chr(13) & "918-398-4236" & Chr(13) &
> >> >> > "comple...@serviceenvoy.com" & Chr(13) & "www.serviceenvoy.com"
> >> >> >           .display
> >> >> >       End With
>
> >> >> > Exit Sub
> >> >> > ErrorHandler:
> >> >> > MsgBox "Error Number: " & Err.Number & " " & Err.Description
> >> >> > End Sub
>
> >> > I have 3 different fields for "servicers" which can be assigned to a
> >> > ticket.  Each field is named progressively, servicer1, servicer2,
> >> > servicer3.  When we enter them, I have an after update option that
> >> > automatically loads their respective email address into the fields,
> >> > email1, email2, email3.
>
> > I've done my best to normalize in every place I can.  However, I don't
> > believe I can use a subform for this use.  If we were to use a
> > subform, we could not filter for tickets assigned to that servicer nor
> > could we setup double click and after update events to happen based on
> > those servicer and related email fields located in the subform.  Now,
> > if I'm ignorant of something here where these things can be done with
> > a subform then I'm a student ready to learn.  Please enlighten me.
> > Otherwise, I have to work with my data this way.
>
> > To make it as simple as possible, I set up the following related
> > fields:
>
> > servicer1ID servicer1name servicer1email
> > servicer2ID servicer2name servicer2email
> > servicer3id servicer3name servicer3email
>
> > This allows us to have up to 3 different servicer assigned to one
> > ticket and has served our needs to this point.  I have an auto update
> > that inserts the servicer name and email address based on the unique
> > id selected.  I have also setup the command button I was asking about
> > which will do what we want for servicer1.  I just didn't know if there
> > might be a way to make the button ask us which servicer email to apply
> > the command to.  If not, of course, I can make 3 sets of buttons for
> > the 3 servicers.
>
> > I'm willing to expand my knowledge and alter the way I'm doing things
> > but I'm a relative novice so whatever you suggest needs to come with
> > detailed explanations.

Ah.....that's useful to know.  Can you filter for things in a subform
across the entire table like you would a regular field on the main
form?