英文:
Moving beginning portion of email body to Excel
问题
我拼凑出了一段可行的代码,将电子邮件正文移至Excel。
我的公司在Outlook电子邮件底部有一个所有人都使用的签名,这是我从正文中提取到Excel的一部分,我不想要那部分。
这是一个示例:
Public Function IsWorkbookOpen(ByVal argFileName As String) As Boolean
Dim fileID As Long, errNum As Long
fileID = FreeFile()
On Error Resume Next
Open argFileName For Input Lock Read As #fileID
errNum = Err.Number
Close fileID
IsWorkbookOpen = CBool(errNum)
End Function
Private Sub Items_ItemAdd(ByVal item As Object)
On Error GoTo ErrorHandler
Dim Msg As Outlook.MailItem
If TypeName(item) = "MailItem" Then
Set Msg = item
Dim xExcelFile As String
Dim xExcelApp As Excel.Application
Dim xWb As Excel.Workbook
Dim xWs As Excel.Worksheet
Dim xNextEmptyRow As Integer
Dim xExcelRange As Excel.Range
xExcelFile = "C:\Users\placeholder\Desktop\Testing\Test2.xlsx"
End If
If IsWorkbookOpen("C:\Users\placeholder\Desktop\Testing\Test2.xlsx") = True Then
GoTo Skip
Else
Set xExcelApp = CreateObject("Excel.Application")
Set xWb = xExcelApp.Workbooks.Open(xExcelFile)
Set xWs = xWb.Sheets(1)
xWs.Activate
Set xExcelRange = xWs.Range("A1")
xExcelRange.Activate
xExcelApp.Visible = True
End If
Skip:
MsgBox "New Ticket"
On Error GoTo ErrHandler
' 设置Outlook应用程序对象。
Dim objOutlook As Object
Set objOutlook = CreateObject("Outlook.Application")
Dim objNSpace As Object
Set objNSpace = objOutlook.GetNamespace("MAPI")
Dim myFolder As Object
Set myFolder = objNSpace.GetDefaultFolder(olFolderInbox).Folders("Automation").Items
Dim objItem As Object
Dim iRows, iCols As Integer
iRows = 2
For Each objItem In objNSpace.GetDefaultFolder(olFolderInbox).Folders("Automation").Items
If objItem.Class = olMail Then
Dim objMail As Outlook.MailItem
Set objMail = objItem
Cells(iRows, 1) = objMail.ReceivedTime
Cells(iRows, 2) = objMail.SenderName
Cells(iRows, 3) = objMail.SenderEmailAddress
Cells(iRows, 4) = objMail.To
Cells(iRows, 5) = objMail.Body
End If
iRows = iRows + 1
Next
Set objMail = Nothing
Set objOutlook = Nothing
Set objNSpace = Nothing
Set myFolder = Nothing
ErrHandler:
Debug.Print Err.Description
MsgBox "End of sub"
ProgramExit:
Exit Sub
ErrorHandler:
MsgBox Err.Number & " - " & Err.Description
Resume ProgramExit
End Sub
英文:
I pieced together working code to move the body of email to Excel.
My company has a sign off that everyone uses at the bottom of their Outlook emails that is part of the body I pull into Excel. I don't want that part.
Here is an example of what that looks like:
>What a great day
Name
Company
position
address
email links
Public Function IsWorkbookOpen(ByVal argFileName As String) As Boolean
Dim fileID As Long, errNum As Long
fileID = FreeFile()
On Error Resume Next
Open argFileName For Input Lock Read As #fileID
errNum = Err.Number
Close fileID
IsWorkbookOpen = CBool(errNum)
End Function
Private Sub Items_ItemAdd(ByVal item As Object)
On Error GoTo ErrorHandler
Dim Msg As Outlook.MailItem
If TypeName(item) = "MailItem" Then
Set Msg = item
Dim xExcelFile As String
Dim xExcelApp As Excel.Application
Dim xWb As Excel.Workbook
Dim xWs As Excel.Worksheet
Dim xNextEmptyRow As Integer
Dim xExcelRange As Excel.Range
xExcelFile = "C:\Users\placeholder\Desktop\Testing\Test2.xlsx"
End If
If IsWorkbookOpen("C:\Users\placeholder\Desktop\Testing\Test2.xlsx") = True Then
GoTo Skip
Else
Set xExcelApp = CreateObject("Excel.Application")
Set xWb = xExcelApp.Workbooks.Open(xExcelFile)
Set xWs = xWb.Sheets(1)
xWs.Activate
Set xExcelRange = xWs.Range("A1")
xExcelRange.Activate
xExcelApp.Visible = True
End If
Skip:
MsgBox "New Ticket"
On Error GoTo ErrHandler
' Set Outlook application object.
Dim objOutlook As Object
Set objOutlook = CreateObject("Outlook.Application")
Dim objNSpace As Object
Set objNSpace = objOutlook.GetNamespace("MAPI")
Dim myFolder As Object
Set myFolder = objNSpace.GetDefaultFolder(olFolderInbox).Folders("Automation").Items
Dim objItem As Object
Dim iRows, iCols As Integer
iRows = 2
For Each objItem In objNSpace.GetDefaultFolder(olFolderInbox).Folders("Automation").Items
If objItem.Class = olMail Then
Dim objMail As Outlook.MailItem
Set objMail = objItem
Cells(iRows, 1) = objMail.ReceivedTime
Cells(iRows, 2) = objMail.SenderName
Cells(iRows, 3) = objMail.SenderEmailAddress
Cells(iRows, 4) = objMail.To
Cells(iRows, 5) = objMail.Body
End If
iRows = iRows + 1
Next
Set objMail = Nothing
Set objOutlook = Nothing
Set objNSpace = Nothing
Set myFolder = Nothing
ErrHandler:
Debug.Print Err.Description
MsgBox "End of sub"
ProgramExit:
Exit Sub
ErrorHandler:
MsgBox Err.Number & " - " & Err.Description
Resume ProgramExit
End Sub
答案1
得分: 0
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论