title: Passing Outlook mail body to string
body: <p>I'm trying to gather email addresses from bad responses to an email blast.  </p>
  
  <p>The code is split into two parts, the search part, which searches for a character in the email and returns the string before and after it, and the process part, which runs the search on every email in an Outlook folder.  </p>
  
  <p>I've tested the search on emails that I've copied into Excel and it works.</p>
  
  <p>The issue I'm having is I can't pass the email body, which is an object, to a string variable.</p>
  
  <pre><code>Sub Extract()
  On Error Resume Next
  
  'specify the folder to pull emails from
  Set myOlApp = Outlook.Application
  Set mynamespace = myOlApp.GetNamespace("mapi")
  Set myfolder = myOlApp.ActiveExplorer.CurrentFolder
  Dim myitem As Outlook.MailItem
  
  'start excel and open spreadsheet
  Set xlobj = CreateObject("excel.application")
  xlobj.Visible = True
  xlobj.Workbooks.Add
  
  'Set Heading on spreadsheet
  xlobj.Range("a" &amp; 1).Value = "Email"
  
  'for loop passing email body to search code
  For i = 1 To myfolder.Items.Count
  Set myitem = myfolder.Items(i)
  extractStr = myitem.Body
  
  'search for specific text
  CheckStr = "[A-Za-z0-9._-]"
  OutStr = ""
  Index = 1
  Index1 = VBA.InStr(Index, extractStr, "@")
  getStr = ""
  If Index1 &gt; 0 Then
      For p = Index1 - 1 To 1 Step -1
          If Mid(extractStr, p, 1) Like CheckStr Then
              getStr = Mid(extractStr, p, 1) &amp; getStr
          Else
              Exit For
          End If
      Next
      getStr = getStr &amp; "@"
      For p = Index1 + 1 To Len(extractStr)
          If Mid(extractStr, p, 1) Like CheckStr Then
              getStr = getStr &amp; Mid(extractStr, p, 1)
          Else
              Exit For
          End If
      Next
      Index = Index1 + 1
      If OutStr = "" Then
          OutStr = getStr
      Else
          OutStr = OutStr &amp; Chr(10) &amp; getStr
      End If
  Else
      GoTo 20
  End If
  
  
  'write to excel
  20 xlobj.Range("a" &amp; i + 1).Value = OutStr
  
  Next
  End Sub
  </code></pre>
  
  <p><strong>Update:</strong>  I think I've got it figured out.  To test this script I place one or two of the emails to pull email addresses from into a test folder.  The emails I selected were html formatted!  I put the following line of code to convert the current email body (myitem) to plain text.</p>
  
  <pre><code>myitem.BodyFormat = olFormatPlain
  </code></pre>
  
  <p>I've declared the myitem variable as both an object and a mailitem.  When I run the script with myitem as an object I get an "object doesn't support this property or method" error at the following line:</p>
  
  <pre><code>myitem.BodyFormat = olFormatPlain
  </code></pre>
  
  <p>However, when I run it as a mail item I get a type mismatch error at this line:</p>
  
  <p><code>For Each myitem In myfolder</code></p>
  
  <p>Here's how I'm declaring the myitem variable in the two different scenarios:</p>
  
  <pre><code>Dim myitem as MailItem
  Dim myitem as Object
  </code></pre>
  
  <p>Here's my updated code.</p>
  
  <pre><code>Option Explicit
  Sub Extract()
  'On Error Resume Next
  
  'Variable declaration
  Dim myOlApp As Outlook.Application
  Dim mynamespace As Outlook.NameSpace
  Dim myfolder As Selection
  Dim myitem As MailItem
  Dim i As Integer
  Dim extractStr As String
  Dim CheckStr As String
  Dim OutStr As String
  Dim Index As Integer
  Dim Index1 As Integer
  Dim getStr As String
  Dim p As Integer
  
  'start excel and open spreadsheet
  Dim xlobj As Object
  Set xlobj = CreateObject("excel.application")
  xlobj.Visible = True
  xlobj.Workbooks.Add
  
  'Set Heading on spreadsheet
  xlobj.Range("a" &amp; 1).Value = "Email"
  
  'Set reference to the Selection.
  Set myOlApp = Outlook.Application
  Set mynamespace = myOlApp.GetNamespace("mapi")
  Set myfolder = ActiveExplorer.Selection
  
  'for loop passing email body to search code
  For Each myitem In myfolder
      myitem.BodyFormat = olFormatPlain
      extractStr = myitem.Body
      MsgBox (extractStr)
  
  'search for specific text
      CheckStr = "[A-Za-z0-9._-]"
      OutStr = ""
      Index = 1
      Index1 = VBA.InStr(Index, extractStr, "@")
      getStr = ""
      If Index1 &gt; 0 Then
          For p = Index1 - 1 To 1 Step -1
              If Mid(extractStr, p, 1) Like CheckStr Then
                  getStr = Mid(extractStr, p, 1) &amp; getStr
              Else
                  Exit For
              End If
          Next
          getStr = getStr &amp; "@"
          For p = Index1 + 1 To Len(extractStr)
              If Mid(extractStr, p, 1) Like CheckStr Then
                  getStr = getStr &amp; Mid(extractStr, p, 1)
              Else
                  Exit For
              End If
          Next
          Index = Index1 + 1
          If OutStr = "" Then
              OutStr = getStr
          Else
              OutStr = OutStr &amp; Chr(10) &amp; getStr
          End If
      Else
          GoTo 20
      End If
  
  
  'write to excel
  20     xlobj.Range("a" &amp; i + 1).Value = OutStr
  
  Next
  End Sub
  </code></pre>