选择电子邮件的正文，将其复制并使用VBA将其粘贴到excel中 [英] Select the body of an email, copy it and paste it into excel using VBA

问题描述

我要选择特定电子邮件的正文，将其复制并粘贴到Outlook中.我知道在电子表格中仅按Ctrl + A然后按Ctrl + C会更容易，但这只是涉及自动化报告的更大流程的一部分.这是我拥有的代码:

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 just part of a much larger process that involves automation of a report. this is the code that i have:

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 this line:

If InStr(olMail.Subject, "Criteria") > 0 Then

所以我不知道我在想什么或做错了什么，所以请帮忙，因为我对VBA不太熟悉.

so i dont know what am i missing or doing wrong so please help as i am not very experienced with VBA.

推荐答案

我会看两件事.首先，您要粘贴邮件正文的工作表实际上称为"YourSheet"，其次，您引用的是outMail.Body，其中outMail从未设置尺寸或设置.尝试执行此操作(假设要粘贴到的工作表称为"Sheet1").

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

这篇关于选择电子邮件的正文，将其复制并使用VBA将其粘贴到excel中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！