title: excel VBA to send e-mail from macro
body: <p>I am writing a macro to send an e-mail from an excel sheet. The macro prepares a few reports and then has a function to prepare the e-mail for the report. Everything works fine except when it gets to the .Send line it gives me a run time error -2147467259. Not sure what this means, but would appreciate the help. </p>
  
  <p>Here is the code for the function:</p>
  
  <pre><code>Function Mail_Reports(ByRef wkDate2 As String, fileDate2 As String, wkNumber2 As String, thisYear2 As String)
  ' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, Outlook 2010.
  ' This example sends the last saved version of the Activeworkbook object .
      Dim OutApp As Object
      Dim OutMail As Object
      Dim mailList As String
      Dim i As Long, lstRow As Long, p As Long, addressNum As Long, begRow As Long
      Dim proNam2 As String
      Set OutApp = CreateObject("Outlook.Application")
      Set OutMail = OutApp.CreateItem(0)
  
      'On Error Resume Next
     ' Change the mail address and subject in the macro before you run it.
      For i = 1 To 5 Step 1
          mailList = ""
          lstRow = Sheets("Data").Cells(Rows.Count, i).End(xlUp).Row
          addressNum = lstRow - 16
          begRow = lstRow - addressNum
          proNam2 = Sheets("Data").Cells(16, i)
          proNam2 = Replace(proNam2, " ", "")
          For p = 1 To addressNum Step 1
              mailList = Sheets("Data").Cells(begRow + p, i) &amp; " ; " &amp; mailList
          Next p
          With OutMail
              .To = mailList
              '.CC = "" remove comma and use this if you want to cc anyone, can be string or variable
              '.BCC = "" remove comma and use this if you want to cc anyone, can be string or variable
              .Subject = "Test"
              .HTMLBody = "&lt;HTML&gt;&lt;BODY&gt;&lt;Font Face=Calibri(Body)&gt;&lt;p&gt;Hi All,&lt;/p&gt;&lt;p2&gt;Attached to this e-mail is the test file.&lt;p2/&gt;&lt;br&gt;&lt;br&gt;&lt;p3&gt;Best,&lt;p3/&gt;&lt;/font&gt;&lt;/BODY&gt;&lt;/HTML&gt;"
              .attachments.Remove 1
              .attachments.Add "C:\Documents and Settings\test.xlsx"
              .Display
              .Send
      Next i
  
  
      On Error GoTo 0
  
      Set OutMail = Nothing
      Set OutApp = Nothing
  End Function
  </code></pre>