Hi Graham
I added the below code in the module of my outlook. It throws the Inputbox asking for the subject, but nothing appears on the excel sheet. Please let me know what I am missing here.
What I want from the mail -
From
Email-ID
Sunject Line -
Body -
Sub CopyToExcel(olItem As MailItem)
Dim xlApp As Object
Dim xlWB As Object
Dim xlSheet As Object
Dim vText As Variant
Dim sText As String
Dim sAddr As String
Dim vAddr As Variant
Dim vItem As Variant
Dim i As Long, j As Long
Dim rCount As Long
Dim bXStarted As Boolean
strTestSubject = InputBox("Enter the message subject to process")
Const strWorkSheetName As String = "Sheet1"
Const strWorkBookName As String = "C:\Users\\New folder (2)\Book2.xlsx" 'the path of the workbook
'Use FileExists function to determine the availability of the workbook
If Not FileExists(strWorkBookName) Then Exit Sub
'Get Excel if it is running, or open it if not
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Set xlApp = CreateObject("Excel.Application")
bXStarted = True
End If
On Error GoTo 0
'Open the workbook to input the data
Set xlWB = xlApp.Workbooks.Open(strWorkBookName)
Set xlSheet = xlWB.Sheets("Sheet1")
'Process the message
With olItem
sText = olItem.Body
vText = Split(sText, Chr(13))
'Find the next empty line of the worksheet
rCount = xlSheet.Range("B" & xlSheet.Rows.Count).End(-4162).Row + 1
'Check each line of text in the message body
For i = UBound(vText) To 0 Step -1
If InStr(1, vText(i), "Source:") > 0 Then
vItem = Split(vText(i), Chr(58))
xlSheet.Range("A" & rCount) = Trim(vItem(1))
End If
If InStr(1, vText(i), "Customer Name:") > 0 Then
vItem = Split(vText(i), Chr(58))
xlSheet.Range("B" & rCount) = Trim(vItem(1))
End If
If InStr(1, vText(i), "Customer Email:") > 0 Then
vItem = Split(vText(i), Chr(58))
sAddr = ""
For j = 1 To UBound(vItem)
sAddr = sAddr & vItem(j)
Next j
If InStr(1, UCase(sAddr), "HYPERLINK") > 0 Then
vAddr = Split(sAddr, Chr(34))
sAddr = vAddr(UBound(vAddr))
End If
xlSheet.Range("C" & rCount) = sAddr
End If
If InStr(1, .Subject, strTestSubject) > 0 Then
vItem = Split(vText(i), Chr(58))
xlSheet.Range("D" & rCount) = Trim(vItem(1))
End If
Next i
xlWB.Save
End With
xlWB.Close SaveChanges:=True
If bXStarted Then
xlApp.Quit
End If
Set xlApp = Nothing
Set xlWB = Nothing
Set xlSheet = Nothing
End Sub
Public Function FileExists(ByVal Filename As String) As Boolean
Dim nAttr As Long
On Error GoTo NoFile
nAttr = GetAttr(Filename)
If (nAttr And vbDirectory) <> vbDirectory Then
FileExists = True
End If
NoFile:
End Function
Sub ExtractData()
Dim oItem As MailItem
If Application.ActiveExplorer.Selection.Count = 0 Then
MsgBox "No Items selected!", vbCritical, "Error"
Exit Sub
End If
For Each oItem In ActiveExplorer.Selection
CopyToExcel oItem
Next oItem
Set oItem = Nothing
End Sub
Need help to amend the Outlookto Excel Macro
Reply With Quote
