将电子邮件正文的起始部分移到Excel中。

huangapple go评论53阅读模式
英文:

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

使用InStr函数查找不需要的文本的开头,并使用Left函数复制该位置之前的文本。

英文:

Use InStr function to find the beginning of the unwanted text and copy the text prior to that position using the Left function.

huangapple
  • 本文由 发表于 2023年3月31日 22:27:49
  • 转载请务必保留本文链接:https://go.coder-hub.com/75899693.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定