Consulting

Results 1 to 2 of 2

Thread: VBA code to send individual emails with attachments based on cell values

  1. #1

    VBA code to send individual emails with attachments based on cell values

    Hi All,

    I'm trying to create some code to send individual emails from a worksheet, i have inputted all the information into the work sheet (Email Addresses, Attachment Locations, and Subject Lines) just wondering if anyone has any code to create the multiple emails.

    Email Address to: Column J & Column K
    Subject: Column B & " - " & "Retention Invoice"
    Attachment: Column M
    Wording In Email will be standard, apart from 1 line which is Column G

    if you need any more information please let me know.
    ND

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,787
    Location
    It is pretty simple. For you concatenation string for the To field, before to delimit the values with semicolons.

    e.g.
    'More Excel to Outlook Examples: http://www.rondebruin.nl/win/s1/outlook/bmail4.htm
    'http://www.rondebruin.nl/win/s1/outlook/signature.htm
    
    
    'Add reference: Microsoft Outlook xx.x Library, where xx.x is 14.0, 15.0, 16.0, etc.
    Sub Main()
      Dim olApp As Outlook.Application, olMail As Outlook.MailItem
      Dim a() As Variant, r As Range, c As Range
      
      Set olApp = New Outlook.Application
      
      Set r = Range("B2", Range("B" & Rows.Count).End(xlUp))
      For Each c In r
        Set olMail = olApp.CreateItem(olMailItem)
        With olMail
          .To = c.Value                           'Column B
          .Subject = "Pin No. " & c.Offset(, 3)   'Column E
          .Body = c.Offset(, 1)                   'Column C
          .Display
          '.Send
          Exit Sub
        End With
      Next c
      
      Set olMail = Nothing
      Set olApp = Nothing
    End Sub

Posting Permissions

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