Results 1 to 10 of 23

Thread: how to send each row by email

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Junior Member
    Join Date
    Aug 2012
    Posts
    14
    Rep Power
    0
    Hi Again,
    How can I add cell C(i) to subject line into each email?
    What I need to print to subject field for the first line is, for example, "customer11, expired account" etc..
    Here is the script and the excel file I run in my pc:

    Code:
    Sub SendEmailRowByRow()
        
        Dim OutApp      As Object
        Dim OutMail     As Object
        Dim strBody     As String
        Dim LastRow     As Long
        Dim eMailIDs, i As Long
        Dim varBody
        
        Const StartRow  As Long = 1     '<<< adjust to suit
        
        
        If Not Application.Intersect(Range("I:I"), ActiveSheet.UsedRange) Is Nothing Then
            
            LastRow = Range("I" & Rows.Count).End(xlUp).Row
            
            eMailIDs = Range("I" & StartRow).Resize(LastRow - StartRow + 1)
            
            For i = 1 To UBound(eMailIDs, 1)
                
                Set OutApp = CreateObject("Outlook.Application")
                Set OutMail = OutApp.CreateItem(0)
                
                varBody = Range("a" & StartRow + i - 1).Resize(, 7).Value
                strBody = Join(Application.Transpose(Application.Transpose(varBody)), vbTab)
                On Error Resume Next
                With OutMail
                    .To = eMailIDs(i, 1) 'email from corresponding row goes here
                    .CC = ""
                    .BCC = ""
                    .Subject = "Expired account notification"    '<< adjust subject line
                    .Body = strBody
                    'You can add a file like this
                    '.Attachments.Add ("C:\")
    '                .Display
                    .Send
                    Application.Wait Now + TimeSerial(0, 0, 3)
                    Set OutMail = Nothing
                    Set OutApp = Nothing
                End With
                On Error GoTo 0
            Next
        End If
        
    End Sub
    Attached Files Attached Files

Similar Threads

  1. Replies: 17
    Last Post: 07-15-2013, 09:56 PM
  2. Replies: 2
    Last Post: 05-23-2013, 08:08 AM
  3. How To Send Outlook Email Using VBA
    By mfaisalrazzak in forum Excel Help
    Replies: 7
    Last Post: 03-03-2013, 03:09 AM
  4. Send Outlook Email With Word Document
    By Murali K in forum Excel Help
    Replies: 2
    Last Post: 06-27-2012, 08:42 PM
  5. Send Lotus Notes Email Using VBA
    By ramakrishnan in forum Excel Help
    Replies: 1
    Last Post: 09-08-2011, 09:00 AM

Posting Permissions

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