VBA code to send separate emails to different people with different attachment

[chchanbp]

Hi all,

I want to send email in Outlook to company (column E), their email is in column F, with attachment path in column H. What is the VBA code? Thanks!

 

[CSmith]

Something like this should work . . .

    Dim olApp As Outlook.Application
    Dim olMail As Outlook.MailItem
    Dim Recipient, RecipientRange As Range

    Set olApp = GetObject(, "Outlook.Application")
    If olApp Is Nothing Then
        Set olApp = New Outlook.Application
    End If
    Set RecipientRange = Range(Sheets("Sheet2").Cells(2,6),Sheets("Sheet2").Cells(Sheets("Sheet2").Cells(Rows.Count, 6).End(xlUp).Row, 6)) ' Assumes top row is header
    For Each Recipient In RecipientRange.Rows
    Set olMail = olApp.CreateItem(olMailItem)
    With olMail
    Do While .ReplyRecipients.Count > 0 ' Clear possible recipients
      .ReplyRecipients.Remove 1
    Loop
    .Recipients.Add LCase(Recipient.Value)
    .Attachments.Add Recipient.Offset(0,2).Value
    ' I will normally set breakpoint here and ensure my details are pulling & showing properly before allowing the send below
    .Display ' Show email to be sent 
    ' .Send ' Send email UNCOMMENT TO AUTO SEND
    End With
    DoEvents ' Allow for Events
    Sleep (3000) ' Wait 3 sec between each email
    DoEvents ' Allow for Events
    Next Recipient

    'Cleanup
    Set Recipient = Nothing
    Set RecipientRange = Nothing
    Set olMail = Nothing
    Set olApp = Nothing