I've a workbook that needs to email the active sheet to multiple, user
entered, recipients using Lotus Notes. Thanks to lots of "borrowed" code
from here and other sites, I have the majority of the code done.

The bit I'm stuck on is how to collect the email address(es) from the user.
I've tried using an inputbox (which works if I only enter one address) and
I've tried hard coding the addresses using an array (which, although this
works, isn't the answer as the recipients will change from day to day).

What I think I need to do is combine the two.... but I have no idea how!

Please help

Jude

Try using a userform with a list box. You could collect the address using a
textbox and then add each one to the list box. The items in the list box
could be processed for one time or stored for later use.


"MuppetBaby" wrote:

I've a workbook that needs to email the active sheet to multiple, user
entered, recipients using Lotus Notes. Thanks to lots of "borrowed" code
from here and other sites, I have the majority of the code done.

The bit I'm stuck on is how to collect the email address(es) from the user.
I've tried using an inputbox (which works if I only enter one address) and
I've tried hard coding the addresses using an array (which, although this
works, isn't the answer as the recipients will change from day to day).

What I think I need to do is combine the two.... but I have no idea how!

Please help

Jude

JRForm, I should've added that I'm a little rusty with VBA, also that I've
been looking at this for 3 hours now so my brain hurts!

To save me a bit of time researching how to do what you suggest, can you
please give me a few pointers?

Thanks

Jude

"JRForm" wrote:

Try using a userform with a list box. You could collect the address using a
textbox and then add each one to the list box. The items in the list box
could be processed for one time or stored for later use.


"MuppetBaby" wrote:

I've a workbook that needs to email the active sheet to multiple, user
entered, recipients using Lotus Notes. Thanks to lots of "borrowed" code
from here and other sites, I have the majority of the code done.

The bit I'm stuck on is how to collect the email address(es) from the user.
I've tried using an inputbox (which works if I only enter one address) and
I've tried hard coding the addresses using an array (which, although this
works, isn't the answer as the recipients will change from day to day).

What I think I need to do is combine the two.... but I have no idea how!

Please help

Jude

Okay try this out.

Assuming you can create a userform, add two text boxes and a command
button to the form. Place the code below in the code module behind the form.
Make sure you name the text boxes like this txtEmail & txtMailbox
txtEmail is the text box to enter the addresses and the txtMailbox is
collecting them when the <Add button is clicked.


Private Sub cmdAdd_Click()
LoadUpText
End Sub


Function LoadUpText()

If Not IsNull(Me.txtEmail) Then
If Len(Me.txtMailbox) = 0 Then
Me.txtMailbox = Me.txtEmail '& ";"
Me.txtEmail = ""
Else
Me.txtMailbox = Me.txtMailbox & ";" & Me.txtEmail
Me.txtEmail = ""
End If
Exit Function
End If

"MuppetBaby" wrote:

JRForm, I should've added that I'm a little rusty with VBA, also that I've
been looking at this for 3 hours now so my brain hurts!

To save me a bit of time researching how to do what you suggest, can you
please give me a few pointers?

Thanks

Jude

"JRForm" wrote:

Try using a userform with a list box. You could collect the address using a
textbox and then add each one to the list box. The items in the list box
could be processed for one time or stored for later use.


"MuppetBaby" wrote:

I've a workbook that needs to email the active sheet to multiple, user
entered, recipients using Lotus Notes. Thanks to lots of "borrowed" code
from here and other sites, I have the majority of the code done.

The bit I'm stuck on is how to collect the email address(es) from the user.
I've tried using an inputbox (which works if I only enter one address) and
I've tried hard coding the addresses using an array (which, although this
works, isn't the answer as the recipients will change from day to day).

What I think I need to do is combine the two.... but I have no idea how!

Please help

Jude

JRForm,

Thanks, I have the form working but can't figure out why trying to use the
Lotus Notes user name (employee ID number), rather than an email address is
causing it to fail. I have successfully sent an email using the full email
address but should be able to send it using the person's ID number.

Jude

"JRForm" wrote:

Okay try this out.

Assuming you can create a userform, add two text boxes and a command
button to the form. Place the code below in the code module behind the form.
Make sure you name the text boxes like this txtEmail & txtMailbox
txtEmail is the text box to enter the addresses and the txtMailbox is
collecting them when the <Add button is clicked.


