1

I want to select the body of a specific email, copy it and paste it into Outlook.

I know that it would be easier to just press Ctrl + A and then Ctrl + C in the spreadsheet but this is part of a much larger process that involves automation of a report.

Sub GetFromInbox()
     
    Dim olApp As Outlook.Application
    Dim olNs As Outlook.Namespace
    Dim olFldr As Outlook.MAPIFolder
    Dim olItms As Outlook.Items
    Dim olMail As Variant
    Dim i As Long
     
    Set olApp = New Outlook.Application
    Set olNs = olApp.GetNamespace(”MAPI”)
    Set olFldr = olNs.GetDefaultFolder(olFolderInbox)
    Set olItms = olFldr.Items
     
    olItms.Sort “Subject”
     
    i = 1
     
    For Each olMail In olItms
        If InStr(olMail.Subject, “Criteria") > 0 Then
            ThisWorkbook.Sheets("YourSheet").Cells(i, 1).Value = outMail.Body
            i = i + 1
        End If
    Next olMail
     
    Set olFldr = Nothing
    Set olNs = Nothing
    Set olApp = Nothing
     
End Sub

I get a syntax error on:

If InStr(olMail.Subject, “Criteria") > 0 Then
Community
  • 1
  • 1
Raul Gonzales
  • 866
  • 1
  • 15
  • 28
  • 1
    The first parameter for `InStr` is the start position. Try `If InStr(1, olMail.Subject, “Criteria") > 0 Then` – Tony Dallimore Feb 14 '17 at 19:39
  • @TonyDallimore thanks for that! missed that part!... i am getting an error at the very next line saying Run-time error 424 Object Required? sorry but i only have been coding in VBA for 3 months! – Raul Gonzales Feb 14 '17 at 19:48
  • 1
    Put Option Explicit at the top of the module. Under Tools | Options | Editor tab - "Require Variable Declaration". – niton Feb 14 '17 at 20:52
  • 1
    You have `For Each olMail In olItms` and `outMail.Body`. `olMail` and `outMail` must be the same. I agree with nitin; `Option Explicit` would help you detect these errors before you run the code. – Tony Dallimore Feb 14 '17 at 21:00
  • 1
    Fix your `" "` quotation marks, they look different – 0m3r Feb 14 '17 at 23:07

1 Answers1

1

I'd look at two things. First, is the sheet you want to paste the mail body to actually called "YourSheet" and secondly, you're referencing outMail.Body where outMail has never been dimensioned or set. Try this (assuming the sheet to paste to is called "Sheet1").

Sub GetFromInbox()

Dim olApp As Outlook.Application
Dim olNs As Outlook.Namespace
Dim olFldr As Outlook.MAPIFolder
Dim olItms As Outlook.Items
Dim olMail As Variant
Dim i As Long

Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set olFldr = olNs.GetDefaultFolder(olFolderInbox)
Set olItms = olFldr.Items

olItms.Sort "Subject"

i = 1

For Each olMail In olItms
    If InStr(1, olMail.Subject, "Criteria") > 0 Then
        ThisWorkbook.Sheets("Sheet1").Cells(i, 1).Value = olMail.Body
        i = i + 1
    End If
Next olMail

Set olFldr = Nothing
Set olNs = Nothing
Set olApp = Nothing

End Sub
RyanL
  • 1,246
  • 2
  • 10
  • 14
  • thaks for that mate! it works now but all the email is copied onto 1 cell and not as HTML. if you have any suggestions they are welcome but i will try to find the answer as well. – Raul Gonzales Feb 15 '17 at 07:49
  • @RaulGonzales If you're interested in the HTML, swap out olMail.Body with olMail.HTMLBody -- keep in mind this will provide the markup but will display as text. And in terms of it pasting in one cell, you've maintained the i=i+1 in your loop, right? – RyanL Feb 15 '17 at 13:39