Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sending Emails from Excel VBA - Names Not Recognized

Tags:

excel

vba

outlook

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 ("[email protected]; [email protected]; [email protected]")

  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 = ("[email protected]; [email protected]; [email protected]")
        .Subject = "Test Mail"
        .Body = "This is a test email."
        '.Display
        .Send
    End With

End Sub
like image 226
CaptainABC Avatar asked Dec 25 '22 07:12

CaptainABC


1 Answers

You cannot pass multiple names to Recipients.Add - you get a single recipient with the name of "[email protected]; [email protected]; [email protected]". Either call Recipients.Add 3 times once for each recipient or set the To property - it will parse multiple names.

like image 85
Dmitry Streblechenko Avatar answered Jan 09 '23 03:01

Dmitry Streblechenko