Sending Emails from Excel VBA - Names Not Recognized
excel, outlook, vba
Solution
You cannot pass multiple names to Recipients.Add - you get a single recipient with the name of "name@domain.com; name2@domain.com; name3@domain.com". Either call Recipients.Add 3 times once for each recipient or set the To property - it will parse multiple names.
Problem
I am using the below code to send an email from excel using outlook: ``` Private Sub SendEmail() Set OutlookApp = CreateObject("Outlook.Application") Set OlObjects = OutlookApp.GetNamespace("MAPI") Set newmsg = OutlookApp.CreateItem(olMailItem) newmsg.Recipients.Add ("name@domain.com; name2@domain.com; name3@domain.com") newmsg.Subject = "Test Mail" newmsg.Body = "This is a test email." 'newmsg.Display newmsg.Send End Sub ``` The code works just fine, however I get the below error from Outlook when trying to send the email: ErrorScreen http://im58.gulfup.com/GRENlB.png The strange thing is that if I leave the new message open for two or three minutes the names automatically get resolved: Working http://im74.gulfup.com/qmOYGQ.png However this doesn't suit me as I don't want the message to be displayed before it's sent. I am looking to have it send as soon as I run the code. Any suggestions or workarounds will be appreciated. As a side note: I have tried enabling the "Allow commas as email separators" option in outlook, and then using the commas instead of the semicolons, but I am still facing the same problem. UPDATE: Below is the working code, as per Dmitry Streblechenko's answer: ``` Private Sub SendEmail() Dim OutApp As Object Dim OutMail As Object Dim strbody As String Set OutApp = CreateObject("Outlook.Application") Set OlObjects = OutApp.GetNamespace("MAPI") Set OutMail = OutApp.CreateItem(olMailItem) On Error Resume Next With OutMail .To = ("name@domain.com; name2@domain.com; name3@domain.com") .Subject = "Test Mail" .Body = "This is a test email." '.Display .Send End With End Sub ```