Refer 'To' field in Outlook to cell

Question

I'm trying to create a macro on Excel VBA that will create an email and will populate the To field from the Excel cell K6.

When this code runs, I get the error message Run-time error'5': Invalid procedure call or argument.

Dim OutApp As Object
Dim MItem As Object
Dim cell As Range
Dim rng As Range
Dim Subj As String
Dim EmailAddr As String
Dim myRecipient As Object
Dim myRecipients As Object
Dim Recipient As String
Dim Msg As String
Dim ws1 As Worksheet
Dim DateNow As Date

Set ws1 = Sheets("Email")

'Create Outlook object

Set rng = ws1.Range("B6:F26").SpecialCells(xlCellTypeVisible)

With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With


Set OutApp = CreateObject("Outlook.Application")
Set MItem = OutApp.CreateItem(0)
Set myRecipients = MItem.Recipients

myRecipients = ws1.Cells.Range("K6")

If Not myRecipients.ResolveAll Then

 For Each myRecipient In myRecipients

 If Not myRecipient.Resolved Then

 MsgBox myRecipient.Name

 End If

 Next

 End If

DateNow = Format(Now, "dd/MM/yyyy")
DateNow2 = Format(Now, "h:mm")

Msg = "This report was generated on " & DateNow & " at " & DateNow2 & "."

With MItem
    .CC = EmailAddr2
    .Subject = Subj
    .HTMLBody = RangetoHTML(rng) & Msg
    .Display
End With
On Error GoTo 0

With Application
    .EnableEvents = True
    .ScreenUpdating = False
End With

Set MItem = Nothing
Set OutApp = Nothing

End Sub

If I use Set myRecipients = ws1.Cells.Range("K6") I get the error message Run-time error '438': Object doesn't support this property or method.

If I set the myRecipients As String, it says Object required.

I'm having lots of problems to understand late-binding Outlook in Excel VBA and I've read lots of things, but haven't found many sources on it that could explain this in a more didactic way.

Besides that, I'm also trying to, after adding the content of the cell, to Resolve (the effect of using ctrl + K on Outlook to resolve the email to the display name) the emails added to the To field, but I can't test it without making the first part work.

Thanks for the attention,

Edit: after Bruce Wayne's suggestion, I put them as Range, but now I'm getting a different error: Run-time error '-2147352567 (800200009)': Property is read-only.

Dim myRecipient As Range Dim myRecipients As Range

In the middle of the code:

Set OutApp = CreateObject("Outlook.Application")
Set MItem = OutApp.CreateItem(0)

Set myRecipients = ws1.Cells.Range("K6")
Set MItem.Recipients = myRecipients

After Dmitry's suggestion:

Set OutApp = CreateObject("Outlook.Application")
Set MItem = OutApp.CreateItem(0)

Set myRecipients = ws1.Cells.Range("K6")
Set myRecipient = MItem.Recipients.Add(myRecipients)

If Not myRecipients.ResolveAll Then

 For Each myRecipient In myRecipients

 If Not myRecipient.Resolved Then

 MsgBox myRecipient.Name

 End If

 Next

 End If

But I get the error message: Run-time error '438': Object doesn't support this property or method marked on the If Not myRecipients.ResolveAll Then. If I delete all the If part, the code runs fine. But it's very important to me that I'm able to resolve the names and emails in the To/CC fields.

Answer 1

Recipients property is indeed read-only. You need to either call MailItem.Recipients.Add for each recipient or set the To / CC/ BCC properties to a ";" separated list of names or addresses.

UPDATE:

Set OutApp = CreateObject("Outlook.Application")
Set MItem = OutApp.CreateItem(0)
Set recipName = ws1.Cells.Range("K6").Value
Set myRecipient = MItem.Recipients.Add(recipName)
If Not myRecipient.Resolve Then
  MsgBox myRecipient.Name
End If

Answer 2

I think it's due to the fact that you're setting myRecipients and myRecipient as an Object but want to set it with what is essentially a Range type. Try:

Dim myRecipients as Range, myRecipient as Range
Dim objMyRecipients as Object, objMyRecipient as Object 'create a variable that holds the object

Then, when you need to use them as an object, you have a separate variable to do so.