title: bulk emails with excel
body: <p>Trying to create a bulk email workbook out of Excel using VBA code which includes embedded images.  I'm unable to apply a "<code>For i</code>" to the code and can't figure out how to email from an entire list with a <code>ListObject table</code>.  For the script below, the Sheet referenced is "Message Generator."  I'm trying to send an individual email to everyone in the list until the value in the row in Column B = 0.  However, the Integer I set for the loop seems to return the value 0, as though there are no values in the rows and columns at all. </p>
  
  <p>Anyone know how I can send create a workbook to send bulk emails?  See below for the script.  Thank you!</p>
  
  <pre><code>Dim MainWB As Workbook
  Dim olApp As Outlook.Application
  Dim olemail As Outlook.MailItem
  Dim fso As Scripting.FileSystemObject
  Dim ts As Scripting.TextStream
  Dim SigPath As String, SigText As String
  SigPath = Environ("AppData") &amp; "\Microsoft\Signatures\New.htm"
       Set fso = New Scripting.FileSystemObject
       Set ts = fso.OpenTextFile(SigPath)
  SigText = ts.ReadAll
  ts.Close
       Set fso = Nothing
       Set MainWB = ActiveWorkbook
  
  Dim Subject As String
  Dim Body As String
  Dim i As Integer
  Dim l As Integer
  l = NumberOfNonBlankRowsInColumn(2) - 2 'subtract 2 header rows
      Set olApp = New Outlook.Application
  
  For i = 0 To l
      Set olemail = olApp.CreateItem(olMailItem)
      Subject = MainWB.Sheets("Message Generator").Range("B3").Offset(i, 0).Value
      Body = MainWB.Sheets("Message Generator").Range("AB3").Offset(i, 0).Value
      With olemail
          .BodyFormat = olFormatHTML
          .To = "UTOAI@outlook.com"
          .Subject = Subject
          .Body = Body
          .Attachments.Add "C:\Users\Jacka\Documents\Test\logo.jpg"
          .HTMLBody = "&lt;img src='cid:logo.jpg'" &amp; "width='309.5' height='39.5'&gt;&lt;br&gt;" &amp; _vbanewline &amp; .HTMLBody &amp; SigText
          .Display
          End With
          Set olemail = Nothing
  Next i 
      Set olApp = Nothing
  End Sub
  </code></pre>
  
  <hr>
  
  <p>Function NumberOfNonBlankRowsInColumn(souceCol As Integer) As Integer</p>
  
  <pre><code>Dim NumberOfRowsInColumn As Integer, j As Integer
  Dim CurrentRowValue As String
  NumberOfRowsInColumn = Cells(Rows.Count, sourceCol).End(xlUp).row
  
  For j = 1 To NumberOfRowsInColumn
      CurrentRowValue = Cells(j, sourceCol).Value
  
  If IsEmpty(CurrentRowValue) Or CurrentRowValue = "" Then
  Exit For
  End If
  Next j
  NumberOfNonBlankRowsInColumn = (j - 1)
  End Function
  </code></pre>