sending email from excel via outlook, send list

[nmk34]

i have a workbook that i send email to recipients by typing their email address like this:
oMail.To = " name1@gmail.com ; name2@gmail.com; name3@gmail.com" and it works fine. now there is a need to populate the list of emails in shee1 of the workbook, for example column AM2 thru AM20. has all email addresses.
how can I use the sheet1 column with [ oMail.To= ]?

Thanks

 

[Trebor76]

Hi nmk34,

You could incorporate this into you existing code:

Option Explicit
Sub Macro1()

    Dim rngCell As Range
    Dim strTo As String
    
    For Each rngCell In Sheets("Sheet1").Range("AM2:AM20")
        strTo = IIf(Len(strTo) = 0, rngCell, strTo & ";" & rngCell)
    Next rngCell
    
    oMail.To = strTo

End Sub

Regards,

Robert

 

[nmk34]

Hi nmk34,

You could incorporate this into you existing code:

Option Explicit
Sub Macro1()

    Dim rngCell As Range
    Dim strTo As String
   
    For Each rngCell In Sheets("Sheet1").Range("AM2:AM20")
        strTo = IIf(Len(strTo) = 0, rngCell, strTo & ";" & rngCell)
    Next rngCell
   
    oMail.To = strTo

End Sub

Regards,

Robert

 

[nmk34]

Stro always =0. it will hang on .sendemail after the for loop. error message content, "we need to know to whome we should send this email, make sur you have at least one recipient"

 

[Trebor76]

Not sure as it worked for me

Try stepping through the code to see why it’s not picking up the distribution list from the given range.