Results 1 to 12 of 12

Multiple email recipients from excel

Threaded View

  1. #1
    Forum Contributor
    Join Date
    01-23-2010
    Location
    Suffolk, England
    MS-Off Ver
    Office 365
    Posts
    237

    Unhappy Multiple email recipients from excel

    Hi,

    I have obtained the following code which works great apart from the fact that I want to just send one email to all addressees instead of multiple emails. Is there someone who could help modify it please;

    Sub TestFile()
        Dim OutApp As Object
        Dim OutMail As Object
        Dim cell As Range
        Dim strbody As String
    
        Application.ScreenUpdating = False
        Set OutApp = CreateObject("Outlook.Application")
        OutApp.Session.Logon
    
        On Error GoTo cleanup
        For Each cell In Range("G1:G20")
            strbody = strbody & cell.Value & vbNewLine
        Next
        For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
            If cell.Value Like "?*@?*.?*" And _
               LCase(Cells(cell.Row, "C").Value) = "yes" Then
    
                Set OutMail = OutApp.CreateItem(0)
                On Error Resume Next
                With OutMail
                    .BCC = cell.Value
                    .Subject = "Reminder"
                    .Body = "Dear " & Cells(cell.Row, "A").Value & vbNewLine & vbNewLine & strbody
                    'You can add files also like this
                    '.Attachments.Add ("C:\test.txt")
                    .Display  'Or use Send
                End With
                On Error GoTo 0
                Set OutMail = Nothing
            End If
        Next cell
    
    cleanup:
        Set OutApp = Nothing
        Application.ScreenUpdating = True
    End Sub
    I appreciate any help offered

    Kind regards

    Steve
    Last edited by Richard Buttrey; 01-23-2010 at 12:50 PM. Reason: Missing Code tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1