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:
[vba]
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
[/vba]
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!!
Reply With Quote
