PDA

View Full Version : Solved: SendMail using one cell containing recipeint names



feathers212
11-30-2006, 02:46 PM
I have a memo (setup as a worksheet) that is created using VBA forms (it acts as a template). One of the forms allows the user to enter in the names of people that he wants the memo to be sent to. The names are entered and separated using semi-colons (i.e. John Smith; Jane Doe). The names are then automatically entered into a single cell on the worksheet (?F12?). Once the user has completed all information, he presses a button that is supposed to automatically email the memo to the list of recipients noted in the cell. I have the button linked up to the following code:


Private Sub CommandButton1_Click()

Dim MaterialName As String
Dim SendNotificationTo As String

MaterialName = ActiveSheet.Range("F12")
SendNotificationTo = ActiveSheet.Range("F6")

ActiveSheet.Shapes("CommandButton1").Select
Selection.Delete
ActiveWorkbook.SaveAs MaterialName & " - Test Results Notification.xls"

ActiveWorkbook.SendMail Array(SendMailTo), MaterialName & " - Test Results Notification"
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False

End Sub


If there is only one name entered in the cell, the email is created perfectly in Outlook and is ready to be sent out (the email address is obtained by finding the entered name in the address book in Outlook). However, if I have multiple names entered in the cell, when I click the button and attempt to run the code, I get the following error:

Run-time error ?1004?:
Unknown recipient name found in the recipient list. Use a valid name and try again.


I need to be able to keep the memo format look and I need to keep the user input information to a minimum.

Please help!!

Zack Barresse
11-30-2006, 02:55 PM
Hi there, welcome to VBAX!!

Check out these two Knowledge Base entries:
http://vbaexpress.com/kb/getarticle.php?kb_id=97
http://vbaexpress.com/kb/getarticle.php?kb_id=326
http://vbaexpress.com/kb/getarticle.php?kb_id=311

HTH

feathers212
12-01-2006, 06:53 AM
The links provide some good codes. However, I am not looking to hard code email addresses into the program. I want things to be flexible so that the memo can go out to people that the user inputs into the single cell space. My user is not all that computer savy, so I want to make things as simple as possible for him.

Thanks again!

Charlize
12-01-2006, 07:28 AM
For article 97 change the to: in range("A2").value
When you need more adresses, put a ; in it (or ,). it depends on your configuration.
Maybe do a check on A2. If empty show box and exit sub.

Charlize

Zack Barresse
12-01-2006, 12:14 PM
What I'm saying is I do not think you can configure SendMail to do what you're asking. I would go with one of the procedures listed in the KB links I posted, as they are fully customizable.

HTH

feathers212
12-04-2006, 07:24 AM
Thanks Zack.

I worked with the code from article 326. I still assigned my recipients as before.
When setting up the email, I then just set
.To = SendNotificationTo
Thanks again!