Excel VBA array of strings to Outlook email "To" field

Question

I am trying to create a macro that will take a column of email addresses from my Excel sheet and populate the "To" field in an Outlook email. I have the basics working, and I am able to create a new Outlook email message with the various field values I have specified - however, I cannot figure out how to populate multiple email addresses into the "To" field, for a single email.

As of right now, I am able to create an array with all of the desired email addresses, but can't figure out how to populate the array values into the Outlook "To" field.

show your current code

Tim Williams
– Tim Williams

2015-03-31 16:25:50 +00:00
Commented Mar 31, 2015 at 16:25 — Tim Williams
– Tim Williams, Commented Mar 31, 2015 at 16:25

Jeanno · Accepted Answer · 2015-03-31 16:34:15Z

1

This is based on Eugene's answer, edited to include the excel implementation

Sub CreateStatusReportToBoss(addRng as Excel.Range)  
  Dim myItem As Outlook.MailItem  
  Dim myRecipient As Outlook.Recipient  
  Set myItem = Application.CreateItem(olMailItem)
  For Each cell in addRng
     Set myRecipient = myItem.Recipients.Add(cell.Value)
  Next cell  
  myItem.Subject = "Status Report"  
  myItem.Display  
End Sub

answered Mar 31, 2015 at 16:34

Jeanno

2,8894 gold badges26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

User5590 Over a year ago

I have added Microsoft Outlook 15 reference, then also I am getting error "Object doesn't support type or method" in line no 3 (CreateItem).

Eugene Astafiev · Accepted Answer · 2015-03-31 16:30:03Z

1

You can use the Recipients property of the MailItem class for adding multiple recipients. It also allows to specify the type of the Recipient: To, CC or BCC.

Sub CreateStatusReportToBoss()  
  Dim myItem As Outlook.MailItem  
  Dim myRecipient As Outlook.Recipient  
  Set myItem = Application.CreateItem(olMailItem)  
  Set myRecipient = myItem.Recipients.Add("Dan Wilson")  
  myItem.Subject = "Status Report"  
  myItem.Display  
End Sub

answered Mar 31, 2015 at 16:30

Eugene Astafiev

49.8k3 gold badges28 silver badges50 bronze badges

Comments

Machavity · Accepted Answer · 2015-03-31 16:32:45Z

1

This might help but the concept is bit different as to the items. Hope this helps out- have used in the past for similar case but ofcourse, will only provide maproad.

Sub CreateMail()

Dim objOutlook As Object
Dim objMail As Object
Dim rngTo As Range
Dim rngCc As Range
Dim rngSubject As Range
Dim rngBody As Range

Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)

With ActiveSheet
    Set rngTo = .Range("B1")
    Set rngCc = .Range("B2")
    Set rngSubject = .Range("B3")
    Set rngBody = .Range(.Range("B4"), .Range("B4").End(xlDown))
End With
rngBody.Copy

With objMail
    .To = rngTo.Value
    .Cc = rngCc.Value
    .Subject = rngSubject.Value
    .Display
End With
SendKeys "^({v})", True

Set objOutlook = Nothing
Set objMail = Nothing
Set rngTo = Nothing
Set rngCc = Nothing
Set rngSubject = Nothing
Set rngBody = Nothing

End Sub

edited Mar 31, 2015 at 16:32

Machavity♦

31.8k27 gold badges97 silver badges108 bronze badges

answered Mar 31, 2015 at 16:26

Ina gurey

197 bronze badges

1 Comment

user1130306 Over a year ago

Thanks everyone. I was able to accomplish what I was trying to do with this "String Concentate" fuction I found here: cpearson.com/excel/stringconcatenation.aspx But your solutions are much for straightforward. Thanks!

Collectives™ on Stack Overflow

Excel VBA array of strings to Outlook email "To" field

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related