Private Sub cmdAdd_Click()
LoadUpText
End Sub


Function LoadUpText()

If Not IsNull(Me.txtEmail) Then
If Len(Me.txtMailbox) = 0 Then
Me.txtMailbox = Me.txtEmail '& ";"
Me.txtEmail = ""
Else
Me.txtMailbox = Me.txtMailbox & ";" & Me.txtEmail
Me.txtEmail = ""
End If
Exit Function
End If

"MuppetBaby" wrote:

JRForm, I should've added that I'm a little rusty with VBA, also that I've
been looking at this for 3 hours now so my brain hurts!

To save me a bit of time researching how to do what you suggest, can you
please give me a few pointers?

Thanks

Jude

"JRForm" wrote:

Try using a userform with a list box. You could collect the address using a
textbox and then add each one to the list box. The items in the list box
could be processed for one time or stored for later use.


"MuppetBaby" wrote:

I've a workbook that needs to email the active sheet to multiple, user
entered, recipients using Lotus Notes. Thanks to lots of "borrowed" code
from here and other sites, I have the majority of the code done.

The bit I'm stuck on is how to collect the email address(es) from the user.
I've tried using an inputbox (which works if I only enter one address) and
I've tried hard coding the addresses using an array (which, although this
works, isn't the answer as the recipients will change from day to day).

What I think I need to do is combine the two.... but I have no idea how!

Please help

Jude

you might try changing the txtmailbox to a variant type. The quotation marks
around the names that kicks them out. You also may want to build a lookup in
your excel file to convert the id to the correct address.

"MuppetBaby" wrote:

JRForm,

Thanks, I have the form working but can't figure out why trying to use the
Lotus Notes user name (employee ID number), rather than an email address is
causing it to fail. I have successfully sent an email using the full email
address but should be able to send it using the person's ID number.

Jude

"JRForm" wrote:

Okay try this out.

Assuming you can create a userform, add two text boxes and a command
button to the form. Place the code below in the code module behind the form.
Make sure you name the text boxes like this txtEmail & txtMailbox
txtEmail is the text box to enter the addresses and the txtMailbox is
collecting them when the <Add button is clicked.


Private Sub cmdAdd_Click()
LoadUpText
End Sub


Function LoadUpText()

If Not IsNull(Me.txtEmail) Then
If Len(Me.txtMailbox) = 0 Then
Me.txtMailbox = Me.txtEmail '& ";"
Me.txtEmail = ""
Else
Me.txtMailbox = Me.txtMailbox & ";" & Me.txtEmail
Me.txtEmail = ""
End If
Exit Function
End If

"MuppetBaby" wrote:

JRForm, I should've added that I'm a little rusty with VBA, also that I've
been looking at this for 3 hours now so my brain hurts!

To save me a bit of time researching how to do what you suggest, can you
please give me a few pointers?

Thanks

Jude

"JRForm" wrote:

Try using a userform with a list box. You could collect the address using a
textbox and then add each one to the list box. The items in the list box
could be processed for one time or stored for later use.


"MuppetBaby" wrote:

I've a workbook that needs to email the active sheet to multiple, user
entered, recipients using Lotus Notes. Thanks to lots of "borrowed" code
from here and other sites, I have the majority of the code done.

The bit I'm stuck on is how to collect the email address(es) from the user.
I've tried using an inputbox (which works if I only enter one address) and
I've tried hard coding the addresses using an array (which, although this
works, isn't the answer as the recipients will change from day to day).

What I think I need to do is combine the two.... but I have no idea how!

Please help

Jude

Did this solve your issue?

"MuppetBaby" wrote:

JRForm,

Thanks, I have the form working but can't figure out why trying to use the
Lotus Notes user name (employee ID number), rather than an email address is
causing it to fail. I have successfully sent an email using the full email
address but should be able to send it using the person's ID number.

Jude

"JRForm" wrote:

Okay try this out.

Assuming you can create a userform, add two text boxes and a command
button to the form. Place the code below in the code module behind the form.
Make sure you name the text boxes like this txtEmail & txtMailbox
txtEmail is the text box to enter the addresses and the txtMailbox is
collecting them when the <Add button is clicked.


Private Sub cmdAdd_Click()
LoadUpText
End Sub


Function LoadUpText()

