Send e-mail to multiple recipients RRS feed

  • Question

  • Hi,

    I've downloaded the follwoing code from Internet. When I try to have in my cell 2 e-mails :

    ub Send_Files()
    'Working in Excel 2000-2013
    'For Tips see:
        Dim OutApp As Object
        Dim OutMail As Object
        Dim sh As Worksheet
        Dim cell As Range
        Dim FileCell As Range
        Dim rng As Range
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
        Set sh = Sheets("Sheet1")
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItemFromTemplate("U:\Mail.oft")
        For Each cell In sh.Columns("A").Cells.SpecialCells(xlCellTypeConstants)
            'Enter the path/file names in the C:Z column in each row
            Set rng = sh.Cells(cell.Row, 1).Range("B1:Z1")
            If cell.Value Like "?*@?*.?*" And _
               Application.WorksheetFunction.CountA(rng) > 0 Then
                'Set OutMail = OutApp.CreateItem(0)
                Set OutMail = OutApp.CreateItemFromTemplate("U:\Mail.oft")
                With OutMail
                    '.SentOnBehalfOfName = ThisWorkbook.Sheets("Sheet2").Range("E2").Value
                    .to = cell.Value
                    .CC = ""
                    .BCC = ""
                    .Subject = "Subject"
                    'Body = ThisWorkbook.Sheets("Sheet1").Range("D2").Value
                    For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
                        If Trim(FileCell) <> "" Then
                            If Dir(FileCell.Value) <> "" Then
                                .Attachments.Add FileCell.Value
                            End If
                        End If
                    Next FileCell
                    .send  'Or use Send
                End With
                Set OutMail = Nothing
            End If
        Next cell
        Set OutApp = Nothing
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End Sub

    " ;" I have the following message :

    Run-Time error '-2147467259 (80004005)':

    Outlook does not recognize one or more names.

    But, when I use 2 of my collegues e-mail (thos insight the outlook organization) it works well.

    I can't add people into my organisation due to acess.

    I was wondering if I could force the e-mail sending into the VBA beacause when I display it and click on send, I do not have any errror message.

    Thank you for your help,


    • Edited by Martin-01 Thursday, August 13, 2015 11:59 AM
    Thursday, August 13, 2015 11:58 AM

All replies

  • As far as emailing I use a product from Codestone.

    You need the SSL Edition.  I use it with both Gmail and

    It is simple to use and cheap.  The other techniques were complicated and didn't work consistently.  Plenty of VBA examples.   I have nothing to do with this company.

    Thursday, August 13, 2015 1:05 PM
  • Unfortunatly I can instal anything on my computer. I'm not the admin and I have to do it so.

    My solution work except this littel detail.

    When I display the e-mail I can click on send. The solution will be to display all with 2 e-mails and click one by one.

    Would be better is I founded a solution...

    Thursday, August 13, 2015 1:58 PM