CODEWITHSUNDEEP
excelvba
Mariana Rola
Mariana Rola

Reputation: 5

How to reference a range of cells?

I want to send a picture from an Excel file, to a list of email addresses in the same file.

How do I reference the range?

Sub Email_picture()
    
    Dim ol As Outlook.Application
    Dim mi As Outlook.MailItem
    Dim doc As Word.Document
    
    Set ol = New Outlook.Application
    Set mi = ol.CreateItem(olMailItem)
    
    mi.Display
    mi.To = ThisWorkbook.Worksheets("Sheet2").Range("A1:A3")
    mi.Subject = "Picture"
    
    Set doc = mi.GetInspector.WordEditor
    
    doc.Range(0, 0).InlineShapes.AddPicture "C:\Users\msr\Documents\Slide2.jpg"
    
End Sub

It is the line mi.To = ThisWorkbook.Worksheets("Sheet2").Range("A1:A3").

Upvotes: 0

Views: 59

Answers (1)

Dmitry Streblechenko
Dmitry Streblechenko

Reputation: 66215

Range is an Excel specific COM object. Outlook would not know what to do with it. You need to loop through all cells explicitly and use their values to either construct a ";" separated list of addresses that you can assign to the To property, or you can use the value of each cell to call Recipients.Add to add one address at a time.

Upvotes: 1

Related Questions