If Not IsNull(Me.txtEmail) Then
If Len(Me.txtMailbox) = 0 Then
Me.txtMailbox = Me.txtEmail '& ";"
Me.txtEmail = ""
Else
Me.txtMailbox = Me.txtMailbox & ";" & Me.txtEmail
Me.txtEmail = ""
End If
Exit Function
End If

"MuppetBaby" wrote:

JRForm, I should've added that I'm a little rusty with VBA, also that I've
been looking at this for 3 hours now so my brain hurts!

To save me a bit of time researching how to do what you suggest, can you
please give me a few pointers?

Thanks

Jude

"JRForm" wrote:

Try using a userform with a list box. You could collect the address using a
textbox and then add each one to the list box. The items in the list box
could be processed for one time or stored for later use.


"MuppetBaby" wrote:

I've a workbook that needs to email the active sheet to multiple, user
entered, recipients using Lotus Notes. Thanks to lots of "borrowed" code
from here and other sites, I have the majority of the code done.

The bit I'm stuck on is how to collect the email address(es) from the user.
I've tried using an inputbox (which works if I only enter one address) and
I've tried hard coding the addresses using an array (which, although this
works, isn't the answer as the recipients will change from day to day).

What I think I need to do is combine the two.... but I have no idea how!

Please help

Jude

Apologies for the delayed response, this worked perfectly at home.... in the
office was a different story!

What I ended up doing was using an array, input box and message box. Using
the input box I asked the user to enter the ID number of the person they
wanted to email, this was then stored in array(0). Using a message box I
asked if they wanted to email anyone else, yes incremented the array by one
and re-ran the input box, no ended the loop.

"JRForm" wrote:

Did this solve your issue?

"MuppetBaby" wrote:

JRForm,

Thanks, I have the form working but can't figure out why trying to use the
Lotus Notes user name (employee ID number), rather than an email address is
causing it to fail. I have successfully sent an email using the full email
address but should be able to send it using the person's ID number.

Jude

"JRForm" wrote:

Okay try this out.

Assuming you can create a userform, add two text boxes and a command
button to the form. Place the code below in the code module behind the form.
Make sure you name the text boxes like this txtEmail & txtMailbox
txtEmail is the text box to enter the addresses and the txtMailbox is
collecting them when the <Add button is clicked.


Private Sub cmdAdd_Click()
LoadUpText
End Sub


Function LoadUpText()

If Not IsNull(Me.txtEmail) Then
If Len(Me.txtMailbox) = 0 Then
Me.txtMailbox = Me.txtEmail '& ";"
Me.txtEmail = ""
Else
Me.txtMailbox = Me.txtMailbox & ";" & Me.txtEmail
Me.txtEmail = ""
End If
Exit Function
End If

"MuppetBaby" wrote:

JRForm, I should've added that I'm a little rusty with VBA, also that I've
been looking at this for 3 hours now so my brain hurts!

To save me a bit of time researching how to do what you suggest, can you
please give me a few pointers?

Thanks

Jude

"JRForm" wrote:

Try using a userform with a list box. You could collect the address using a
textbox and then add each one to the list box. The items in the list box
could be processed for one time or stored for later use.


"MuppetBaby" wrote:

I've a workbook that needs to email the active sheet to multiple, user
entered, recipients using Lotus Notes. Thanks to lots of "borrowed" code
from here and other sites, I have the majority of the code done.

The bit I'm stuck on is how to collect the email address(es) from the user.
I've tried using an inputbox (which works if I only enter one address) and
I've tried hard coding the addresses using an array (which, although this
works, isn't the answer as the recipients will change from day to day).

What I think I need to do is combine the two.... but I have no idea how!

Please help

Jude

Great! The problem is solved then?

"MuppetBaby" wrote:

Apologies for the delayed response, this worked perfectly at home.... in the
office was a different story!

What I ended up doing was using an array, input box and message box. Using
the input box I asked the user to enter the ID number of the person they
wanted to email, this was then stored in array(0). Using a message box I
asked if they wanted to email anyone else, yes incremented the array by one
and re-ran the input box, no ended the loop.

"JRForm" wrote:

Did this solve your issue?

"MuppetBaby" wrote:

JRForm,

Thanks, I have the form working but can't figure out why trying to use the
Lotus Notes user name (employee ID number), rather than an email address is
causing it to fail. I have successfully sent an email using the full email
address but should be able to send it using the person's ID number.

