Link to home
Start Free TrialLog in
Avatar of sai sai
sai sai

asked on

How to send the same mail to multiple recipients at a time from outlook vba

This is the code:

It works fine if it is 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")



     ' subject = Cells(iRow, 2).Value
     ' Atmt = Cells(iRow, 3).Value '

'   // 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

    '// CleanUp


      iRow = iRow + 1

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



End Sub

Open in new window


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:

yhui@gmail.com
abc@gmail.com
ikj@gmail.com
plo@gmail.com

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

So To field should be containing: yhui@gmail.com;abc@gmail.com;ikj@gmail.com;plo@gmail.com

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

Thanks in advance
ASKER CERTIFIED SOLUTION
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sai sai
sai sai

ASKER

It works like a charm, thanks!!
Do you know how to add a signature at the end of the mail in outlook vba?
thanks. for signature, what do you mean a bunch of text or something  or a file that you have saved on your pc that is already preset in outlook and contain the signature ?

In any case it is doable please ask a related question and let me know of the link I would gladly assist.
gowflow
Avatar of sai sai

ASKER

@ gowflow
 I just wanted to insert Regards and thanks signature at the end of the body in the mail.
And I was able to do it.
Thanks again
ok great this is simple. But if you want with HTML then it is a bit more complicated.
gowflow