CODEWITHSUNDEEP
excelvbaoutlook
Sai
Sai

Reputation: 97

Retrieve multiple recipient names from Excel sheet

This is the code:

It works for one recipient.

Sub Sendmail()

    Dim olItem As outlook.MailItem
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSht As Excel.Worksheet
    Dim sPath As String
    Dim iRow As Long

    sPath = "***"
'   // Excel
    Set xlApp = CreateObject("Excel.Application")
'   // Workbook
    Set xlBook = xlApp.Workbooks.Open(sPath)
'   // Sheet
    Set xlSht = xlBook.Sheets("Sheet1")

'   // Create e-mail Item
    Set olItem = Application.CreateItem(olMailItem)

    With olItem
        .To = xlSht.Range("A1")
        .CC = xlSht.Range("c1")
        .subject = "test"
        .Display
        .Send
    End With

'   // Close
    xlBook.Close SaveChanges:=True
'   // Quit
    xlApp.Quit

    Set xlApp = Nothing
    Set xlBook = Nothing
    Set xlSht = Nothing
    Set olItem = Nothing

End Sub

Query: As per my requirement the recipients names should be taken from linked Excel sheet.

All the recipients mail address placed in column A of the Excel sheet.

And these values are dynamic, it might contain any number of mail Ids.

Example:

Column A:

[email protected] [email protected] [email protected] [email protected]

Here there are 4 values, now the mail should be sent to all these 4 recipients at the same time.

So the To field should contain: [email protected];[email protected];[email protected];[email protected]

The code should be able to traverse down column A till the last row and concatenate all the values separated by a semicolon (;)

Upvotes: 0

Views: 69

Answers (1)

A.S.H
A.S.H

Reputation: 29352

Try this for the recipients field:

.To = Join(xlApp.Transpose(xlSht.Range("A1", xlSht.Range("A9999").End(xlUp))), ";")

Upvotes: 1

Related Questions