Jude

"JRForm" wrote:

Okay try this out.

Assuming you can create a userform, add two text boxes and a command
button to the form. Place the code below in the code module behind the form.
Make sure you name the text boxes like this txtEmail & txtMailbox
txtEmail is the text box to enter the addresses and the txtMailbox is
collecting them when the <Add button is clicked.


Private Sub cmdAdd_Click()
LoadUpText
End Sub


Function LoadUpText()

If Not IsNull(Me.txtEmail) Then
If Len(Me.txtMailbox) = 0 Then
Me.txtMailbox = Me.txtEmail '& ";"
Me.txtEmail = ""
Else
Me.txtMailbox = Me.txtMailbox & ";" & Me.txtEmail
Me.txtEmail = ""
End If
Exit Function
End If

"MuppetBaby" wrote:

JRForm, I should've added that I'm a little rusty with VBA, also that I've
been looking at this for 3 hours now so my brain hurts!

To save me a bit of time researching how to do what you suggest, can you
please give me a few pointers?

Thanks

Jude

"JRForm" wrote:

Try using a userform with a list box. You could collect the address using a
textbox and then add each one to the list box. The items in the list box
could be processed for one time or stored for later use.


"MuppetBaby" wrote:

I've a workbook that needs to email the active sheet to multiple, user
entered, recipients using Lotus Notes. Thanks to lots of "borrowed" code
from here and other sites, I have the majority of the code done.

The bit I'm stuck on is how to collect the email address(es) from the user.
I've tried using an inputbox (which works if I only enter one address) and
I've tried hard coding the addresses using an array (which, although this
works, isn't the answer as the recipients will change from day to day).

What I think I need to do is combine the two.... but I have no idea how!

Please help

Jude

Till the next one, yes!!

Thanks again.

"JRForm" wrote:

Great! The problem is solved then?

"MuppetBaby" wrote:

Apologies for the delayed response, this worked perfectly at home.... in the
office was a different story!

What I ended up doing was using an array, input box and message box. Using
the input box I asked the user to enter the ID number of the person they
wanted to email, this was then stored in array(0). Using a message box I
asked if they wanted to email anyone else, yes incremented the array by one
and re-ran the input box, no ended the loop.

"JRForm" wrote:

Did this solve your issue?

"MuppetBaby" wrote:

JRForm,

Thanks, I have the form working but can't figure out why trying to use the
Lotus Notes user name (employee ID number), rather than an email address is
causing it to fail. I have successfully sent an email using the full email
address but should be able to send it using the person's ID number.

Jude

"JRForm" wrote:

Okay try this out.

Assuming you can create a userform, add two text boxes and a command
button to the form. Place the code below in the code module behind the form.
Make sure you name the text boxes like this txtEmail & txtMailbox
txtEmail is the text box to enter the addresses and the txtMailbox is
collecting them when the <Add button is clicked.


Private Sub cmdAdd_Click()
LoadUpText
End Sub


Function LoadUpText()

If Not IsNull(Me.txtEmail) Then
If Len(Me.txtMailbox) = 0 Then
Me.txtMailbox = Me.txtEmail '& ";"
Me.txtEmail = ""
Else
Me.txtMailbox = Me.txtMailbox & ";" & Me.txtEmail
Me.txtEmail = ""
End If
Exit Function
End If

"MuppetBaby" wrote:

JRForm, I should've added that I'm a little rusty with VBA, also that I've
been looking at this for 3 hours now so my brain hurts!

To save me a bit of time researching how to do what you suggest, can you
please give me a few pointers?

Thanks

Jude

"JRForm" wrote:

Try using a userform with a list box. You could collect the address using a
textbox and then add each one to the list box. The items in the list box
could be processed for one time or stored for later use.


"MuppetBaby" wrote:

I've a workbook that needs to email the active sheet to multiple, user
entered, recipients using Lotus Notes. Thanks to lots of "borrowed" code
from here and other sites, I have the majority of the code done.

The bit I'm stuck on is how to collect the email address(es) from the user.
I've tried using an inputbox (which works if I only enter one address) and
I've tried hard coding the addresses using an array (which, although this
works, isn't the answer as the recipients will change from day to day).

What I think I need to do is combine the two.... but I have no idea how!

Please help

Jude