Save exported excel from Outlook with Subject name

[mariokart]

Hello - I am creating a macro to export an HTML table embedded in a Outlook email to Excel.

The exporting piece is fine, however I am having troubles when saving. I would like to save the new file in a specific folder and the name should be the subject of the email.

I am having issues with the part in bold. If I use a specific file name (for example "\\xxx\docs\Testing\test.csv") , the file is saved with that name with no issues. However, if I try to make it dynamic using the subject of the email, the file is not saved. Can you help?

Below the entire code:

Sub ImportTableToExcel()Dim xMailItem As MailItem
Dim xTable As Word.Table
Dim xDoc As Word.Document
Dim xExcel As Excel.Application
Dim xWb As Workbook
Dim xWs As Worksheet
Dim I As Integer
Dim xRow As Integer
Dim xFilePath As String
Dim strSubject As String




On Error Resume Next
Set xExcel = New Excel.Application
Set xWb = xExcel.Workbooks.Add
xExcel.Visible = True
Set xWs = xWb.Sheets(1)
xRow = 1


For Each xMailItem In Application.ActiveExplorer.Selection
    Set xDoc = xMailItem.GetInspector.WordEditor
    For I = 1 To xDoc.Tables.count
        Set xTable = xDoc.Tables(I)
        xTable.Range.Copy
        xWs.Paste
        xRow = xRow + xTable.Rows.count + 1
        xWs.Range("A" & CStr(xRow)).Select
    Next
Next
 
strSubject = xMailItem.Subject


xFilePath = [B]"\\xxx\docs\Testing\" & strSubject[/B]


xWs.SaveAs xFilePath
xWs.Close


End Sub

 

[ZVI]

Hi and welcome to the MrExcel board!

After For Each xMailItem In ... the object xMailItem becomes Nothing.
You can get subject of the last selected email by inserting of strSubject = xMailItem.Subject between Next: Next, like this:

    Next
    strSubject = xMailItem.Subject ' This gets subject of the last selected email at the end of For-Each-Next
Next
 
'strSubject = xMailItem.Subject  ' xMailItem is Nothing here after For Each xMailItem In ...

Regards