Reputation: 906
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
Upvotes: 1
Views: 5747
Reputation: 1276
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
Upvotes: 1